Subtotal for Second Level Grouping

Like many others before me, I would like to have a subtotal for groupings beyond level 1 in a grid. I have been fiddling around with code for a few days, trying to piecemeal bits of code together into solution. I am not fluent enough in Javascript though and I am really not making any progress. Here’s what I was trying to achieve:

  1. For each sublevel, pull all the values from a particular field and sum them into a var.
  2. Prepend a table row before the next sublevel grouping:
    a. New TR must have same number of TD as all other TR
    b. Sum of field should go in the correct corresponding TD in the new TR
  3. Repeat for all sublevels

Anyone willing to help me out with any of these items? Or has anyone made a similar solution already that I haven’t found?

2 Likes

Following…

@TootsieTor35028 Ooh I love a good coding challenge, and I can see this is something that has been requested a lot in the forums.

This video will provide a demonstration of how this solution works, as well as a walkthrough of what the below code does:

Prerequisites:

  • A fair understanding of using JavaScript in Knack
  • A grid view (view_xx) that has:
    • One or more groupings
    • One or more number/equation/currency fields that you want to calculate (field_xx, field_yy)
    • ZERO column summaries

Compatibility
This code conflicts with the KTL library when removing the colspan value of grouped rows, as KTL automatically reapplies the colspan for grid views. If you want to utilise both, you’ll need to insert a MutationObserver that prevents this being reapplied.

Method:

  1. Insert the following code into your Knack JavaScript editor
  2. Replace view_xx, field_xx, and field_yy with your own view and field keys
    Note that your calcFields MUST be number, equation, or currency fields
  3. Update the calcField item attributes depending on how you want to output the calculated value
$(document).on('knack-records-render.view_xx', async function(event, view, records){

  calcFields = [
    { key: 'field_xx', calc: 'sum', comma: true, decimals: 2, pre: '$', post: '' },
    { key: 'field_yy', calc: 'sum', comma: true, decimals: 2, pre: '$', post: '' }
  ]
  addGroupCalculations(view, calcFields);

});

function addGroupCalculations(view, calcFields) {

  // Determine how to source the column structure based on the view type
  const viewType = Knack.models[view.key].view.type;
  viewColumns = {
    table: Knack.models[view.key]?.view?.columns,
    search: Knack.models[view.key]?.view?.results?.columns
  }

  // If the view type is not table or search, then throw an error
  if (!viewColumns[viewType]) {
    throw new Error('Invalid view type. Must be a table or search type view');
  }

  const columns = viewColumns[viewType];
  console.log('columns',columns)

  $(`#${view.key} .kn-table tbody`).prepend(`<tr class="kn-table-group kn-group-level-0"><td>Total</td></tr>`); // Add totals row to top of grid
  
  // For each group row...
  $(`#${view.key} .kn-table tbody .kn-table-group`).each(function() {
    const $row = $(this);
    const level = $row.attr('class').match(/kn-group-level-(\d)/); // Get the group level
    
    $(this).find('td').removeAttr('colspan'); // Remove the default colspan for group rows

    // Add table cells on group rows
    columns.filter(column => column.grouping === false).slice(1).forEach(column => { // Ignore the first column, as it's reserved for the group title
      const fieldKey = column?.id || ''; // Get the field key for each column if it exists
      $row.append($(`<td style="text-align:${column.align}" class="${fieldKey}"></td>`)); // Add new row cells with their respective field key
    });

    // For each calcField array item...
    calcFields.forEach(function(field) {
      const value = calcValues($row.nextUntil(`.${level[0]}`), field); // Sum all values up until the next same group level in the grid
      const formattedValue = formatValue(value, field); // Format the value based on the specified attributes
      $row.find(`.${field.key}`).text(formattedValue); // Add the value to the group row's cell
    });

  });

  $(`#${view.key} .kn-table tbody`).append($(`#${view.key} .kn-table tbody .kn-group-level-0`)); // Move the total row to the end of the grid
      
}

// Calculates all found field values based on the chosen calculation and returns the calculated value (i.e. sum, average, min, or max)
function calcValues(elements, field) {
  const values = elements.find(`td.${field.key}`).map(function() {
    return parseFloat($(this).text().replace(/[^\d.-]/g, '')) || 0;
  }).get();
  switch (field.calc) {
  case 'sum':
    return values.reduce((total, num) => total + num, 0);
  case 'average':
    return values.reduce((total, num) => total + num, 0) / values.length;
  case 'min':
    return Math.min(...values);
  case 'max':
    return Math.max(...values);
  default:
    throw new Error('Invalid calculation type. Use sum, average, min, or max.');
  }
}

// Formats a value based on the attributes that are loaded in
function formatValue(value, field) {
  const comma = field?.comma || false;
  const decimals = field?.decimals || 0;
  const pre = field?.pre || '';
  const post = field?.post || '';
  let newValue = value;
  newValue = newValue.toFixed(decimals);
  newValue = comma ? Knack.formatNumberWithCommas(newValue) : newValue;
  newValue = pre + newValue + post;
  return newValue;
}

Please feel free to share your feedback, or buy me a coffee if this has been valuable to you.
If you need help installing/modifying this, please contact me privately.

5 Likes

Mind officially blown :brain: :boom: :clap::clap::clap:

1 Like

This is amazing; thank you! I will give it a try today. ChatGPT and I have been putting something together that sort of works, but is no where near as elegant as your solution.

Here is a screenshot of where I was at with it. This morning I started getting double subtotals and the formatting is still lacking.

@StephenChapman I dropped your code into my project and it is almost perfect. In my case, I am summing 4 columns, starting in column 3. It looks like the code did not actually remove the “colspan” from the td for the group row, which result in the summaries going off the table in extra cells.

Also, for some reason, I got duplicate summaries, so 8 new cells instead of 4.

1 Like

Thanks @TootsieTor35028, I’ve messaged you privately to try and help diagnose.

Oops, I didn’t account for search views in my testing! Updated code now with following changes:

  • Updated handler to records-render instead of view-render
  • Added logic to determine column structure based on view type, as it differs between table (grid) and search.

EDIT: Turns out that this conflicts with the KTL library when removing the colspan value of grouped rows, as KTL automatically reapplies the colspan for any grid/search operations. If you want to utilise both, you’ll need to insert a MutationObserver that prevents this being reapplied.

2 Likes

This is amazing! Thank you Stephen!
But as they say, much wants more. Would it be possible to have a piece of code that would only show the grouping header when there is more than one table row in that grouping? In your example, under “Commercial” there is only one entry, so the entry and the sum are the same number. I have a table with mostly single entries, so not having those single entries grouped would clean things up quite a bit.

If I would have this option, I would then include the grouped field twice, so that it could be used once for grouping, and also as the first column to identify the ungrouped single entries.

Thanks in advance and enjoy your coffee!