Using InfoPath Math functions with repeating fields as input

Applies to: InfoPath 2010

Learn how to use the avg, sum, min, max, and eval functions in InfoPath together with repeating nodes to perform calculations.

ADVERTISEMENTS

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:

  1. In InfoPath, create a new Blank Form template.
  2. 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:

    Repeating group node in the Main data source of an InfoPath form template

    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:

  1. Add 5 default rows to the repeating table with the values of 2, 5, 8, 10, and 3.
  2. Add 5 Text Box controls to the InfoPath form template. Your InfoPath form template should now resemble the following figure:

    InfoPath form template to perform Math calculations

    Figure 2. InfoPath form template to perform Math calculations.

    and have the following data source:

    Data source of InfoPath form template to perform Math calculations

    Figure 3. Data source of InfoPath form template to perform Math calculations.

  3. 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):

    avg(field1)
    sum(field1)
    min(field1)
    max(field1)
    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:

  1. On the Properties dialog box for that control, click the formula button behind the Value field under the Default Value section.
  2. On the Insert Formula dialog box, click Insert Function.
  3. On the Insert Function dialog box, select the function you want to use, and then click OK.
  4. On the Insert Formula dialog box, double-click on the text double click to insert field.
  5. On the Select a Field or Group dialog box, expand the group1 node, expand the group2 node, select field1, and then click OK.
  6. On the Insert Formula dialog box, click Verify Formula to check for any errors. Click OK to close the message box.
  7. On the Insert Formula dialog box, click OK.
  8. On the Properties dialog box for the control, click OK.

Now when you click Preview, you should see the results of the math calculations.

Example of using Math functions in InfoPath

Figure 4. Example of using Math functions in InfoPath.

For more examples on how to use Math functions in InfoPath, see

 


Related InfoPath Articles:

 

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. 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.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services