CODEWITHSUNDEEP
javascripthtmljqueryajaxdatatables
BeerusDev
BeerusDev

Reputation: 1509

Using rowGroup in DataTables

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');
            }
        } );
    } );

Upvotes: 0

Views: 1755

Answers (1)

andrewJames
andrewJames

Reputation: 21902

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:

enter image description here

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:

  1. 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).

  1. 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.

  1. 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.

  1. 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).

Upvotes: 1

Related Questions