Summing Column Data - Using the DataTables and Editor API Part 2

In my last blog post, I gave an example of how the DataTables API can be used to extract data from a CloudTable, and then use that data for summing information and filtering. That approach works well if the data set is small, but if there are many records, it may be easier for your users if that data is contained within a DataTable.

DataTable Summary

Office	Total On All Pages	Total On Current Page

Like last time, we'll break down the code to do this.

First, we need to iterate through the rows in the table to calculate the salary for each office. This time, we're also keeping a total for the current page, so depending on the value of onPage the function will calculate either the global total for all the records, or just the ones on the current page.

// Create an object that contains all the salary information
function calculateSalaries(onPage) {
  var obj = {};

  table.rows({page: onPage? 'current' : 'all'}).every(function(idx) {
    var data = table.row(idx).data();

    data['l-5'].forEach(function(o) {
      var office = JSON.parse(o['dp-12'])[0].text;

      if (obj[office] === undefined) {
        obj[office] = {
          total: parseInt(data['dp-11']),
          colour: o.color
        };
      }
      else {
        obj[office].total = parseInt(data['dp-11']) + obj[office].total;
      }
    });
  });

  return obj;
}

We'll store the results this time inside a DataTable. This has the benefit of being orderable, so would help if there are many records in the table. Searching could also be enabled to give even greater flexibility. The table is initialised without any records, and a click event handler will search the CloudTable.

// Initialise a DataTable for the counts
var salaries = $('#salaries').DataTable({
  // Disable features
  searching: false,
  paging: false,
  info: false,
  columnDefs: [{
    // Render the currency field in the salaries table
    targets: [1,2],
    render: $.fn.dataTable.render.number(',', '.', 0, '$')
  }],
  initComplete: function() {
    // Add filtering when a record is clicked upon
    $('#salaries tbody').on('click', 'tr', function() {
      var office = $(salaries.row(this).data()[0]).text();
      if (table.search() === office) {
        table.search('').draw();
      }
      else {
        table.search(office).draw();
      }
    });
  }
});

And finally, each time the CloudTable is redrawn, we'll recalculate the totals for the current page. The DataTable is cleared, and the new records added.

// Update the salaries table on each draw of the CloudTable
table.on('draw', function() {
  var onPage;	

  table.columns([1,5,6]).visible(false);

  // Only need to get the full totals once
  if (total === undefined) {
    total = calculateSalaries(false);
  }

  onPage = calculateSalaries(true);

  // Empty and then recreate the salaries table
  salaries.rows().remove();

  // Add the rows with a bit of colour so they match the CloudTable's pill
  for (const key in total) {
    salaries.row.add([
      '<span class="ct-content__link ct-content__pill" style="background: '
        + total[key].colour
        + '; color: #000000">'
        + key
        + '</span>',
      total[key].total,
      onPage[key] === undefined? 0 : onPage[key].total
    ]);
  }

  // Redraw the tables now we've added the rows
  salaries.draw();
});

If you have any suggestions for a future blog post, please let us know - we'd love to tailor them to the needs of the community.