|
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 |
The use of operators is not limited to number fields and decimal fields. Additionally, the following calculations can be performed.
Field type |
Description |
Sample usage |
Date field, Date and Time field |
Subtraction (-) can be used to subtract two date fields or two date and time fields from each other. •The field types can not be mixed. •The calculation returns the difference between the two fields in whole days (rounded down). •Negative results are accepted |
DateField1 = 2023.04.12 DateField2 = 2023.06.09
Calculation:
DateField2 - DateField1 = 58 |
Text field |
Addition (+) can be used between strings •Addition can be used both for text fields and literals •The toString function can be used to convert other field types into strings |
"PATIENT-"+PatientFirstName+"-"+PatientLastName= PATIENT-John-Doe
"INV-"+ToString(BuyerID)+"-"+ToString(OrderNumber) = INV-112-11773360 |
|
Note: Please note that the Time data given and used by any Date and Time fields is set to the System's Standard Time Zone. The Standard Time Zone is an Advanced Setting that can be configured by an administrator in the Therefore™ Solution Designer. Access the context menu of the Therefore™ Object, select Settings, and go into the Advanced tab. |
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) |
Category Fields
The following index data field types can be used in a condition.
Function |
Can be used in a condition |
Sample usage |
Label |
no |
- |
Image |
no |
- |
Text |
yes |
Strings need to be entered in quotes: "string". Numbers can be entered without quotes. If text fields in the category have the field ID 'company_name' and company_zipcode: company_name = "Moyaware" company_zipcode = 1160 |
Integer |
yes |
field_id = 1234 |
Decimal |
yes |
field_id = 14.36 |
Date |
yes |
field_id = d'YYYY-MM-DD' |
Datetime |
yes |
Timestamp1 < d'YYYY-MM-DD HH:MM:SS' Timestamp1 < d'YYYY-MM-DD HH:MM' |
Checkbox |
yes |
Checked: checkbox = 1 Unchecked: (NOT IsEmpty(checkbox)) AND checkbox = 0 Indeterminate: IsEmpty(checkbox) |
Numeric counter |
no |
- |
Formatted counter |
no |
- |
Single keyword |
yes |
Strings need to be entered in quotes: "string". Numbers can be entered without quotes. If the field ID is 'single_keyword': single_keyword = 2 or single_keyword_Text = "keyword" 'Text' needs to be manually added to the field ID. |
Multiple keyword |
no |
- |
Primary/Dependent |
yes |
The input format depends on the field type. Please refer to the above sample usages. |
Tab Control |
no |
- |
Tab |
no |
- |
Table |
no |
- |
Table Text |
yes* |
See Text |
Table Integer |
yes* |
See Integer |
Table Decimal |
yes* |
See Decimal |
Table Date |
yes* |
See Datetime |
Table Datetime |
yes* |
See Checkbox |
Table Checkbox |
yes* |
See Single keyword |
Table Single keyword |
yes* |
See Multiple keyword |
Table Primary/Dependent |
yes* |
See Primary/Dependent |
* Table values can only be used when a condition for a field within the same table is specified. Defining conditions for different tables is not possible.
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) |
AddDays(dateValue, days) |
Addition of days (as integer numbers) to date and date and Time fields. •The argument 'dateValue' refers to a date or date and time field. •The argument 'days' refers to a number field or an integer •The result is a date or date and time field depending on the first integer •Negative numbers are supported |
AddDays(DateField1, NumberField)= DateField2 |
IsEmpty() |
Returns true/false if the field is empty |
IsEmpty(Field) |
Round(x,y) |
Rounds a decimal number x to y places |
Round(3.4567,2) = 3.46 |
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 |
Converts index data field values to strings. •The first parameter is required •The following field types are accepted: Number, Decimal, Date, Date and Time, Checkbox (0, 1, empty), Keyword fields (server language) •The second parameter is optional and can be used to define a format for a decimal, date, or date and time field |
ToString(DecimalField1, "0.##") ToString(DateField1, "YYYY/MM/DD")
|
Macros
Macro |
Description |
Sample usage |
WFTaskId |
Set conditional formatting or make fields mandatory/disabled based on a workflow task. |
If the workflow task ID was set to 'task_ID': WFTaskId = "task_ID" The task ID is considered a string so it needs to be in quotes. |
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.
