Show/Hide Toolbars

Thereforeâ„¢ Help

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

 

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

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

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.