Objects and Functions

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 Icon

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.

In addition to these operators, in conditional formatting you can use the following operators.

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
Comparison 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
 
ToString(Field1, [Optional Format String])

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'

Whenever an operator or function of a formula throws an exception during evaluation 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.