1 FORMULA Syntax
- 1.1 Operators and precedence
  - 1.1.1 Operators
  - 1.1.2 Precedence
- 1.2 Operands
  - 1.2.1 Literal operand types
  - 1.2.2 Functions
  - 1.2.3 Groups
  - 1.2.4 Boolean expressions
  - 1.2.5 ITEM References
  - 1.2.6 OBJECT CODE Operands
2 Nesting
3 Where FORMULAS can be placed?
4 ACTION FORMULAS
5 OBSOLETE SPECIFICATION: Versions prior to GLORIA 2 (2.7.2)
- 5.1 Syntax
  - 5.1.1 PREFIX
  - 5.1.2 FORM_FORMULA
  - 5.1.3 DEFAULT VALUE
- 5.2 WHERE FORMULAS CAN BE PLACED

FORMULA Syntax

FORMULAS are expressions composed by operands that are combined using mathematical operators

See the following examples

Formula	Comments

Formula

Comments

25 + 34

This formula is composed by two operands: the numbers "25" and "34", which are combined combined by the "+" (addition) operator

25 + MATH_SUM(12,22)

This formula is composed by two operands: The number "25" and a function MATH_SUM(), and they are combined by the "+" (addition) operator

25 + (12 + $3)

This is an example of a FORMULA where one of the operands is a nested FORMULA. In this case the operands are treated hierarchal as follows

First operand: 25
Second operand: (12 - $3). This is a complex operand (a GROUP) represented by a sub-formula that is composed of two operands combined by a "-" (subtraction) operator
- 12: a simple numeric value
- $3: a reference to an ITEM in the FORM where this FORMULA is being calculated

Operators and precedence

Operators

Operator		Works on operands of type

Operator		Works on operands of type
+	Arithmetic addition	Number
-	Arithmetic subtraction	Number
*	Arithmetic multiplication	Number
/	Arithmetic division	Number
>	Logical Greater than	Number or String (if any of the operands is a String, the comparison will be textual)
<	Logical Less than	Number or String (if any of the operands is a String, the comparison will be textual)
=	Logical Equal	Number or String (if any of the operands is a String, the comparison will be textual)
!=	Logical Not equal	Number or String (if any of the operands is a String, the comparison will be textual)
>=	Logical Greater than or equal	Number or String (if any of the operands is a String, the comparison will be textual)
<=	Logical Less than or equal	Number or String (if any of the operands is a String, the comparison will be textual)
&&	Logical AND	Number or string: A string with NULL or empty value is evaluated as 'false'. Otherwise it will be evaluated as 'true' A number with value 0 is evaluated as 'false'. Otherwise it will be evaluated as 'true'
\|\|	Logical OR	Number or string: A string with NULL or empty value is evaluated as 'false'. Otherwise it will be evaluated as 'true' A number with value 0 is evaluated as 'false'. Otherwise it will be evaluated as 'true'
&	String concatenation	Number or String

Precedence

When multiple operands are combined without grouping them into parenthesis, the operators are applied using the following precedence table.

Operators	Precedence (operators with higher values are calculated first)

Operators	Precedence (operators with higher values are calculated first)
`&`	6
`*, /`	5
`+, -`	4
`<, >, =, !=, >=, <=`	3
&&	2
\|\|	1

Example	Result	Comments

Example

Result

Comments

2 + 3*4

14

The multiplication is calculated before the addition

2 > 4 + 5

0

The addition is calculated before the logical comparison ( 2>9 ==> 0)

(3>2) && (2=0) || 5

1

Logical AND is calculated first: (3>2) && (2=0) ==> 0

Logical OR is calculated with the result of the previous operation (0) and 5: 0 || 5 ==> 1

Operands

An operand is whatever expression that evaluates to a value. They can be literal values, or a complex expression (e.g. a Function) that returns a value.

All operands have an associated Data Type that determines the type of value they hold. Available data types are (see OPERAND DATA TYPES for more details about their syntax)

String
Number
Boolean
Datetime
Time
JSON

Literal operand types

Consist of expressions that are literally represented and do not need to be evaluated to know their value (strings, numbers...)

Example	Comment

Example	Comment
`"John Doe"`	Literal string (see String data type)
`123.54`	Literal number (see Number data type)
`[5, 6]`	Array with 2 components (see Array data type)

Functions

Expressions that perform a calculation to return a literal value. A function is expressed by a function name and a group of parameters (which depend on the function)

The function name must be one of the available FORMULA FUNCTIONS. The parameters of the function can be also any FORMULA expression

Valid function expressions	Evaluates to	Comments

Valid function expressions	Evaluates to	Comments
`MATH_SUM(3, 4, 5)`	12
`TODAY()`	"2018-11-15 13:43:02"
`REPLACE(["John","Peter"],["Mary","Jane"],"John and Peter go shopping")`	"Mary and Jane go shopping"
`MATH_SUM(3, MATH_DIVIDE(27,3) )`	12	One of the parameters is a nested function
`MATH_SUM(4, 5+4)`	12	One of the parameters is a nested formula

Invalid function expressions	Evaluates to

Invalid function expressions	Evaluates to
`MATH_SUM (3, 4, 5)`	The name of the function is separated from the opening parenthesis by a space and should be adjacent

Groups

A group is any FORMULA enclosed into parenthesis.

Boolean expressions

A Boolean can have only two values (true/false) which are always represented as an integer:

Boolean value	Effective value

Boolean value	Effective value
true	1
false	0

This means that a boolean value can be used in arithmetic expressions. This is specially useful to implement an 'if-else' condition using only arithmetic operators. For example

Expression	Result

Expression

Result

($1>10)*5 + ($1<=10)*1

This formula is the same than the following 'if-else' control statement:

if($1 > 10) then
    return 5
else
    return 1
endif

ITEM References

An ITEM reference is an operand that represents the value of an ITEM located in the same FORM where the FORMULA is being evaluated. An ITEM reference is represented by a '$' signed followed by the numeric ITEM ID (E.g. $11).

Since API Version 2.7.18 it is also permitted to identify ITEMs by the new property ITEM_CODE (a customized alphanumeric ID for the ITEM) instead of the numeric ITEM ID. For example, if the ITEM with ID=1 has a ITEM_CODE='HEART_RATE', then the ITEM can be referenced either as $1 or $HEART_RATE

Note that it is not possible to get the value of an ITEM placed in a different FORM using an ITEM reference (if necessary you can use an OBJECT CODE Operand instead)

Expression	Result

Expression	Result
`REPLACE("XXXX", $2, $5)`	Replace the string XXXX in the string stored in ITEM $5 by the value of the string stored in ITEM $2

OBJECT CODE Operands

It is possible to use any OBJECT CODE expression in FORMULAs. Take into account that in many cases an OBJECT CODE can return an array of values, so ensure that you are using them in expressions where the expected value of the OBJECT CODE will be a valid operand.

Expression	Result

Expression	Result
`REPLACE("XXXX", $2, TASK{SCORING_REPORT}.TITLE)`	Replace the string XXXX in the string represented by the OBJECT CODE "`TASK{SCORING_REPORT}.TITLE`" by the value of the string stored in ITEM $2

Nesting

Nesting is permitted at any level

Example	Evaluates to

Example	Evaluates to
MATH_SUM`(3, 4 + 5)`	12
TODAY`()`	"2018-11-15 13:43:02"
REPLACE`( ["John","Peter"],["Mary","Jane"], CONCATENATE("John ",CONCATENATE("and Peter go shopping)))`	"Mary and Jane go shopping"

Where FORMULAS can be placed?

Formulas can be placed in the following ITEM TEMPLATE OBJECT properties:

FORM ITEM	DESCRIPTION

FORM ITEM

DESCRIPTION

FORMULA

The value placed in the form ITEM. The value of the ITEM will not be editable.

DEF_VAL

The default value of an ITEM. The formula must be preceded by an 'equal' sign, otherwise it will be considered a literal value

The value will be editable

If there is a formula in the FORMULA property the value of this field is ignored

ACTION FORMULAS

OBSOLETE SPECIFICATION: Versions prior to GLORIA 2 (2.7.2)

The following specification is obsolete from Gloria 2 (Version 2.7.2)

The upgrade from a previous version to Gloria 2 (Version 2.7.2) includes an automatic migration of existing FORMULAS to the new syntax

Syntax

"@FUNCTION" {(parameters)}

PREFIX

Formulas are calculated when:

the form is created (form_insert)
an answer is provided in a question, it is calculated again all the FORMULAS available in the next questions of the FORM

FORM_FORMULA

Any of the functions described in the FORM FORMULAS

In the current version, the parameter doesn't allow formula nesting except for the FORM formula when included in a MATH formula.

DEFAULT VALUE

If DEFAULT_VALUE field in form have a formula with '=' to top, this formula will be calculated in first open rather than a formula in FORMULA field. Then, the formula of FORMULA field can't have a '=' on top, thus this formula can't be calculated on first open, but it can't be calculated "on fly" (or on form close).

WHERE FORMULAS CAN BE PLACED

Formulas can be placed in the following template element properties:

FORM ITEM	DESCRIPTION

FORM ITEM	DESCRIPTION
FORMULA	The value placed in the form ITEM. The value of the ITEM will not be editable.
DEF_VAL	The default value of an ITEM. The formula must be preceded by an 'equal' sign, otherwise it will be considered a literal value The value will be editable If there is a formula in the FORMULA property the value of this field is ignored
MIN_LEN MAX_LEN MIN_VAL MAX_VAL	Element data properties that can be dynamically calculated
MANDATORY HIDDEN CONDITIONED	Element form properties that can be dynamically calculated. For this kind of properties, any value returned different than "0" will be considered "1" (true)

More information on the template elements can be found at FORM TEMPLATE OBJECT under the LIBRARY OBJECTS section.

INTRODUCTION TO FORMULAS