dynamic columns

montoyam

I found other posts that deal with grammatically creating columns. My data seems exactly the same but I am getting an error. So I am guessing it is because my json is formatted differently.

Here is the link where someone else got it working: https://datatables.net/forums/discussion/comment/162006/#Comment_162006

Here is my code:

            var columns = []; 
            $.ajax({
                url: "api/ToGenerate",
                success: function (data) {
                    //tableData = JSON.parse(data);  //Unexpected token o in JSON at position 1
                    tableData = JSON.parse(JSON.stringify(data));    // Cannot read property '0' of undefined
                    
                    columnNames = Object.keys(tableData.data[0]);
                    for (var i in columnNames) {
                        columns.push({
                            data: columnNames[i],
                            title: columnNames[i]
                        });
                    }
                    
                    $('#ToGenerate').DataTable({
                        dom: 'frtip',
                        ajax: {
                            url: 'api/ToGenerate',
                            dataSrc: 'Table'
                        },
                        rowId: 'ImportID',
                        columns: columns
                    })
                }

            });

here is the format of my JSON

{"Table":[{"ImportID":121,"DeptName":"Ag Commissioner","FTE":48.15,"EmployeeCount":50}...

the first line of code JSON.parse(data) results in 'unexpected token...
the second line of code (from the post that I have the link to above) results in Cannot read property '0' of undefined and is talking about the line: columnNames = Object.keys(tableData.data[0]);

So I assume something is wrong with the structure of the object tableData but I can't figure it out.

kthorngren

The first thing to do is to figure out what data is. Add console.log(typeof data) on line 5. If its a string then you need to use JSON.parse(data). If its an object then you don't need to do anything.

Using JSON.parse(JSON.stringify(data)) doesn't really do anything. It will encapsulate data then decapsulate it.

columnNames = Object.keys(tableData.data[0]); won't work since tableData is an object with a key of Table not data. You will need something like columnNames = Object.keys(tableData.Table[0]);.

One suggestion is to use something like this example:
http://live.datatables.net/huyexejo/1/edit

Its probably the same as the examples in the thread you posted but the key is that the Datatables initialization is in the success function. It uses data instead of ajax. This way you can just use the initial data that is fetched instead of requesting it twice.

Kevin

montoyam

once again, you provided the perfect solution. thank you. in the examples, 'data' was used so many times, being a beginner I was not sure which was which. I didn't realize a couple of the 'data's was referring to the property name of the json return.

working version:

            var columns = []; 
            $.ajax({
                url: "api/ToGenerate",
                success: function (data) {
                    //tableData = JSON.parse(data);  //Unexpected token o in JSON at position 1
                    tableData = JSON.parse(JSON.stringify(data));    // Cannot read property '0' of undefined
                    
                    columnNames = Object.keys(tableData.Table[0]);
                    for (var i in columnNames) {
                        columns.push({
                            data: columnNames[i],
                            title: columnNames[i]
                        });
                    }
                    
                    $('#ToGenerate').DataTable({
                        dom: 'frtip',
                        data: tableData.Table,
                        rowId: 'ImportID',
                        columns: columns
                    })
                }

            });

montoyam

one last question (hopefully) regarding this...

I know I want to build the columns dynamically, but say there were a few columns that I want specific properties for, such as a date format. Can I delete and re-add those columns or somehow add the formatting after the fact?

kthorngren

tableData = JSON.parse(JSON.stringify(data));

Again you don't need this. Please see my previous explanation. You can either replace tableData with data or replace the above statement with tableData = data;.

Can I delete and re-add those columns or somehow add the formatting after the fact?

Datatables doesn't allow for changing the column definitions after initialization. To change this you would need to use destroy or destroy() to change the column config. But this isn't answering your question:

few columns that I want specific properties for, such as a date format

How would you know, programmatically, that you need these extra config options? Likely you will need to add them in the for (var i in columnNames) loop. You could use columnDefs and set the columnDefs.targets based on a global variable you set in the for loop.

Kevin

montoyam

yeah, those columns would not be dynamic, they would be hard coded for fields that I know are going to exist. I will play around with your suggestions.

I took out tableData and just switched everything back to data. thanks for that one too

            $.ajax({
                url: "api/AdjustedRatios",
                success: function (data) {
                    var columns = []; 
                    //build the DataTable dynamically.
                    // json return:  {"Table":[{"ImportID":121,"DeptName":"Ag Commissioner","FTE":48.15,"EmployeeCount":50}...
                    columnNames = Object.keys(data.Table[0]); //.Table[0]] refers to the propery name of the returned json
                    for (var i in columnNames) {
                        columns.push({
                            data: columnNames[i],
                            title: columnNames[i]
                        });
                    }
                    
                    $('#AdjustedRatios').DataTable({
                        dom: 'frtip',
                        data: data.Table,
                        rowId: 'ImportID',
                        scrollX: true,
                        columns: columns
                    })
                }

            });

etilley

I have a need to call this routine when a function is called. Has anyone got an example of this working from inside one?

I took a crack at it but no luck ...

function indexRankDetail() {

      var table = $('#indexranks').DataTable({
          "ajax": {
              // "url": "static/objects2.txt", // This works for the static file
              // url: "/index_maker/" + indid,
              "url": "/indextest/" // This now works too thanks to @kthorngren
              "success": function (data2) {
                  var columns = [];
                  //build the DataTable dynamically.
                  // json return:  {"Table":[{"ImportID":121,"DeptName":"Ag Commissioner","FTE":48.15,"EmployeeCount":50}...
                  columnNames = Object.keys(data2.Table[0]); //.Table[0]] refers to the propery name of the returned json
                  for (var i in columnNames) {
                      columns.push({
                          data: columnNames[i],
                          title: columnNames[i]
                      });
                  }

                  var table = $('#indexranks').DataTable({
                      dom: 'frtip',
                      data: data2.Table,
                      rowId: 'ImportID',
                      scrollX: true,
                      columns: columns
                  });
              }
            }
      })
    };

colin

@etilley You're going to need to give more context here. We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

etilley

Thanks for responding @colin .
Your test case page doesn't emulate a flask ajax json "return", and I don't have a public IP to display my json output unfortunately.

I did get the following code to work with a function call. What is the purpose of rowID: I wonder?

JSON

When I browse to > http://localhost:5000:/indextest on my dev system, I return your json data:
{"Table":[{"ImportID":121,"DeptName":"AgCommissioner","FTE":48.15,"EmployeeCount":50}] }

Flask routes.py

    @blueprint.route('/indextest/')
    def indextest():

    text = """{"Table":[{"ImportID":121,"DeptName":"Ag Commissioner","FTE":48.15,"EmployeeCount":50}]}"""
        return (text)

For others who want to display tables with dynamic columns:

JS

function indexRankDetail() {

    var columns = [];  
    $.ajax({
            // "url": "static/objects2.txt", // This works for a static file
            url: "/indextest/",
            dataType: "json",
            dataSrc: "",
            success: function (data) {
                var columns = [];
                //build the DataTable dynamically.
                columnNames = Object.keys(data.Table[0]); //.Table[0]] refers to the propery name of the returned json
                for (var i in columnNames) {
                    columns.push({
                        data: columnNames[i],
                        title: columnNames[i]
                    });
                }

                $('#indexranks').DataTable({
                    data: data.Table,
                    rowId: 'ImportID',
                    scrollX: true,
                    columns: columns
                });
            }
          })
    };

HTML

<div class="table-responsive table-sales">
      <table id="indexranks" class="display" style="width:100%;"></table>
</div>

etilley

Just one problem @colin ...

Periods inside of column titles throws an error "DataTables warning: table id=indexranks - Requested unknown parameter 'SI.POV.DDAY' for row 0, column 7. For more information about this error, please see http://datatables.net/tn/4"

Here is some test data, the title "SI.POV.DDAY" is displayed in the header correctly, but its value is not displayed in the table data... Note that an "_" doesn't generate an error.

{"Table":[{"iso2c":"AO","name":"Angola","A_or _C":"A","Terr":5.751,"FinalRank":110.0,"FinalMnMx":130.0,"FinalMean":125.0,"D.POV.DAY":47.6,"date":"2018-01-01","rank":144.0,"D.POV.GINI":51.3,"date_2":"2018-01-01","rank_2":148.0,"DYN.LE00":60.782,"date_3":"2018-01-01","rank_3":179.0,"XPD.SECO":42.42431,"date_4":"2006-01-01","rank_4":21.0},{"iso2c":"AL","name":"Albania","A_or _C":"C","Terr":-13.713,"FinalRank":61.0,"FinalMnMx":57.0,"FinalMean":60.0,"D.POV.DAY":1.7,"date":"2017-01-01","rank":75.5,"D.POV.GINI":33.2,"date_2":"2017-01-01","rank_2":46.0,"DYN.LE00":78.458,"date_3":"2018-01-01","rank_3":45.0,"XPD.SECO":21.5625,"date_4":"2017-01-01","rank_4":150.0},{"iso2c":"AR","name":"Argentina","A_or _C":"C","Terr":-2.139,"FinalRank":53.0,"FinalMnMx":54.0,"FinalMean":51.0,"D.POV.DAY":1.0,"date":"2018-01-01","rank":60.0,"D.POV.GINI":41.4,"date_2":"2018-01-01","rank_2":107.5,"DYN.LE00":76.52,"date_3":"2018-01-01","rank_3":68.0,"XPD.SECO":39.5394,"date_4":"2017-01-01","rank_4":43.0}]}

colin

Here's the reference page for rowId.

For the period, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

Jonathan_16

Hello, friends I'm trying to run this pivot table but I get these numbers next to the table names, I just want to get the names, they seem to be the number of data but I'm not sure why I get that, and besides it generates this error. Could you help me to know why this happens?

Here is my code

<script>
     
            var columns = [];
            function getDT() {
                $.ajax({
                  url: "index2.php",
                  success: function (data) {
                        data = JSON.parse(data);
                        columnNames = Object.keys(data[0]); 
                        for (var i in columnNames) {
                          columns.push({data: columnNames[i], 
                                    title: capitalizeFirstLetter(columnNames[i])});
                        }
                        $('#example').DataTable({
                            ajax: "index2.php",
                            columns: columns
                        });
                    }
                    
                });
            }
            
            function capitalizeFirstLetter(string) {
                return string.charAt(0).toUpperCase() + string.slice(1);
            }
            
            $(document).ready(function() {
              
              
              getDT();
            
              
            } );

    </script>

And this is the error I get

kthorngren

The columnNames = Object.keys(data[0]); statement gets the first row of the data response to build the columns. Start by looking at the first row to see if its waht you expect.

Cannot read property 'length' of undefined

Something is not correct with the ajax response in line 15. There are many threads, like this one, discussing that error. Search the forum to see if you can find something that helps.

In order for us to help we will need a link to your page or a test case replicating the issue so we can see what is happening.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Jonathan_16

Thanks a lot friend, I was able to solve the ajax error in line 15, this is my code working.

<script>
    

        
           var columns = [];

                function getDT() {
                    $.ajax({
                      url: "index2.php",
                      success: function (data) {
                        data = JSON.parse(data);
                        columnNames = Object.keys(data[0]);
                        for (var i in columnNames) {
                          columns.push({data: columnNames[i], 
                                    title: capitalizeFirstLetter(columnNames[i])});
                        }
                        $('#example').DataTable( {
                          data: data,
                            columns: columns
                        } );
                      }
                    });
                }

                function capitalizeFirstLetter(string) {
                    return string.charAt(0).toUpperCase() + string.slice(1);
                }

                $(document).ready(function() {
                  
                  
                  getDT();

                  
                } );



    </script>