Subtotal data table values

Forms SharePoint Online
1

Hi all - I'm using a data table to add individual lines with costs associated, and wanted to show users the total sum of the "cost" column within the data table in a separate field ("total cost") after each new row is added to the data table.

Can anyone help with the javascript for this?

2

Hello @Gabegia,

You can calculate the total by column when the line is added/removed within DataTable control using the code:

fd.control('DataTable1').$on('change', function(value){
    var totalDataTable = 0;
    console.log(value)
    value.forEach(function(line){

        if(line.Column1){
            totalDataTable += line.Column1;
        }

    });
    fd.field('Total').value = totalDataTable;
});
3

Thanks mnikitina -
Can you confirm that I should just put putting the JS in the current form JS area and the fields I would need to replace in your code with mine here would be:

  1. line.Column1 -- replace Column1 with the column to sum values from
  2. fd.field('Total') -- replace Total with the form field to update

I'm also including in quantity, so I've tweaked it a little, but have no coding background so I'm 100% guessing =)
(costEach column multiplied by quantity column within the data table)

For some reason it's not populating anything to the field. Here's what I'm using:

fd.control('DataTable1').$on('change', function(value){
var extendCost = 0;
var totalDataTable = 0;
console.log(value);
value.forEach(function(line){

    if(line.costEach){
        extendCost = line.costEach * line.quantity;
        totalDataTable += line.extendCost;
    }

});
fd.field('RequestAmount').value = totalDataTable;

});

Thanks as always for your amazing help!

4

Hello @Gabegia,

You need to add your code to JS editor, Current form tab:
image

You also need to wrap the code inside spRendered() event like this:

fd.spRendered(function() {
  fd.control('DataTable1').$on('change', function(value){
  var extendCost = 0;
  var totalDataTable = 0;
  console.log(value);
  value.forEach(function(line){

      if(line.costEach){
          extendCost = line.costEach * line.quantity;
          totalDataTable += line.extendCost;
      }

  });
  fd.field('RequestAmount').value = totalDataTable;
  });
});

And yes, you need to check the internal names of control DataTable1 and columns costEach, quantity.

If the code doesn't work, check are there any errors in the browser console(F12).

5

Hey Mnikitina - thanks again, that worked!

I also found the thread here: Work with Data Table using JS on Plumsail Forms for SharePoint which allowed me to update each row values in addition to the grand total.

Gabe

1 Like