SUM/CONTAINS Functions: Calculations in Tables

Article Number:040510

You can calculate fields in a table.
For example, you can perform the following calculations.

  • Place calculated fields in a table and perform calculations in each row
  • Use SUM function to calculate the total of numeric values in the table
  • Use CONTAINS function to find out whether any row in a table exactly matches the search string (specified value)

This page explains these three calculations.

Place Calculated Fields in a Table and Perform Calculations in Each Row

You can place calculated fields in a table and perform calculations in each row.
For example, by using the quantity and the unit price in the table, you can set the formula "Volume*Unit_Price" in the calculated field.

Sum Function: Calculating the Total of Numeric Values or Time Data in a Table

By using SUM function, you can calculate the total of values in the fields within a table.

Using SUM Function
SUM(Number1)

For "Number1", specify the field code of the Number field, a formula that results in a numerical value, or a number.

Formula Example

Formula (calculate subtotal): SUM (subtotal)

Calculate the total of numeric values entered in the "Subtotal" fields in the table.

Example: Calculating the Sum of Subtotals in a Table

This section describes how to display the sum of subtotal values in the table into the "Total amount" field, by using an example of an order management app.
For details on tables, see the "Adding Tables into Forms" page.

  1. From the parts list shown on the left side, drag and drop a Calculated field.

  2. Open the screen to set the Calculated field, and enter "Total Price" as the field name.
    Keep the screen open.

  3. Enter the SUM function that calculates the total amount of values in the table.
    In this example, enter "SUM(Subtotal)" to calculate the total amount of subtotals.
    Image

  4. To make the total amount easy to read, select "Number with thousands separator (1,000)". Image

  5. Change the field code to "Total_Price", and click Save. Image

  6. Click Save Form on the upper left side of the screen, and then click Update App on the upper right side of the screen. Image

Now, the Calculated field to display the total amount of values in the table is set. Calculated field is created

The sum of subtotal values in the table is displayed in the Total Price field. The sum of subtotal values is displayed

TIPS: If Empty Fields Are Included

The sum is calculated even if empty fields are included in the data subject to calculation. The calculation result is reflected in the total amount as well. The sum of subtotal values is displayed

Example: Calculate the Total of Time Data in the Table

In the Calculated field, you can calculate Date & time, Date, Time, and Hours in addition to numbers
For example, you can set "End-Start" formula, to automatically calculate the time taken to respond to an inquiry
Calculating the response time

You can also set a formula "SUM(Response_Time)" in the "Total Response Time" field, to sum up the time taken to respond to customers. Formula to calculate the total response time The total response time

CONTAINS Function: Finding Out Whether Any Row in a Table Matches Specified Conditions

Using CONTAINS function, you can find out from the formula whether any row in a table exactly matches the search string (specified value).

Using CONTAINS Function
CONTAINS(Field_code, Search string)

Returns TRUE if a value of the field (specified for "Field_code") in a table matches the specified value (specified for "Search string"). If not, the function returns FALSE.
  • As the first argument, specify any of the text, radio button, drop-down, and lookup (if the data type of the copy source is a string) in the table.
  • As the second argument, specify a search string.

Formula Example

This section shows you an example of a formula using CONTAINS function.

Formula: IF(CONTAINS(Priority, "Urgent"), "Respond Urgently", "Normal")

Determine if the table contains any row whose value is "Urgent" by using CONTAINS function.
If the table has at least one row containing "Urgent" in the "Priority" field, it will display "Respond Urgently". Otherwise, it will display "Normal".
The string for the second argument must be enclosed with double quotation marks ("").