Formulas

Draft comment:
This topic is shared by BAW, CP4BA, CP4BASaaS. Last updated on 2025-03-13 12:15

Formulas allow you to set the value of a view based on the content of other views.

Syntax for references to views

Use the following syntax to refer to views in formulas:

${ControlName} refers to the view whose control id = ControlName.
@{ControlName} refers to the value of the view whose control id = ControlName.
${ControlName}.getValue() and @{ControlName} are equivalent.
For the Decimal and Integer views, you can use the standard arithmetic operators ('+", '-', '*', '/', '%') or the equivalent functions.
Because formulas are JavaScript expressions, anything valid in a JavaScript expression is valid in a formula.

For example, say we have the following views: Quantity (integer), Cost (decimal), and Total (decimal). We can specify the value of Total by using the following formula:

@{Quantity} * @{Cost}

If the Quantity (integer), Cost (decimal), and Total (decimal) views are used in a table with formulas, you most likely need to access fields that are in "my" row. To do that, use a syntax that places an equal sign ( =) after the field name, as shown in the following example:

@{Quantity=} * @{Cost=}

Note: The Total control can be unbound, meaning that it is not necessary to have a Total data field in the table.

Aggregate functions

When you process a table, you might need to use aggregate functions. The * character might follow any ControlName and indicates all the rows in a specified column.

The following aggregate functions are supported:

COUNT(${ControlName})
SUM(${ControlName}, expression)
AVG(${ControlName}, expression)
MIN(${ControlName}, expression)
MAX(${ControlName}, expression)

Note: To process a paginated table, you can use the optional expression operand. Due to the implementation of the Table view, when a table is paginated, it shows only the rows that are visible. To get an accurate count of the number of records in a paginated table, use the getRecordCount() method.

The expression operand is valid for all the aggregate functions except COUNT. For example:

FOR_EACH{expression}

where expression mirrors the formula specified for the processed column.

This expression uses the special '#{data-element-name} notation. The data-element-name refers to the actual bound data that is associated with a specified column. As an example, say your table has the following columns:

Amount1 with control Id 'tblAmount1' bound to the amount1 parameter
Amount2 with control Id 'tblAmount2' bound to the amount2 parameter
Total with control Id 'tblTotal' bound to the formula @{tblAmount1=} * @{tblAmount2=}

To create the total of table amounts in a paginated table, use a formula similar to this:

SUM(${Table1},FOR_EACH{#{amount1} * #{amount2}})

Note: This formula is logically equivalent to the SUM(${Table1/tblTotal*}) formula, which can be used for a non-paginated table.