Unknown Number of Column With Each Query

Malcont3nt

I have a dropdown with a list of reports that can be run on a page. DataTables is using ajax to retrieve the correct number of rows, but I can't get the columns names to show up. My data is a JSON array of objects with the column name in each row.

{"action": "getReportData", "actionId": "21", "retVal": "True", "sqlCode": "0", "sqlMsg": "", "recordset": [ { "ShortCatalogName": "1949, General Statutes of Connecticut, Comley et al, 3v plus supps.", "LongID": "08024.0003.001", "CatalogID": "08024", "Downloads": 3 }, { "ShortCatalogName": "1949, General Statutes of Connecticut, Comley et al, 3v plus supps.", "LongID": "08024.0003.001", "CatalogID": "08024", "Downloads": 1 }, { "ShortCatalogName": "1875-, Georgia Attorney General Opinions", "LongID": "08027.1949.001", "CatalogID": "08027", "Downloads": 3 }, { "ShortCatalogName": "1875-, Georgia Attorney General Opinions", "LongID": "08027.1972.001", "CatalogID": "08027", "Downloads": 2 }, { "ShortCatalogName": "1888, Miller’s Annotated Code 1v+s.", "LongID": "08031.1888.001", "CatalogID": "08031", "Downloads": 2 } ] }

Malcont3nt

How can I create the column definition when I don't know which columns are coming back? Each report has a different number of columns and I don't know what the columns are until the AJAX call returns.

allan

Thanks for your question - however, per the forum rules can you link to a test case showing the issue please. This will allow the issue to be debugged.

Information on how to create a test page, if you can't provide a link to your own page can be found here.

Thanks,
Allan

Malcont3nt

It's not an issue with DataTables itself, but an issue of using DataTables. My JOSN is coming from a .Net page that includes a number of other values. The examples on the site show an array, or object array, where each array item is a row from the database, and the initialization has a definition for the name and number of columns.

I'm using JsonConvert.SerializeObject in my .Net code, and am presented with an array of objects. I can get DataTables to display the JSON data, but only because I know how to define the names and number of columns when initializing the table in my test cases.

I could modify my JSON code to return an extra property with the names of the columns being returned, but was hoping there was a mechanism built into DataTables to determine the column names from the array of objects.

I WAS using AJAX loading via DataTables with a dataSrc option to get the correct number of rows, but was unable to dynamically define the column names.

I'm currently working on getting the column names from the JOSN object to build a string for use in defining the columns that came in:

    function drawReportTable() {
        // Feed JSON object to DataTable
        // Build list of columns from data prior to init of DataTable
        var columns = Object.keys(_reportTable[0]);
        var numColumns = columns.length;
        var columnInit = '{';
        var obj = $.parseJSON(_oAjaxReportTable.responseText);

        // Get column names for DataTables
        for (var i = 0; i < numColumns; i++) {
            columnInit += ' "data": "' + columns[i] + '", ';
        }

        columnInit = columnInit.substring(0, columnInit.length - 2);
        columnInit += ' }';

        debug('columnInit');
        debug(columnInit);

        $('#ReportTable').DataTable({
            "paging": true,
            "ordering": true,
            "pageLength": 50,
            "columns": columnInit,
            "destroy": true,
            "data": obj.recordset
        });
    }

The above doesn't work because columns needs to be an object. If I can pass the column definition as an array I can modify my JSON output to accommodate this.

allan

I WAS using AJAX loading via DataTables with a dataSrc option to get the correct number of rows, but was unable to dynamically define the column names.

Yes, there is no way to do that via the DataTables Ajax options at the moment I'm afraid (FAQ).

Allan

allan

Sorry for multiple messages - I missed this bit:

The above doesn't work because columns needs to be an object.

columns has to be an array.

Allan

Malcont3nt

After a bunch of trial and error I decided to mock up two rows of data:

       function drawReportTable() {
        // Feed JSON object to DataTable
        // Build list of columns from data prior to init of DataTable
        var columns = Object.keys(_reportTable[0]);
        var numColumns = columns.length;
        var columnInit = [];
        var obj = $.parseJSON(_oAjaxReportTable.responseText);

        // Get column names for DataTables
        for (var i = 0; i < numColumns; i++) {
            columnInit.push({ "name": columns[i] });
        }

        $('#ReportTable').DataTable({
            "paging": true,
            "ordering": true,
            "pageLength": 50,
            "columns": columnInit,
            "destroy": true,
            "data": [[
                "Office One",
                "Group 1",
                true,
                "09/18/2012"
            ],
            [
                "Office Two",
                "Group 1",
                true,
                "12/09/2010"
            ]]
        });

        nbsLoadingScreen_Stop();
    }

This results in a table with two rows and four columns BUT no column names.

I'm sure I can get the JSON to look like the mocked up data, and return a Columns property, but I can't get the column names to show up.

When I change
"columns": columnInit,

to this:
"columns": ['1', '2', '3', '4'],

I get the following error:
Unhandled exception at line 534, column 2 in http://localhost:4800/scripts/jquery-3.1.0.js

0x800a138f - JavaScript runtime error: Invalid operand to 'in': Object expected

Malcont3nt

I believe I found the issue. I was using the wrong property for the columns. I should have been doing:
columnInit.push({ "title": columns[I] });

This is powerful stuff, Allan. Thanks for making DataTables

allan

Thanks for posting back - great to hear you've got the answer!

Allan