Skip to main content

Using formulas in auto-populate rules

Note

This option pertains to the Hyperproof TPRM product line.

When building tables, you can configure auto-populate rules for each column as needed. One of the rule options is to add a formula. For more details on auto-populate rules, see Auto-populate rules for table columns.

If you choose to use a formula in one of your auto-populate rules, note that you can use formulas to compute numeric or string expressions. These can reference:

  • Columns from the same table

  • Columns from child tables

To operate on child table columns and insert the result into a parent column, you should use the Function tab in the formula editor.

Supported functions

The following functions are available in the Function tab.

Table 190. Formula editor functions

Function

Definition

AVERAGE

Computes the average of values in the child rows.

CONCAT

Concatenates values from child rows into a single string.

COUNT

Finds the total number of rows.

FILTER

Applies filtering criteria to child records before aggregating them.

MAXIMUM

Finds the maximum value from the child rows.

MINIMUM

Finds the minimum value from the child rows.

SUM

Calculates the sum of values in the child rows.



Example use case 1

Suppose you have a column A in the parent table, and a child table containing multiple rows with a numeric column. If you want to:

  • Find the sum, minimum, or maximum of those child values and auto-populate it in column A.

  • Or, concatenate child values into a string and insert it into column A.

You would use the appropriate function "SUM", "CONCAT" in the formula editor to achieve this.

Example use case 2

In this use case, we are using the Formula auto-populate rule to calculate the difference between two fields and store the result in a column named Difference. Assume you have a table with a column named Difference and its data type is Number.

  1. Access the table where you want to configure a column rule. See Accessing tables.

  2. Click the Configure button (Gear icon) next to the column.

    The Configure window displays.

  3. Click the Auto Populate tab.

  4. Click + Add Auto-Populate Rule and select Formula.

    The Auto-Populate Rules window displays.

    Note

    You can configure multiple auto-populate rules for a single column. However, the following guidelines apply:

    • Only one rule can exist without a condition — this acts as the default rule.

    • All other rules must have conditions to determine when they should be applied.

  5. Click the Insert Dynamic Value link to open the Formula editor.

    Note

    You can also type the formula into the Formula field without having to open the Formula editor.

  6. Use the tools in the Formula editor to create a difference calculation using existing column values from other columns, for example:

    • Select the column Monthly Sales and add that to the Formula Expression field.

    • Click the minus sign (-) in the Operators section to add it to your formula.

    • Select the column Target Sales and add that to the Formula Expression field.

  7. Click Save Rule to save it to the list of rules.

    The Difference column will now automatically calculate its value.

In this section: