Using the AVG function in InfoPath

Applies to: InfoPath 2003

Use rules and the avg() function in formulas to calculate the average for a column of numbers in a repeating table.

ADVERTISEMENTS

Problem

You want to recreate the functionality used in the Applicant Rating sample InfoPath form, but you do not want to write custom code to calculate the average for ratings.

Solution

Use rules and the avg() function in InfoPath to calculate the average for a column of numbers in a repeating table.

Discussion

You can accomplish this functionality as follows:

  1. Create a New Blank Form in InfoPath.
  2. Add a Table with Title to the InfoPath form.
  3. Add a Repeating Table control with 7 columns to the table.
  4. Add a footer to the Repeating Table by opening its Properties dialog box and checking Include footer under the Options section on the Display tab. Uncheck the Allow users to insert and delete rows check box under the Defaut settings on the Data tab. Click on the OK button to close the dialog box.
  5. Right-click on field2 and select Change To > Option Button from the context menu that appears.
  6. Double-click on field2 to open its Properties dialog box and change its Data type to Whole Number (integer) under the Binding section on the Data tab. Also type in a 1 in the Value when selected field under the Binding section on the Data tab. Click on the OK button to save the changes and close the dialog box.
  7. Repeat step 5 for field3.
  8. Right-click on field3 and select Change Binding... from the context menu that appears.
  9. Select field2 in the Option Button Binding dialog box and click on the OK button to save the changes and close the dialog box.
  10. Double-click on the option button that used to be bound to field3 and set the Value when selected field to 2.
  11. Repeat steps 5, 8, 9, and 10 for the rest of the option buttons, but set their Value when selected field to 3, 4, 5, respectively, in step 10. Leave the Value when selected field blank (empty) for the last option button (n/a).
  12. Double-click on the last option button in the row of option buttons, and check This button is selected by default.
  13. Open the data source pane and delete field3, field4, field5, field6, and field7.
  14. Rename group1 to ratings.
  15. Rename group2 to jobExperience.
  16. Rename field1 to description.
  17. Rename field2 to experienceRating.
  18. Add a new field (Element) with the name averageJobExperience and data type Decimal (double) to the ratings group.
  19. Add another new field (Element) with the name helperField and data type Text (string) to the ratings group.
    Your Main data source should now resemble the following figure:

    Figure 1. Main data source of the InfoPath form.
  20. Drag the averageJobExperience field to the Repeating Table and drop it in the footer of the repeating table.
  21. Double-click on the averageJobExperience field, click on the Format... button on the Text Box Properties dialog box and set the Decimal places to 1.
  22. Double-click on the description field and check the Read-only check box on the Display tab of the Text Box Properties dialog box.
  23. Add 5 default rows to the repeating table as follows: Go to Tools > Default Values.... Right-click on jobExperience and select Add another jobExperience below from the context menu that appears. Repeat this step 3 more times. Expand each jobExperience node, click on the description node under the first jobExperience node, and then type in "Experience relevant to position" in the text field for the Default value of the description node. Type in "Quality of work" for the default value of the description node under the second jobExperience node, "Career goals fit position" for the default value of the description node under the third jobExperience node, "Motivation and enthusiasm" for the default value of the description node under the fourth jobExperience node, and "Problem solving skills" for the default value of the description node under the fifth jobExperience node. Click on the OK button to save the changes and close the dialog box. The repeating table will now be populated with these 5 rows as soon as the form is opened.
  24. Modify the table and controls to resemble the following figure:

    Figure 2. The InfoPath form in design mode.
  25. The form should resemble the following figure when previewing it:

    Figure 3. The InfoPath form in preview mode.
  26. The averageJobExperience field must be set by an event (e.g. a click on a button or a text changed in a field) outside of the repeating table. You can do this by simulating an OnChange event on the helperField and set the averageJobExperience field through a rule on the helperField field. Do the following to get this done: Drag the helperField from the Data Source pane to the form and drop it anywhere on the form. Double-click on the helperField, create a Rule, and add an Action for the Rule that says:
    Set a field's value: averageJobExperience = avg(experienceRating)
    This rule will calculate the average value using the avg() function and the values of the selected option buttons. To simulate the OnChange event, you need to ensure that the value of the helperField is changed whenever an option button is clicked. You can do this by setting 2 actions on a rule on the experienceRating field. Add a Rule to the experienceRating field and then create 2 actions for this rule. The first Action must say:
    Set a field's value: helperField = ""
    The second Action must say:
    Set a field's value: helperField = "calc"
    It does not matter what you set the helperField field to in the second action. As long as you set it to something other than what you set it to in the first action (an empty string in this case), an OnChange event will be triggered on the helperField field, which will then in turn trigger the calculation of the average in the averageJobExperience field whenever an option button is clicked.
  27. Remove the helperField field from the form, but NOT from the Data Source. The rule you set previously on the helperField field will remain intact.

You should now have a form that calculates the average of ratings using rules and the avg() function in InfoPath without writing any custom code.

 


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