Thereforeâ„¢ Help

Calculation

 

These operators are supported in the calculation formula:

 

 

Additionally to these, in conditional formatting you can use:

 

 

Global Functions

 

A global function is a normal function, that is not written in syntax. Available functions include:

•IsEmpty()

 Returns true/false if field is empty.

 Sample usage: IsEmpty(Field)

•Abs()

 Returns the absolute value.

 Sample usage: Abs(Field)

 

Table Properties

 

Table properties are properties that can only be used on a table. Include member syntax to use them, and without parenthesis.

•RowCount

 Returns how many rows the table currently has.

 Sample usage: MyTable.RowCount (MyTable must be the table field itself)

 

Table Column Properties

 

Table column properties are properties that can only be used on a table column. Include member syntax to use them, and without parenthesis.

•Sum

 Returns the sum of all rows for this table column.

 Sample usage: TableIntCol.Sum (TableIntCol must be a table column field)

 

Operator Precedence (Order of operations)

 

The operator precedence is defined as such. Lowest is executed first, highest is executed last.

1.Power (^)

2.Multiply/Division (*, /)

3.Plus/Minus (+, -)

4.Relational Operators (<, <= , > , >=)

5.Comparison Operators (==, !=)

6.Logical And

7.Logical Or

For example, an "OR" is always executed at the end, and a multiplication would be calculated before an addition.

2 + 3 * 2 would result in 8.

 

If the precedence order is not what was intended, parentheses can be included to change it:

E.g. (2 + 3) * 2 would result in 10.

 

Whenever an operator or function of a formula throws an exception during evaluation (e.g. division by zero), then the result will be empty. The error will be logged in the event log and is not shown to the user.

 

Similarly, the condition is assumed to be FALSE whenever an operator or function of a formula throws an exception during evaluation. The error will be logged in the event log and is not shown to the user.

 

Field Sources

 

Depending on the field type, formulas can use different field sources. A normal index data field can use the following field sources:

•        Any supported normal index data field source (Number, Decimal, Dependent Fields of the Number or Decimal type)

•        Table properties (e.g. RowCount function)

•        Table column properties (e.g. Sum function)

 

Normal fields cannot have a table column field as a source (only indirectly through a RowCount/Sum function). A table column index data field can use the following field sources:

•        Any supported normal field source (Number, Decimal, Dependent Fields of the Number or Decimal type)

•        Another table column index data field of the same table (a table column of a different table cannot be referenced).

•        Table column properties (e.g. Sum function), except for the column currently in use. This means if table column "X" is in use, the formula "X.Sum" cannot be used.

 

"This" Keyword

 

In Conditional Formatting only, the "this" (without quotes) keyword can be used. The keyword will reference the field the user is currently in.

 

E.g. If a user is in the field "Calc", a conditional formatting would appear like this:

Calc > 10

 

Or the "this" keyword can be used to reference the field:

this > 10

 

The "this" keyword only works in conditional formatting because a calculation cannot reference itself. There are special cases where the category already has a field with a "this" FieldID. In this case, "this" will reference the other field, and not itself.