DataTables Row Group Not Posting First Data Source but Posts Everything Else

zgoforth

Hello. I have never seen or experience this issue before.

I have a SharePoint list which I am using an AJAX call to populate the DataTable, that has 5 columns of data. Company(Title), Capability, Customer, SEStatus, and Vehicles. There are about 100 companies, but on that list item, either everything else is empty (null), or there are some where only 1 column is null which is ok.

You will see below how I am grouping the data, Capabilities then Company (Title). When the table loads, it only shows Title/Company and then I can expand/collapse the rest of the data.

This probably has to do with the fact that the Capabilities column has less data than total Companies.

rowGroup: {
                        dataSrc: [
                            'Capabilities',
                            'Title'
                        ],

How can I manipulate the table/data to not post data if the only object it contains is just a Title.?

Here is my script:

function loadData() { //Initializing the AJAX Request function to load in the external list data from different subsites
            //create an array of urls to run through the ajax request instead of having to do multiple AJAX Requests               
              $.ajax({
                url: urls,
                'headers': { 'Accept': 'application/json;odata=nometadata' },
                success: function (data) { // success function which will then execute "GETTING" the data to post it to a object array (data.value)
                  
                  var table = $('#myTable').DataTable();
                  table.rows.add( data.value ).draw();
                }
              }); 
        }
        $(document).ready(function() {
            var collapsedGroups = {};
                var top = '';
                var parent = '';
                
          var table = $('#myTable').DataTable( {
            "pageLength": 50,
            "columns": [
              { "data": "Capabilities",
                visible: false    },
              { "data": "Title",
                visible: false    },
              { "data": "Customer" },
              { "data": "SEStatus" },
              { "data": "Vehicles" }
            ],
                        dom: "<'row'<'col-sm-12 col-md-10'f><'col-sm-12 col-md-2'B>>" +
                        "<'row'<'col-sm-12'tr>>" +
                        "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
                        order: [
                        [0, 'asc'],
                        [1, 'asc']
                    ],
                    rowGroup: {
                        dataSrc: [
                            'Capabilities',
                            'Title'
                        ],
                        startRender: function(rows, group, level) {
                            var all;
                            if (level === 1) {
                                top = group;
                                all = group;
                            } else if (level === 2) {
                                parent = top + group;
                                all = parent;
                                // if parent collapsed, nothing to do
                                if (!collapsedGroups[top]) {
                                    return;
                                }
                            } else {
                                // if parent collapsed, nothing to do
                                if (!collapsedGroups[parent]) {
                                    return;
                                }
                                all = top + parent + group;
                            }
            
                            var collapsed = !collapsedGroups[all];
                            console.log('collapsed:', collapsed);
            
                            rows.nodes().each(function(r) {
                                r.style.display = collapsed ? 'none' : '';
                            });
                            //Add category name to the <tr>.
                            return $('<tr/>')
                                .append('<td colspan="5">' + group + ' (' + rows.count() + ')</td>')
                                .attr('data-name', all)
                                .toggleClass('collapsed', collapsed);
            
            
                        }
            
                    }
          } );
            
         loadData();
         $('#myTable tbody').on('click', 'tr.dtrg-start', function() {
                    var name = $(this).data('name');
                    collapsedGroups[name] = !collapsedGroups[name];
                    table.draw(false);
                });
        } );

Would I alter the success function? Put some sort of conditional around the .draw ?

zgoforth

Here is what I tried but nothing is posted to the datatable?

$.ajax({
                url: urls,
                'headers': { 'Accept': 'application/json;odata=nometadata' },
                success: function (data) { // success function which will then execute "GETTING" the data to post it to a object array (data.value)
                  if (data.Title !== null && data.Capabilities == null && data.Customer == null && data.SEStatus == null && data.Vehicles == null){

                  } else {
                  var table = $('#myTable').DataTable();
                  table.rows.add( data.value ).draw();
                }
                }
              }); 

kthorngren

I don't understand what you are asking for. However data.value is an array of rows you are adding. If you want to remove rows based on a condition you will need to loop through the array. Maybe you want something like this:

                success: function (data) { // success function which will then execute "GETTING" the data to post it to a object array (data.value)
                  var table = $('#myTable').DataTable();
                  for (i=0; i<data.value.length; i++) {
                    rowData = data.value[i];
                    if (rowData.Title !== null && data.Capabilities == null && data.Customer == null && data.SEStatus == null && data.Vehicles == null){
                   table.row.add( data.value ); // Add the row if it meets the condition
                 }
                 table.draw();  // Draw the table after all the rows have been added
                }

I'm not sure what the condition is to add the row so it likely will need changed. Note the use of row.add() to add one row instead of rows.add(). After the loop use draw() to draw the table after all the rows have been added.

If this doesn't help please provide a test case showing an example of your data with a description of what you are looking for.

Kevin

zgoforth

With this I receive this error for every single row, about 50 of them:

DataTables warning: table id=myTable - Requested unknown parameter 'Capabilities' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

Here is my static example with a static dataSet where I try to mimic the data I am pulling through AJAX: https://jsfiddle.net/ac8zb6gy/4/

For some reason this is still not posting the parent row of the Capability, just showing customer? But the sample dataSet has data much like the list I am pulling from, if the array has Company, but not Capability, don't post it to the table, but if it has Capability and Company/Title, but none of the other info, it can still post to the table.

kthorngren

Your example is using array based data where your production is using objects. The data structures aren't the same so troubleshooting is difficult.

With this I receive this error for every single row, about 50 of them:

I guess that your if statement is not doing what you want. You will need to troubleshoot the if statement to filter the rows you don't want to add to the table. If you need help with this then update the test case with an example of the data structure you are working with. The loop you have in the success function and details of what you expect the results to be.

Kevin

zgoforth

@kthorngren so I am trying to fix my dynamic example on my own at the moment, and one thing I am running into now is this error:

DataTables warning: table id=myTable - Requested unknown parameter 'Capabilities' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

Why is it telling me that is an unknown parameter? The spelling is correct, and matches the column name on the SharePoint list?

when I console.log(data);, here is an example of what is logged:

value: Array(69)
    0:
        Capabilities: "Capabilities_Value"
        Vehicles: "Vehicles_Value"
        Customer: "Customer_Value"
        SEStatus: "SEStatus_Value"
        Title: "Title_Value"

I am confused because the parameter is indeed there?

Here is my JS:

function loadData() { //Initializing the AJAX Request function to load in the external list data from different subsites
            //create an array of urls to run through the ajax request instead of having to do multiple AJAX Requests
            var urls = webbAppUrl + "/_api/web/lists/getbytitle('ListName')/items?$select=Capabilities,Title,Customer,SEStatus,ContractVehicles";
               
              $.ajax({
                url: urls,
                'headers': { 'Accept': 'application/json;odata=nometadata' },
                success: function (data) { // success function which will then execute "GETTING" the data to post it to a object array (data.value)
                    var table = $('#myTable').DataTable();
                    console.log(data);
                    table.row.add( data.value ).draw; // Add the row if it meets the condition
                    
                }
              }); 
        }
        $(document).ready(function() {
            var collapsedGroups = {};
                var top = '';
                var parent = '';
                
          var table = $('#myTable').DataTable( {
            "pageLength": 50,
            "columns": [
              { "data": "Capabilities",
                visible: false },
              { "data": "Title",
                visible: false    },
              { "data": "Customer" },
              { "data": "SEStatus" },
              { "data": "ContractVehicles" }
            ],
                        dom: "<'row'<'col-sm-12 col-md-10'f><'col-sm-12 col-md-2'B>>" +
                        "<'row'<'col-sm-12'tr>>" +
                        "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
                        order: [
                        [0, 'asc'],
                        [1, 'asc']
                    ],
                    rowGroup: {
                        dataSrc: [
                            'Capabilities',
                            'Title'
                        ],
                        startRender: function(rows, group, level) {
                            var all;
                            if (level === 1) {
                                top = group;
                                all = group;
                            } else if (level === 2) {
                                parent = top + group;
                                all = parent;
                                // if parent collapsed, nothing to do
                                if (!collapsedGroups[top]) {
                                    return;
                                }
                            } else {
                                // if parent collapsed, nothing to do
                                if (!collapsedGroups[parent]) {
                                    return;
                                }
                                all = top + parent + group;
                            }
            
                            var collapsed = !collapsedGroups[all];
                            console.log('collapsed:', collapsed);
            
                            rows.nodes().each(function(r) {
                                r.style.display = collapsed ? 'none' : '';
                            });
                            //Add category name to the <tr>.
                            return $('<tr/>')
                                .append('<td colspan="5">' + group + ' (' + rows.count() + ')</td>')
                                .attr('data-name', all)
                                .toggleClass('collapsed', collapsed);
            
            
                        }
            
                    }
          } );
            
         loadData();
         $('#myTable tbody').on('click', 'tr.dtrg-start', function() {
                    var name = $(this).data('name');
                    collapsedGroups[name] = !collapsedGroups[name];
                    table.draw(false);
                });
        } );

kthorngren

                    var table = $('#myTable').DataTable();
                    console.log(data);
                    table.row.add( data.value ).draw; // Add the row if it meets the condition

data.value is an array of objects. You are using row.add() which adds one row and does not expect it to b in an array. use rows.add() to add multiple rows.

Kevin

zgoforth

Kevin,

Here is an example that I have but it doesn't post any data to the table nor does it leave any console error so I am confused because I am calling the data correctly to manipulate it (although I have heard to stay away from !== null or == !null)

Every other part of my code has no issues, so here is my success function in my AJAX call followed by some sample data in the data.d.results explaining what I will allow/what I don't want posted:

$.ajax({
        url: listUrl,
        method: "GET",
        headers: {
            "Accept": "application/json; odata=verbose"
        },
        success: function(data) {
            console.log(data);
            if (data.d != null && data.d != undefined && data.d.results.length > 0) {
                var table = $('#myTable').DataTable();
                for (i=0; i<data.d.results.length; i++) {
                    rowData = data.d.results[i];
                    console.log(rowData);
                    if (rowData.Capabilities != null && rowData.Title != null){
                    table.rows.add( rowData ).draw; // Add the row if it meets the condition
                }
  // Draw the table after all the rows have been added
              }
            }
        }
    });

Here is the sample of similar data: (The first object is good to post, the second is good to post, the third is good to post, but the fourth is the type of objects I am trying to filter out, where it only contains Title. If it only includes capability and title, then that is fine (like the third object)).

{
  "d": {
    "results": [
      {
        "Title": "Company X",
        "Capabilities": "Cyber",
        "Customer": "Customer X",
        "SEStatus": "Large Business",
        "Vehicles": "Vehicle X"
      },
      {
        "Title": "Company Y",
        "Capabilities": "Management",
        "Customer": "Customer Y",
        "SEStatus": "Large Business",
        "Vehicles": "Vehicle Y"
      },
      {
        "Title": "Company X",
        "Capabilities": "Communications",
        "Customer": null,
        "SEStatus": null,
        "Vehicles": null
      },
      {
        "Title": "Company Z",
        "Capabilities": null,
        "Customer": null,
        "SEStatus": null,
        "Vehicles": null
      },

colin

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

zgoforth

I linked one previously in the post but that error I am not facing anymore, I cannot make a test case as the actual data I am pulling is behind a internal firewall server. I will see what I can come up with