Subtotal for Second Level Grouping

API & Customization Ask Developers
3

@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:

image
image951×603 36 KB

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