Using InfoPath Math functions with repeating fields as input
Learn how to use the avg, sum, min, max, and eval functions in InfoPath together with repeating nodes to perform calculations.
Repeating nodes are an important concept to understand in InfoPath, because they form the basis for repeating controls like Repeating Tables, Repeating Sections, and lists like Multi-Select List Boxes, Drop-Down List Boxes, and List Boxes.
In addition, the following InfoPath functions that are used in mathematical calculations all take repeating fields as input: avg, sum, eval, min, and max.
Microsoft Office Online gives the following explanation for these InfoPath functions:
- avg - Calculates the average value of all of the numerical values in the argument. Example: avg(field1).
- sum - Calculates the sum of the arguments. Example sum(field1).
- min - Returns the smallest number in the argument. Example min(field1).
- max - Returns the largest number in the argument. Example max(field1).
- eval - Returns the values of a set of fields. The first argument defines the set of fields; the second argument defines the expression to calculate for the set of fields. Usually, the eval function is nested within a function that operates on a set of fields, such as sum or avg. Example: sum(eval(group2, "field1 * field2")).
Let's take a look at how the InfoPath Math functions described above would work in conjunction with a repeating field in InfoPath:
- In InfoPath, create a new Blank Form template.
- Add a Repeating Table with 1 column to the InfoPath form template.
The Main data source of the InfoPath form template should now look like the following figure:
Figure 1. Repeating group node in the Main data source of an InfoPath form template.
Do you see that blue square with a small down arrow on the folder icon for the group2 node? That symbol indicates that group2 is a repeating node.
What this means is that you can have multiple group2 nodes under group1. What this also means is that field1, which is a child node of group2 and which generally contains a value can serve as the input field for Math functions that take repeating fields as their input.
Continue designing the InfoPath form template as follows:
- Add 5 default rows to the repeating table with the values of 2, 5, 8, 10, and 3.
- Add 5 Text Box controls to the InfoPath form template. Your InfoPath form template should now resemble the following figure:
Figure 2. InfoPath form template to perform Math calculations.
and have the following data source:
Figure 3. Data source of InfoPath form template to perform Math calculations.
- Set the following Default Value for each one of the Text Box controls respectively (refer to the instructions below that describe how to set the Default Value of a control using a formula):
sum(eval(group2, "my:field1 * my:field1"))
To set the Default Value of a control to the results of a formula containing an InfoPath function:
- On the Properties dialog box for that control, click the formula button behind the Value field under the Default Value section.
- On the Insert Formula dialog box, click Insert Function.
- On the Insert Function dialog box, select the function you want to use, and then click OK.
- On the Insert Formula dialog box, double-click on the text double click to insert field.
- On the Select a Field or Group dialog box, expand the group1 node, expand the group2 node, select field1, and then click OK.
- On the Insert Formula dialog box, click Verify Formula to check for any errors. Click OK to close the message box.
- On the Insert Formula dialog box, click OK.
- On the Properties dialog box for the control, click OK.
Now when you click Preview, you should see the results of the math calculations.
Figure 4. Example of using Math functions in InfoPath.
For more examples on how to use Math functions in InfoPath, see
- Example using AVG function in InfoPath
- Example using MIN function in InfoPath
- Example using MAX function in InfoPath
- Example using SUM function in InfoPath
Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.
Related InfoPath Articles:
- InfoPath sum function to calculate repeating table sum
- Calculating time differences in InfoPath without custom code
- Select the latest date from a repeating table in InfoPath
- Selecting the minimum date in an InfoPath repeating table using formulas
- Using the AVG function in InfoPath