|
Objects and Functions |
Scroll |
Calculation
The following operators are supported in the calculation formula:
Operator |
Function |
+ |
Addition |
- |
Subtraction |
* |
Multiplication |
/ |
Division |
^ |
Exponentiation (raise to the power of) |
() |
Parenthesis |
-a |
Unary minus |
Global Functions |
See Global Functions below |
Table Properties |
See Table Properties below |
Table column properties |
See Table Column Properties below |
Additionally to these, in conditional formatting you can use:
Operator |
Function |
Relational Operators |
|
< |
Less than |
<= |
Less than or equal to |
> |
Greater than |
>= |
Greater than or equal to |
Comparison Operators |
|
= |
Equal to |
!= |
Not equal to |
Logical Operators |
|
AND |
And (not case sensitive) |
OR |
Or (not case sensitive) |
NOT |
Not (not case sensitive) |
Global Functions
A global function is a normal function that is not written in syntax. Available functions include:
Function |
Description |
Sample usage |
Abs() |
Returns the absolute value |
Abs(Field) |
IsEmpty() |
Returns true/false if the field is empty |
IsEmpty(Field) |
Search(x,y) |
Searches for a specific sub-string in a string, and returns true/false if it was found/not found. The following wildcards are available: * matches any characters ? matches exactly one character |
Search("Hello", "*o") = true |
Table Properties
Table properties are properties that can only be used on a table. Include member syntax to use them, and without parenthesis.
Function |
Description |
Sample usage |
RowCount |
Returns how many rows the table currently has |
'MyTable' must be the table field itself MyTable.RowCount > 0 |
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.
Function |
Description |
Sample usage |
Sum |
Returns the sum of all rows for this table column |
'TableIntCol' must be a table column field TableIntCol.Sum > 0 |
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.
