Using the AVG function in InfoPath
Use rules and the avg() function in formulas to calculate the average for a column of numbers in a repeating table.
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.
Use rules and the avg() function in InfoPath to calculate the average for a column of numbers in a repeating table.
You can accomplish this functionality as follows:
- Create a New Blank Form in InfoPath.
- Add a Table with Title to the InfoPath form.
- Add a Repeating Table control with 7 columns to the table.
- 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.
- Right-click on field2 and select Change To > Option Button from the context menu that appears.
- 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.
- Repeat step 5 for field3.
- Right-click on field3 and select Change Binding... from the context menu that appears.
- Select field2 in the Option Button Binding dialog box and click on the OK button to save the changes and close the dialog box.
- Double-click on the option button that used to be bound to field3 and set the Value when selected field to 2.
- 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).
- Double-click on the last option button in the row of option buttons, and check This button is selected by default.
- Open the data source pane and delete field3, field4, field5, field6, and field7.
- Rename group1 to ratings.
- Rename group2 to jobExperience.
- Rename field1 to description.
- Rename field2 to experienceRating.
- Add a new field (Element) with the name averageJobExperience and data type Decimal (double) to the ratings group.
- 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.
- Drag the averageJobExperience field to the Repeating Table and drop it in the footer of the repeating table.
- 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.
- Double-click on the description field and check the Read-only check box on the Display tab of the Text Box Properties dialog box.
- 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.
- Modify the table and controls to resemble the following figure:
Figure 2. The InfoPath form in design mode.
- The form should resemble the following figure when previewing it:
Figure 3. The InfoPath form in preview mode.
- 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)
Set a field's value: helperField = ""
Set a field's value: helperField = "calc"
- 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.
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:
- Create an absence report form using rules
- InfoPath avg function in repeating table
- Using InfoPath Math functions with repeating fields as input
- InfoPath sum function to calculate repeating table sum
- InfoPath eval and sum functions to get total length of strings in repeating table