Capture total of associated item currency column dynamically in parent item

Applies to:
Crow Canyon NITRO activated sites in:
SharePoint Online and On-Premises 2013/2016/2019

Description

The associated items column in NITRO forms has a feature that show aggregate values from the child items. For example, it can show sum/min/max/count of child items in the associated items grid. There is also an option to use JavaScript for advanced calculations for aggregates. However, this value is in a grid and not in any column in the main item. If this is required, then the form event action feature of NITRO forms can be used to update aggregate value in a column in the main item on addition of a new child item or a change of any value in any child item.

Sample list schema and use case

  1. Standard SharePoint list
  2. ‘Time Tracking’: NITRO associated items column in the main list
    1. ‘Cost’: Currency column in the associated items grid
  3. ‘Total Cost’: Currency column in the main list
    1. The aggregate of ‘Cost’ column is updated in ‘Total Cost’ column in the main item. That is, the sum of the cost value in the child items is set in total cost column on the main item.

Steps to configure

  1. Navigate to Crow Canyon NITRO Forms designer for the list -> Form Event Actions (under Advanced section in the left-hand panel) and configure FEA on column value change as shown below:

Update form controls action:

Script:

var linkedColumnName = "AssociatedColumnInternalName";
var itemCost = "Cost";

var objLinkedColumn = _ccs_FormUI.linkItemsColumnSettings[linkedColumnName];
var grid = $("#" + objLinkedColumn.ControlID).data("kendoGrid");
var gridData = grid.dataSource.data();
var itemCount = gridData.length;
var TotalCost = 0;
for (var i = 0; i < itemCount; i++) {
    var objRow = gridData[i];
    var rating = Number(objRow[itemCost]);
	TotalCost = TotalCost + rating;
}
return TotalCost;

Note:

  • Replace ‘AssociatedColumnInternalName’ with the internal name of ‘NITRO Associated Items’ column in the main list.
  • Replace ‘Cost’ with the internal name of the column from the associated list that is to be used for calculating the aggregate.

Sample output