Sum a column of numbers in a repeating table
When you insert a repeating table on a form template, Microsoft Office InfoPath automatically adds a header row at the top of the table so that you can type labels for the table columns. You can optionally add a footer row at the bottom of a repeating table. Footer rows are commonly used to display a running total beneath a column of numbers. For example, you can add a Total text box to a footer row located underneath a column of expenses on an expense report form template. To add the values in the column, you can associate the sum function with the Total text box in the footer row.
In this article
Step 1: Add a footer row to a repeating table
Step 1: Add a footer row to a repeating table
-
Double-click the Repeating Table label below the table on your form template.
-
Click the Display tab.
-
Under Options, select the Include footer check box.
Tip: To add additional table footers at the end of a repeating table, right-click in an empty cell in the first footer row that you inserted, point to Insert on the shortcut menu, and then click Rows Above or Rows Below.
Step 2: Add a Total text box to the footer row
After you add a footer row to your repeating table, you can add a Total text box to the footer row.
Note: You add the Total text box to the footer row, not the data row, because form users can add multiple instances of the data row, and you don't want the Total box to appear multiple times on the user's form.
-
In the footer row, click the cell in the column where you want to add the Total box.
-
On the Insert menu, click Text Box.
Tip: You can use a rich text box or expression box instead of a text box. Expression boxes only display data and do not store it. Therefore, you should use an expression box only if you do not want to use the calculated sum in another calculation or save it as part of your form template.
-
In the footer row, click in the cell to the immediate left of the one where you inserted the text box. This is where you will add the text box label.
-
Type Total: and then select the text.
-
On the Standard toolbar, click Align Text Right
. -
Double-click the text box that you added in step 2.
-
Click the Data tab.
-
In the Field name box, type a label, such as totalExpenses.
-
In the Data type list, change the data type from Text (string) to Decimal (double). This allows you to display numbers with decimal places, such as $1234.12.
-
Click the Format button.
-
In the Decimal Format dialog box, under Format, click Currency, and then choose the currency that you want to use.
Step 3: Sum a column of numbers in the repeating table
If you use a repeating table to collect numerical data from your users, you can use the sum function to add the numbers that users enter in a table. It doesn't matter how many rows users add to the table when they fill out the form, or how many numbers they enter. As long as you use the sum function, the calculated value will adjust itself accordingly in the text box.
Formulas are stored in fields in the data source and are displayed in the controls that are bound to those fields. When designing a form template, you can create a formula that sums a column of numbers in a repeating table by using a text box, rich text box, or expression box.
-
In the footer row, double-click the text box that you inserted into the footer row.
-
Click the Data tab.
-
Under Default Value, click Insert Formula
. -
In the Insert Formula dialog box, click Insert Function.
-
In the Categories list, click Math.
-
In the Functions list, click sum, and then click OK to return to the Insert Formula dialog box.
At this point, you should see the following formula in the Insert Formula dialog box.
-
In the Formula box, double-click the text in parentheses.
The Select a Field or Group dialog box appears.
-
In the Data source list, click the field whose values you want to sum.
No comments:
Post a Comment