Using rowGroup in DataTables

Question

For this project I am working on, I am pulling data via REST API to populate my DataTable. To reduce the amount of similar items displayed, I want to create DataTable subrows with a drop down based on an item in my "Deliverables" Column. The example I want to base it off is the example table shown here.

I have the column Deliverables with two options, Meeting Minutes and MSR, I want to have only those two things populate to the table, then have a click down showing everything under what "Program" it is. Here is a picture of my current DataTable, and I will attach the code below.

Here is my code: it creates a DataTable, but doesn't populate any of the data to it.

    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 = ["url1","url2","url3"];
            
        for (i=0; i < urls.length; i++) { //for loop to run through the AJAX until all URLs have been reached
          $.ajax({
            url: urls[i],
            '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)
              data = data;
              var table = $('#myTable').DataTable();
              table.rows.add( data.value ).draw();
            }
          });
        } // missing bracket
    }
    function format ( data ) {
        // `d` is the original data object for the row
        return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
            '<tr>'+
                '<td>Program:</td>'+
                '<td>'+data.Program+'</td>'+
            '</tr>'+
            '<tr>'+
                '<td>Recipient:</td>'+
                '<td>'+data.To+'</td>'+
            '</tr>'+
            '<tr>'+
                '<td>Date:</td>'+
                '<td>'+data.Date+'</td>'+
            '</tr>'+
            '<tr>'+
                '<td>Approved:</td>'+
                '<td>'+data.Approved+'</td>'+
            '</tr>'+
            '<tr>'+
                '<td>Additional Notes:</td>'+
                '<td>'+data.Notes+'</td>'+
            '</tr>'+
        '</table>';
    }
    $(document).ready(function() {
        var table = $('#myTable').DataTable( {
            "columns": [
                {
                    "className":      'details-control',
                    "orderable":      false,
                    "data":           null,
                    "defaultContent": ''
                },
                { "data": "Deliverable" }
            ],
            "order": [[1, 'asc']]
        } );
loadData();
          
        $('#myTable tbody').on('click', 'td.details-control', function () {
            var tr = $(this).closest('tr');
            var row = table.row( tr );
      
            if ( row.child.isShown() ) {
                // This row is already open - close it
                row.child.hide();
                tr.removeClass('shown');
            }
            else {
                row.child( format(row.data()) ).show();
                tr.addClass('shown');
            }
        } );
    } );

I think the idea from this answer was for you to gather all the data from your 3 source URLs into a single JavaScript object - and then to use that data to populate your DataTable. — andrewJames
– andrewJames, Commented Aug 28, 2020 at 16:43
The link in your question is to the DataTables home page. Did you mean you want to implement this example? — andrewJames
– andrewJames, Commented Aug 28, 2020 at 16:44
@andrewjames check my most recent edit, I followed that but I can't get the data to populate? — BeerusDev
– BeerusDev, Commented Aug 28, 2020 at 17:15
OK - understood. I have proposed an approach which may help. — andrewJames
– andrewJames, Commented Aug 28, 2020 at 20:56

andrewJames · Accepted Answer · 2021-07-27 23:11:16Z

1

Here is an approach which uses the DataTables child rows approach, combined with your approach which needs to merge data from three different URLs.

I have modified the logic which handles the three URLs so that all the data is fetched and combined before the DataTable is initialized.

This uses test data at a real URL: https://jsonplaceholder.typicode.com/posts. In my case, I use this same URL three times - each time it fetches the same 100 records.

The end result looks like this:

Here is the full example:

<!doctype html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <style>
  td.details-control {
    background: url('details_open.png') no-repeat center center;
    cursor: pointer;
  }
  tr.shown td.details-control {
    background: url('details_close.png') no-repeat center center;
  }
  </style>
</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%"></table>

</div>

<script type="text/javascript">

getTableData();

function initializeTable(dataSet) {
  $(document).ready(function() {

    var table = $('#example').DataTable( {
      data: dataSet,
      columns: [
        {
          className: 'details-control',
          orderable: false,
          data: null,
          defaultContent: ''
        },
        { title: 'User ID', 
          data: 'userId' },
        { title: 'ID', 
          data: 'id' },
        { title: 'Title',
          data: 'title' }
      ]
    } );

    // event listener for opening and closing child rows:
    $('#example tbody').on('click', 'td.details-control', function () {
      var tr = $(this).closest('tr');
      var row = table.row( tr );
 
      if ( row.child.isShown() ) {
        // This row is already open - close it
        row.child.hide();
        tr.removeClass('shown');
      }
      else {
        // Open this row
        row.child( format(row.data()) ).show();
        tr.addClass('shown');
      }
    } );

  } );
}

async function getTableData() {

  let dataSet = [];

  var urls = [ 'https://jsonplaceholder.typicode.com/posts', 
               'https://jsonplaceholder.typicode.com/posts', 
               'https://jsonplaceholder.typicode.com/posts' ];

  for (var i = 0; i < urls.length; i++) {
    try {

      let response = await fetchUrl(urls[i]);

      // combine each response into one variable:
      if (Array.isArray(response)) {
        dataSet = [...dataSet, ...response];
      } else { // assume it's an object {...}
        dataSet.push(response);
      }

    } catch(err) {
      console.log(err);
    }
  }

  //console.log(dataSet);
  initializeTable(dataSet);

}

function fetchUrl(url) { 
  return $.ajax( {
    url: url,
    type: 'GET'
  } );
};

function format ( d ) {
  // `d` is the original data object for the row
  return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">' +
    '<tr>' +
      '<td>Title:</td>' +
      '<td>' + d.title + '</td>' +
    '</tr>' +
    '<tr>' +
      '<td>Body:</td>' +
      '<td>' + d.body + '</td>' +
    '</tr>' +
    '<tr>' +
      '<td>Extra info:</td>' +
      '<td>And any further details here (images etc)...</td>' +
    '</tr>' +
  '</table>';
}

</script>

</body>
</html>

I also downloaded the two button image files details_open.png and details_close.png from the DataTables web site.

I don't know why you are not seeing any of your data displayed in the table, but in my case, I force the DataTables initialization step to wait until all data has been fetched before I perform the table initialization. That may be one reason why you do not see any data.

Update

The JSON response looks like this:

{
    "value": [{
        "Notes": "Example Notes\n",
        "Approved": "Yes",
        "Program": "AMMO",
        "Date": "12/23/2018",
        "To": "[email protected]",
        "Deliverable": "Monthly Status Report (MSR)"
    }, {
        "Notes": "Example Notes\n",
        "Approved": "Yes",
        "Program": "AMMO",
        "Date": "03/30/2020",
        "To": "[email protected]",
        "Deliverable": "Meeting Minutes"
    }, {
        "Notes": "Example Notes\n",
        "Approved": "Yes",
        "Program": "AMMO",
        "Date": "12/23/2018",
        "To": "[email protected]",
        "Deliverable": "Monthly Status Report (MSR)"
    }, {
        "Notes": "Example Notes\n",
        "Approved": "Yes",
        "Program": "AMMO",
        "Date": "03/30/2020",
        "To": "[email protected]",
        "Deliverable": "Meeting Minutes"
    }, {
        "Notes": "Example Notes",
        "Approved": "No",
        "Program": "AMMO",
        "Date": "09/22/2022",
        "To": "[email protected]",
        "Deliverable": "Monthly Status Report (MSR)"
    }]
}

I will assume this is the structure of the response from all 3 of the URLs you want to use.

Because each response is nested in an object { "value": [...] }, we have to do some extra work to access the data correctly.

Changes needed from the approach shown above:

      // combine each response into one variable:
      if (Array.isArray(response)) {
        dataSet = [...dataSet, ...response];
      } else { // assume it's an object {...}
        dataSet.push(...response.value);
      }

In the above section of the code, I changed one line from this:

dataSet.push(response);

to this:

dataSet.push(...response.value);

This does 2 extra steps:

(1) It access the value array in each response.

(2) It flattens that array into each separate item in the array, so that they can be combined into a new array (which will include data from all 3 URLs).

July 27 Update

Not a solution, just some notes relating to your latest comments and the updateOutsideCount function:

The function uses an if statement here:

if (moment($('#dpicker').val()).isBetween(searchMon, searchFri)){

I am not sure what the purpose of this statement is. See also points (2) and (3).

Be aware than when you use isBetween() you need to be careful. The match is "exclusive". This means that if you have a date of 07/19/2021 and you want to check if this date is between 07/19/2021 and some later date, then this will fail.

isBetween() means "later than the first date" and "before the second date".

It does not mean "later than or equal to the first date".

You can change this behavior - see below.

Instead of the if statement mentioned in (1) (or maybe as well as that statement), you need an if statement for your flattened map:

transformedResults.flatMap(t=>t.Days)

The above statement generates a list of every date in your data set. So you need to throw away those dates which are outside the selected week:

transformedResults.flatMap(t=>t.Days).forEach((dayArray) => {
  if ( moment(dayArray.Date).isBetween(searchMon, searchFri, undefined, '[)') ) {
    switch(dayArray.Day) { ... }
  }
}

Note that the isBetween function now includes [) as a parameter. The [ means the first date is inclusive and the ) means the second date is exclusive.

Without this [ we would fail to pick up the Monday date you want.

Within your updateOutsideCount function you can declare this:

var totalOutsideCount = {P:0,TW:0,TRV:0,NR:0,PTO:0,H:0};

And then in the forEach((dayArray) loop you can increment the relevant values:

totalOutsideCount[dayArray.Status]++;

That gives you the total counts you need.

Altogether, my version of your updateOutsideCount function is as follows:

function updateOutsideCount() {
  console.clear();
  var totalOutsideCount = {P:0,TW:0,TRV:0,NR:0,PTO:0,H:0};
  moment(moment($('#dpicker').val()));
  if (moment($('#dpicker').val()).isBetween(searchMon, searchFri)) {
    transformedResults.flatMap(t => t.Days).forEach((dayArray) => {

      if (moment(dayArray.Date).isBetween(searchMon, searchFri, undefined, '[)')) {

        switch (dayArray.Day) {
          case 'Monday':
            console.log(dayArray.Status);
            break;
          case 'Tuesday':
            console.log(dayArray.Status);
            break;
          case 'Wednesday':
            console.log(dayArray.Status);
            break;
          case 'Thursday':
            console.log(dayArray.Status);
            break;
          case 'Friday':
            console.log(dayArray.Status);
            break;
        }
        totalOutsideCount[dayArray.Status]++;

      }

    });
    console.log(totalOutsideCount);
  }
}

When I select a date of 07/19/2021 this prints the following to the console:

Console was cleared. 
P
P
P
NR
P
Object { P: 4, TW: 0, TRV: 0, NR: 1, PTO: 0, H: 0 }

That is probably all I can do to help you with this. Good luck! I think you already have the code you need for updating the HTML (and clearing it).

edited Jul 27, 2021 at 23:11

answered Aug 28, 2020 at 20:53

andrewJames

22.6k11 gold badges32 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

32 Comments

andrewJames Over a year ago

Happy to help within the limitation of comments (but not in a room). It sounds like you probably need to create a new question with a MRE - that way you can get help from the entire community.

andrewJames Over a year ago

Sorry to hear about the voting troubles. It happens, sometimes. Your DataTable logic is complex, and it would take time to review. I will look at it if/when I have some time, but I have no idea when that may be.

andrewJames Over a year ago

OK - some notes added. Hope they help - but I think you should try to figure out the rest for yourself (or wait until you can ask questions again). All the best!

andrewJames Over a year ago

I don't know - but bear in mind that your jQuery ajax call (started by loadData()) is asynchronous. The DataTable initialization is also asynchronous. Neither one will wait for the other. You have to ensure you have finished fetching your data first before you try to initialize your table. There are lots of questions about this async poblem - here is one. I cannot run your ajax-based Fiddle, so this is a complete guess on my part.

andrewJames Over a year ago

Most of the time, you avoid this async data fetching problem by placing your ajax call inside the DataTables definition - then DataTables takes care of things for you. But you don't have to do that, as long as you work around the async problem as shown in various SO answers.

|

Collectives™ on Stack Overflow

Using rowGroup in DataTables

1 Answer 1

32 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

32 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related