Totaling Items

When using a Data Grid component for table style data sets, it's often desirable to perform calculations on the rows of the table.  For example, on an expense report, you may want to calculate a total amount by adding all of the line totals.


To perform this type of logic, open the settings for the component that will store the calculated value and click the data tab.  Click the Calculated Value panel to access the advanced rule editor.  Here you can write any javascript necessary to do the calculations.  In the example below, a simple sum of all Amount values is performed, but similar logic may be used to perform averages, differences, or any other logic that meets your specific business need.  A working knowledge of JavaScript is expected to be able to leverage this example in a meaningful way. 

This example is just a model for how to loop through each item in the Data Grid.  You may need to modify this example to fit your specific use case and handle error conditions.  This example assumes a Data Grid component with the API Property Name of expenseLines and a Currency Component with an API Property Name of expenseAmount in the grid.

//Initialize a variable to store the calculated amount.
var amount=0;
 
//Basic check to make sure there is data to calculate.
if(data.expenseLines[0].expenseAmount)
{
    //Loop through each line.
    data.expenseLines.forEach(function(item) {
        if(item.expenseAmount){
            //Add the line's data to the total be tracked.
            amount = amount + item.expenseAmount;
        }
    });
}
 
//Assign the calculation to the field.
value = amount.toFixed(2); 
CODE

Increment Row Number in a Data Grid Table


Below is an example to increment a Number field in a edit grid with an line number with a specified offset.  In a form you may have 3 number controls, "Start Number", "Step By", and then Row Number.  Row Number would be present in a data grid called "dg1".

if(data.dg1.indexOf(row)===0)
{
    value = data.startNumber;
}
else
{
    value = data.dg1.indexOf(row)*data.stepBy + data.startNumber;
}
CODE

A demonstration of this rule in action is available here.


A download of the form is available here.