Formulas

Overview

Formulas allow you to set the value of a control based on the content of other controls.

Formulas are currently supported by the following controls:

• Badge
• Button
• Caption Box
• Collapsible Panel
• Decimal
• Integer
• Link
• Note
• Notification
• Output Text
• Panel
• Progress Bar
• QR Code
• Slider
• Status Box
• Text
• Tool Tip

In addition to referring to other fields it is possible to use Aggregate functions referring to table columns in a formula.

Formula Control References

Controls can be referenced in formulas using the following syntax

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

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

                        @{Quantity} * @{Cost}

Within a table, if you are using formulas you will likely need to access fields that are in "my" row. The syntax for this is to use an "=" after the field name.

For example: Suppose we have the following controls: Quantity (integer), Cost (decimal), and Total (decimal) specified in a table.
We can specify the value of the Total control using the following formula. Note The total control can be unbound, i.e. it is not necessary to have a total data field in the table.

			@{Quantity=} * @{Cost=}		

Aggregate Functions

When processing a table you may wish to use Aggregate functions. The character "*" may follow any fieldName and indicates all the rows in a particular column.

NOTE 1: When processing a paginated table you need to use the optional expression operand. This is due to the implementation of the Table control, when a table is paginated, the only rows that are actually in existance at any one point in time are those that are, or have been visible.

NOTE 2: To get an accurate count of the number of records in a paginated table please use the table's getRecordCount() method.

Aggregate function -- expression operand (valid for all aggregate functions except COUNT)

FOR_EACH{expression}
where expression mirrors the formula specified for the column being processed. This expression uses the special '#{data-element-name} notation. The data-element-name refers to the actual bound data associated with a given column. Assume the following as an example:
We have the columns Amount1 with control Id 'tblAmount1', bound to the amount1 parameter, Amount2 with control Id 'tblAmount2', bound to the amount2 parameter, and Total with control Id 'tblTotal', bound to the formula @{tblAmount1=} * @{tblAmount2=}.
To create the Total of Table Amounts if the table is paginated we would use a formula like this:

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

Note this is logically equivalent to SUM(${Table1/tblTotal*}), when the table is NOT paginated.

The following aggregate functions are currently supported

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