Formulas

Edit online

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

Formulas are supported by the following views: Badge, Button, Caption box, Collapsible panel, Decimal, Integer, Link, Note, Notification, Display text, Panel, Progress bar, QR code, Slider, Status box, Plain text, and Tooltip.

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.