Sum or count items in nested fields
Users often benefit from being able to see values calculated based on data they've entered. When nested fields are used with calculated values, users can ensure they've inputted the appropriate data and/or provide additional information to customers.
A technician who has just finished a job records the labor and parts used while on-site. A calculated field shows the total cost of all parts used, allowing the technician to quickly check for major errors, and notify the customer of the cost of service.
In the Form section, create a nested grid field to contain the list of parts used.
Note: Set the 'Field Name' for this field to something easy to remember, e.g. 'Parts List'
Click the 'Customize nested form' button and create two fields:
- An 'input' type field for 'Part Description'
- A 'money' type field for 'Line Cost'
Note: Set the 'Field Name' for this field to something easy to remember, e.g. 'LineCost'.
Click on 'Nested Field Properties' and display the appropriate columns
- Save the nested grid field
Create a new 'Money' type field called 'Total Parts Cost'
- Click on the 'Abc' icon to the right of the field type, and change the selection to 'formula calculation'
- In the 'Formula' field, enter the following text: SUM(PartsList.LineCost). If you used different field names for your parts list nested grid or your line cost field, substitute the field names appropriately. Be sure to use the 'Field name' value and not the 'Field label' value when using formulas calculations.
Repeat step 5 to create a numeric 'Parts Count' field and use the following formula: COUNT(PartsList)
- Save the form, and publish the application
Several types of fields can have their values calculated from other values on the same form. Values can be formatted as plain text, dates/times, as well as numbers. See the 'Formula Field Syntax' documentation for Flowfinity Actions to see a comprehensive list of formulas and examples. Calculated values can also be used as inputs into other calculations.