Problem
You need to sum up the values in a table column for all rows and display its total in another field on the form, but only for rows that match a specific criteria (eg: rows with Type set to "Fruit").
Solution
Setting up the sample:
1. Create a new form called "Sample Conditional Summation".
2. Create two fields:
Total amount (money field)
Invoice items (table)
3. In the "Invoice items" table, create the following 3 fields:
Item name (single line)
Type (single line)
Amount (money field)
The solution:
1. Click the "Edit form behavior" button.
2. Create a new form behavior.
3. Click the "Action wizard" link.
4. Click the "Create an action" button.
5. Click on the "Table Column Summation" action.
6. In the next page, select the "Invoice items" table, and select the "Amount" table column. Select the "Total amount" form field as the target to display the summed up value.
7. Click on OK to create the action and save changes to create your behavior.
8. Back in the behavior screen, click on "View code" to expand the behavior logic. Add the following lines of script (in bold) around the summation line of code:
if GetText(_FRow42012.Item("Type"))="Fruit" then
_FSum+=GetDbl(_FRow42012.Item("Amount"))
end if
9. Do a syntax check (to confirm that the syntax is correct) and then click on "Create behavior" to create your behavior.
10. Publish your form.
Testing your solution:
1. Run the application & launch the form.
2. Click on the "Add record" button in the "Invoice items" table. Type in a value for the item name and a value for the amount field, and for the "Type" field, type in "Fruit".
3. Repeat to create about 2-3 rows (for some of these rows, type in "Fruit", and for some of the other rows, type in "Machine" or something else. As soon as you add each row, you should see the amount auto-computed automatically (as shown below), but *only* for the rows where the type is equal to "Fruit".
An additional note is that you might also want to set the "Total amount" field to disabled, so that it will appear as read-only to the user.
Comments