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 a "Calculated" field in a table and perform calculation for 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 a "Calculated" Field in a Table and Perform Calculation for Each Row

You can place a "Calculated" field in a table and perform calculation for each row.
For example, you can create a formula that references the "Volume" field and "Unit price" field in a table (Volume*Unit_price) and set the formula in a "Calculated" field in the same table. This allows you to calculate the amount for each row. Screenshot: Setting a formula Screenshot: The calculation result

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

For "number1", specify the field code of a "Number" field, a formula whose calculation result is a numeric value, or a numeric value.

Formula Example

Formula (calculate subtotal): SUM (subtotal)

This formula sums up the numeric values of the "Subtotal" field in the table.

Example: Calculating the Sum of Subtotals in a Table

This section describes how to make the "Total Price" field display the sum of subtotals in a table, using the example of an order management app.
For details on tables, refer to Adding a Table on the Form.

  1. Place a "Calculated" field on the app form by dragging the "Calculated" field displayed in the list of fields on the left side of the screen and then dropping it onto the right area of the screen. Screenshot: The app form

  2. Open the field setting screen of the "Calculated" field and set "Total Price" as the field name.
    Keep the setting screen open.

  3. Enter a formula that sums up the values in the table. Use the SUM function to create the formula.
    In this example, enter the following formula since you want to calculate the sum of subtotals: SUM(Subtotal)
    Screenshot: Setting a formula

  4. In this example, select Number with thousands separator (1,000) for better readability of the total price. Screenshot: Selecting the option to add thousands separators

  5. Change the field code to "Total_Price" and click Save. Screenshot: Changing the field code

  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. Screenshot: The "Save Form" button and the "Update App" button

  7. On the confirmation dialog, click Update App.

You have successfully configured a "Calculated" field that displays the sum of subtotals in a table. Screenshot: The configured "Calculated" field

The sum of subtotals in the table appears in the "Total Price" field. Screenshot: The sum of subtotals is displayed

Tips: If There Are Fields With Empty Values

For example, even if the unit price or quantity in the above example is not entered, the subtotal is calculated. The total price is also calculated. Screenshot: Calculation results are displayed even if fields subject to calculation contain empty values

Example: Calculate the Total of Time Data in a Table

In addition to numeric values, "Calculated" fields can perform calculation on date and time, date, time, and hours and minutes.
For example, you can set a formula such as "End-Start" in an inquiry management app to automatically calculate the service time for each inquiry.
Screenshot: Calculating the service time

You can also set a formula with the SUM function to calculate the total service time. Screenshot: The formula to calculate the total service time Screenshot: Total service 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 ("").