Formula Editor

EasySend's Formula Editor

The Formula editor enables EasySend's platform users to use built-in operators, functions, and model data items to create simple and complex conditions, logical expressions, and calculations.

Where to Find the Formula Editor 

(See Figure 1 and Figure 2)

After clicking a data item, the Formula editor can be found in two locations on the Model screen:

• Under the Values feature (1), when clicking the Value dropdown (2), the Formula (3) is the second option to select:

Figure 1: Formula Under Values

• Under the Condition feature (1), when clicking the dropdown (2) under Visibility or the Edit (Disable) conditions, the formula (3) is the second option to select:

Figure 2: Formula Under Condition

Formula Editor Structure

(See Figure 3)

When selecting the Formula option (under Values or Condition), the formula display window (1) and the Edit Formula button (2) appear.

Figure 3: Formula

The Formula Editor Window

Figure 4 and Table 1 describe the structure of the Formula Editor window.

Figure 4: Formula Editor Window

The Formula display window displays the formulas created using the Formula editor. When clicking it or the Edit Formula button, the Edit Formula window appears.

Table 1: Formula Editor Window

Variables

(See Figure 5)

Variables are the Model's Transaction data items (1) and Metadata items (2). They are used as input/output parameters when creating a formula. Each data item and Metadata item has a specific type (3) that defines their usage as a variable in the formula editor:    

• object
• string
• boolean

Figure 5: Transaction Data and Metadata

Object Type Variables

Object type variables nest other variables to create a logical data structure. Figure 6 displays a simple data structure in which customerDetails (1) is an object type variable that nests two other variables, firstName and lastName (2). In this type of data structure hierarchy, customerDetails is the parent variable and the variables inside of it are its children.

Figure 6: Data Structure

String Type Variable

When creating a formula, string type variables are used as a function's string type parameters or as a returned string type value. For formulas that contain functions such as MAX(), MIN(), or GREATER_THAN(), string type variables are automatically converted into a number variable.

Boolean Type Variable

When creating a formula, boolean type variables are used as a function's True/False parameters or as a returned True/False value.

How to add variables?

(See Figure 7)

Variables can be added to a formula in three different ways:

• Clicking a variable (1). 
• Hovering above a variable and then clicking Use (2).
• Manually writing the name of a variable inside the formula editor (3).

Figure 7: Adding Variables

When adding the variable by clicking it or with the Use button, it will be added in the following format: $variableName. For example:  $age (1) or $customerDetails.age (2) if the variable is nested inside an object (see Figure 8):

Figure 8: Added Variables

Manually adding Variables

(See Figure 9)

When manually adding a variable, the $ sign must be added manually as well. In addition, when starting to type the name of a variable, the list of available variables (1) updates with each typed letter to display the number of matching results (2). The variable displays an error indication (3) until it is written properly - $+full name (4).

Figure 9: Manually Adding Variables

Manually Adding Nested Variables

Nested variables can be added manually using two methods:

• Full path - by specifying the parent variable, for example, $customerDetails.age. 
• Relative path - by writing dots (..) to access the variable without specifying the parent variable, for example, $..age.

Array Type Variables

An Array can be a string type or a boolean type variable that contains zero or more items of the same type. An Array can also be an object type variable nesting other variables that are used as its items. To set any variable as an Array, the IsArray checkbox (1) must be checked (see Figure 10):  

Figure 10: IsArray Checkbox

Accessing Array Items - String/Boolean

The items of a string or boolean type Array are accessed by specifying their path and their index location. For example, Figure 11 displays a string type Array named carsPrice (1):

Figure 11: Array Type Variable

When creating a formula, all Array items can be accessed by writing the variable name followed by an asterisk that is written between two square brackets, for example, carsPrice[*] (1). A specific item will be accessed by indicating its index starting from 0, for example, carsPrice[0[ to access the first item and carsPrice[1] to access the second item (2) (see Figure 12): 

Figure 12: Accessing Array Type Variable Items

Accessing Object Type Array Items

Figure 13 displays an object type Array named children (1) that nests three string type variables (2) used as its items. The childrenAgeAvg variable (3) will be used to create a formula that calculates the average age value from the children Array:

Figure 13: Variables

When creating a formula, all age items within the Array will be accessed by writing $children[*].age (1). A specific item will be accessed by indicating its index starting from 0, for example, children[0].age to access the first item and children[1].age to access the second item (2) (see Figure 14):  

Figure 14: Accessing Object Type Array Items

Nested variables inside an object type Array can be added manually using two methods  

• Full path - by specifying the parent variable, for example, $children[].firstName. 
• Relative path - by writing dots (..) to access the variable without specifying the parent variable, for example, $..firstName.

Functions

The Formula Editor window contains 86 unique functions:

• ALL() - this function checks if all the provided reference items meet a specific criterion according to a comparison operator.
• AND() - this function checks if all provided parameters are logically true.
• ANY() - this function checks if any of the provided reference items meet a specific criterion according to a comparison operator.
• AVERAGE() - this function returns the numerical average of values in a dataset.
• ARRAY_LENGTH() - this function returns the length of an Array. 
• CONCAT() - this function appends string values.
• COUNT() - this function counts the number of values in a dataset.
• COUNT_IF() - this function counts the number of values in a dataset that match a set condition.
• COUNT_UNIQUE - this function counts the number of unique values in a list of specified values and ranges.
• DATE() - this function converts numbers into a date.
• DATE_ADD() - this function adds or subtracts a specified time unit to a date.
• DATE_ADD_ EXACT() - this function adds or subtracts months from date.  
• DATE_DIFF() - this function calculates the number of days, months, or years between two dates.
• DATE_FORMAT() - this function converts a provided date to a string, according to conventional datetime formatting.
• DAY() - this function returns the day of a specific date according to a format.
• DIVIDE() - this function returns the quotient of two value
• EQUAL() - this function checks if two values are equal.
• FILTER() - this function filters items according to criteria. 
• FORMAT_TIN() - this function converts a 9-digit number to a Taxpayer Identification Number (TIN) format.
• FORMAT_PRECISION() - this function formats a number to a given precision. 
• FORMAT_SSN() - this function converts a 9-digit number to a Social Security Number (SSN) format.
• FORMAT_THOUSANDS() - this function formats a number by adding commas.
• FORMAT_CUSTOM() - this function converts and formats text according to a specified pattern.
• GREATER_THAN() - this function checks if the first value is greater than the second value.
• GREATER_THAN_EQUAL() - this function checks if the first value is greater than or equal to the second value.
• IF() - this function checks if a logical expression is true or false.
• IF_ARRAY() - this function checks if a logical expression is true or false.
• INDEX() - this function checks what is the position of the first item that matches the search criteria. 
• IS_BETWEEN() - this function checks if a number is included between two numbers.
• IS_BOOLEAN() - this function checks if the type of the input parameter is boolean.
• IS_EMPTY() - this function checks if a variable contains an empty value or not.
• IS_EMPTY_ARRAY() - this function checks if an Array is empty.
• IS_EVEN() - this function checks if the type of the value of the input parameter is even.
• IS_NUMBER() - this function checks if the type of the input parameter is a number.
• IS_ODD() - this function checks if the type of the value of the input parameter is odd.
• IS_TEXT() - this function checks if the type of the input parameter is a text.
• JOIN() - this function appends string values between a separator.
• LENGTH() - this function indicates the length of an input text.
• LESS_THAN() - this function checks if the first value is lower than the second value.
• LESS_THAN_EQUAL() - this function checks if the first input value is lower than or equal to the second input value.
• LN() - this function returns the natural logarithm of a value.
• LOOKUP() - this function looks for a value in a search array and returns the value of the corresponding item (in the same position/index) of a result array.
• LOOKUP_ARRAY() - this function looks for a value in a search array and returns multiple values of the corresponding item (in the same position/index) of a result array.
• LOWER() - this function converts the characters of the input text from uppercase to lowercase.
• LOWER_ARRAY() - this function converts characters of an input Array from uppercase to lowercase.
• MAX() - this function returns the maximum value from a list of numbers, ignoring empty values.
• MIN() - this function returns the minimum value from a list of numbers, ignoring empty values.
• MONTH() - this function returns the month of a specific date according to a format.
• MULTIPLY() - this function returns the multiplication product of two numbers.
• NOT() - this function returns the opposite value of a boolean input.  
• NOT_EQUAL() - this function checks if two values are not equal.
• OR() - this function returns False if any of the provided arguments are logically True, and False if all the provided arguments are logically False.  
• PAD() - this function adds padding to a string up to the output length.
• PAD_ARRAY() - this function adds padding to all string type Array items up to their output length.
• POSITION() - this function returns the position at which a string/array is first found within text.
• POWER() - this function returns an exponential number (raised to a power).
• PROPER() - this function capitalizes the first character of a word in a specified string and converts the following characters to lowercase.
• RAND_BETWEEN() - this function returns a uniformly random integer between two values, inclusive of those values.
• REGEX_EXTRACT() - this function extracts matching substrings according to a regular expression.
• REGEX_MATCH() - this function checks whether a portion of text matches a regular expression.
• REGEX_REPLACE() - this function replaces matching substrings according to a regular expression.
• REMOVE_EMPTY() - this function removes empty strings and null values from an Array. 
• REPLACE() - this function replaces a portion of a text with a different text. 
• ROUND() - this function rounds a number to a certain number of decimal places according to standard rules.
• SUBSTITUTE() - this function replaces existing text with new text in a string. 
• SUBSTITUTE_ARRAY() - this function replaces existing text with new text in a string for all items in an array.
• SPLIT() - this function splits one string into an array of strings.
• SQRT() - this function returns the square root of a positive number.
• SUBSTRING() - this function returns a segment of a string. Beginning with the start index and up to a specific length.
• SUBTRACT() - this function returns the difference (subtraction result) of two numbers.
• SUM() - this function returns the sum of multiple numbers. Equivalent to the `+` operator. 
• SUM_IF() - this function returns a conditional sum across a range.
• SWITCH() - this function checks a value against cases and returns a matching single value result. 
• SWITCH_ARRAY() - this function checks a value against cases and returns a matching array result.
• TO_ARRAY() - this function converts a provided set of values into an Array.
• TO_NUM() - this function converts a string to a number.
• TO_TEXT() - this function converts a value to a string.
• TRIM() - this function removes leading and tailing padding elements in a string.
• TYPE() - this function returns the type of value/variable that was entered into the function. 
• UNIQUE() - this function returns the unique items of an Array. 
• UPPER() - this function converts the characters of the input text from lowercase to uppercase.
• UPPER_ARRAY() - this function converts characters of an input Array from lowercase to uppercase.
• VALUE() - this function returns a value from an Array according to the provided position.
• WEEK_DAY() - this function returns a number representing the day of the week of the date provided (Sunday=1).
• WEEK_NUM() - this function returns a number representing the week of the year of the date provided.
• YEAR() - this function returns the year of a specific date according to a format.

ALL()

The following sections describe the ALL(operator, criteria, reference_items) function.

General Description

This function checks if all the provided reference items meet a specific criterion according to a comparison operator. 

Input and Output 

Input:

The function receives the following input parameters:

• operator (string):
  • "="
  • "!="
  • ">"
  • "<"
  • ">="
  • "<="
• criteria and reference_items (string, number, boolean):
  • Arrays or Array items - for example: $children[*], $children[*].age, $children[0].age.
  • Variables - for example: $myAge or $spouseAge.
  • Fixed values - for example: "word", 80, True.
  • A returned value from a different function - for example: SUM() or IS_BOOLEAN().

Output:

The function returns a boolean value (True or False).

Examples:

Table 2: Inputs/Outputs

AND()

The following sections describe the AND(logical_formulas) function.

General Description

This function checks if all provided parameters are logically true.

Input and Output 

Input:

The function receives multiple boolean type values or a boolean type Array as input parameters:

• Array or Array items - for example: $haveChildren[*], $children[*].overEighteen, $children[0].overEighteen.
• Variables - for example: $isMarried or $carOwner.
• Fixed values.
• A returned boolean value from a different function - for example: ALL().

Output:

The function returns a boolean value (True or False).

Examples:

Table 3: Inputs/Outputs

ANY()

The following sections describe the ANY(operator, criteria, reference_items) function.

General Description

This function checks if any of the provided reference items meet a specific criterion according to a comparison operator. 

Input and Output 

Input:

The function receives the following input parameters:

• operator (string):
  • "="
  • "!="
  • ">"
  • "<"
  • ">="
  • "<="
• criteria and reference_items (string, number, boolean):
  • Arrays or Array's items - for example: $children[*], $children[*].age, $children[0].age.
  • Variables - for example: $myAge or $spouseAge.
  • Fixed values - for example: "word", 80, True.
  • A returned value from a different function - for example: SUM() or IS_BOOLEAN().

Output:

The function returns a boolean value (True or False).

Examples:

Table 4: Inputs/Outputs

AVERAGE()

The following sections describe the AVERAGE(items) function.

General Description

This function returns the numerical average of values in a dataset.

Input and Output 

Input:

The function receives a number type Array or multiple number variables/values as input parameters (items):

• Arrays or Array items - for example: $salary[*], $children[*].age, $children[0].age, $salary[1].
• Variables - for example: $Age or $spouseAge.
• Fixed values.
• A returned value from a different function - for example: SUM() or MIM().

Output:

The function returns a number.

Examples:

Table 5: Inputs/Outputs

ARRAY_LENGTH()

The following sections describe the ARRAY_LENGTH(array) function.

General Description

This function returns the length of an Array. 

Input and Output 

Input:

The function receives any type of array (boolean, object, string) as an input parameter, for example:

• customer[*].firstName
• $children[*].
• TO_ARRAY("1","2","3","4","5").
• TO_ARRAY($num1,$num2,$num3,$num4,$num5).

Output:

The function returns a number, the length of the Array.

Examples:

Table 6: Inputs/Outputs

CONCAT()

The following sections describe the CONCAT(strings_to_concat) function.

General Description

This function appends string values.

Input and Output 

Input:

The function receives multiple strings as input parameters: 

• A string item of an object type Array - for example: $children[*].firstName, $children[0].firstName. 
• A string type Array - for example: $colors[*].
• A string item of a string type Array - for example: $color[0] or $color[1].
• Fixed values.
• Variables - for example: $word1, $word2, $word3.
• A function that returns a string value, for example: TO_TEXT().

Output:

The function returns a single appended string.

Examples:

Table 7: Inputs/Outputs

COUNT()

The following sections describe the COUNT(items) function.

General Description

This function counts the number of values in a dataset.

Input and Output 

Input:

The function receives an Array or multiple variables/values of any type (string, number, boolean, object, date) as input parameters (items):

• An object type Array - for example: $children[*].
• A string item of an object type Array - for example: $children[*].firstName, $children[0].firstName. 
• A string type Array - for example: $colors[*].
• A string item of a string type Array - for example: $color[0], $color[1].
• Fixed values - for example: "Word",1, True, 12/03/1985.
• Variables - for example: $var1, $var2, $var3.
• A function that returns any type of value - for example: TO_DATE(), IS_BOOLEAN(), SUM().

Output:

The function returns a number.

Examples:

Table 8: Inputs/Outputs

COUNT_IF()

The following sections describe the COUNT_IF(operator, condition_value, items) function.

General Description

This function counts the number of values in a dataset that match a set condition.

Input and Output 

Input:

The function receives the following input parameters:

• operator (string):
  • "="
  • "!="
  • ">"
  • "<"
  • ">="
  • "<="
• condition_value and items (string, number, boolean):
  • Array or Array items - for example: $children[*], $children[*].age, $children[0].age.
  • Variables - for example: $myAge or $spouseAge.
  • Fixed values - for example: "word", 80, True.
  • A returned value from a different function - for example: SUM() or IS_BOOLEAN().

Output:

The function returns a number.

Examples:

Table 9: Inputs/Outputs

COUNT_UNIQUE()

The following sections describe the COUNT_UNIQUE(items) function.

General Description

This function counts the number of unique values in a list of specified values and ranges.

Input and Output 

Input:

The function receives an Array or multiple variables/values of any type (string, number, boolean, object, date) as input parameters (items):

• An object type Array - for example: $children[*].
• Array items - for example: $children[*].firstName, $children[0].firstName. 
• A string type Array - for example: $colors[*].
• A boolean type Array - for example: $isMarried[*].
• String and boolean Array items - for example: $color[0], $isMarried[1].
• Fixed values - for example: "Word",1, True, 12/03/1985.
• Variables - for example: $var1, $var2, $var3.
• A function that returns any type of value - for example: IS_BOOLEAN(), SUM().

Output:

The function returns a number.

Examples:

Table 10: Inputs/Outputs

DATE()

The following sections describe the DATE(year, month, day) function.

General Description

This function converts numbers into a date format.

Input and Output 

Input:

The function receives three input type number parameters:  

• year- for example 1985.
• month - for example 05.
• day - for example 01.

Output:

The function returns a date.

Examples:

Table 11: Inputs/Outputs

DATE_ADD()

The following sections describe the DATE_ADD(date, add, date_unit) function.

General Description

This function adds or subtracts a specified time unit to a date.

Input and Output 

Input:

The function receives three input parameters:  

• date - a date value returned from a different function, for example: DATE(1985,05,01).
• add:
  • A fixed number -  for example: 8 or -5.
  • A string type variable - for example: $number or $dates[0].items. 
• date_unit:
  • A fixed string - for example: "Y", "M", "D".
  • A string type variable, for example: $dateUnit or $date[0].dateUnit

Output:

The function returns a date.

Examples:

Table 12: Inputs/Outputs

DATE_ADD_EXACT_MONTHS()

The following sections describe the DATE_ADD_EXACT_MONTHS(date, add) function.

General Description

This function adds or subtracts a specified time unit to a date.

Input and Output 

Input:

The function receives three input parameters:  

• date - a date value returned from a different function, for example: DATE(1985,05,01).
• add:
  • A fixed number -  for example: 8 or -5.
  • A string type variable - for example: $number or $dates[0].items. 

Output:

The function returns a date.

Examples:

Table 13: Inputs/Outputs

DATE_DIFF()

The following sections describe the DATE_DIFF(start_date, end_date, date_unit) function.

General Description

This function calculates the number of days, months, or years between two dates.

Input and Output 

Input:

The function receives three input parameters:  

• start_date/end_date - a date value returned from a different function, for example: DATE(1985,05,01).
• date_unit:
  • A fixed string - for example: "Y", "M", "D".
  • A string type variable, for example: $dateUnit, $date[0].dateUnit

Output:

The function returns a number.

Examples:

Table 14: Inputs/Outputs

DATE_FORMAT()

The following sections describe the DATE_FORMAT(date, format) function.

General Description

This function converts the provided date to a string, according to conventional datetime formatting.

Input and Output 

Input:

The function receives two input parameters:  

• date - a date value returned from a different function, for example: DATE(1985,05,01).
• format - comprised of a month, day, and year. For example: 
  • "d/M/y" or "d-M-y" - (1/5/85 or 1-5-85).
  • "dd/MM/yy" or "dd-MM-yy"  (01/05/85 or 01-05-85).
  • "ddd/MMM/yyyy" or "ddd-MMM-yyyy" (Mon/Feb/1985 or Mon-Feb-1985). 
  • "dddd/MMMM/yyyy" or "dddd-MMMM-yyyy" (Monday/February/1985 or Monday/February/1985).

Output:

The function returns a string.

Examples:

Table 15: Inputs/Outputs

DAY()

The following sections describe the DAY(date) function.

General Description

This function returns the day of a specific date according to a format.

Input and Output 

Input:

The function receives a date parameter returned from a different function, for example:

• DAY(TO_DATE($customer[0].dateBirth, "yyyy-dd-MM")).
• DAY(DATE(1985/05/01)). 

Output:

The function returns a number.

Examples:

Table 16: Inputs/Outputs

DIVIDE()

The following sections describe the DIVIDE(value1, value2) function.

General Description

This function returns the quotient of two values

Input and Output 

Input:

The function receives two number type input parameters:

• Array items - for example: $children[0].age or $salary[1].
• Variables - for example: $Age or $spouseAge.
• Fixed values.
• A returned value from a different function - for example: SUM() or MIM().

Output:

The function returns a number.

Examples:

Table 17: Inputs/Outputs

EQUAL()

The following sections describe the EQUAL(value1, value2) function.

General Description

This function checks if two values are equal.

Input and Output 

Input:

The function receives two input parameters from any type (number, string, boolean, date):  

• Array items - for example: $children[1] or $children[0].age
• Variables - for example: $number1, $isMarried. 
• Fixed values - for example: "True", True, 8. 
• Returned values from different functions - DATE(), SUM(), ANY().

Output:

The function returns a boolean value (True or False).

Examples:

Table 18: Inputs/Outputs

FILTER()

The following sections describe the FILTER(operator, search_key, search_items) function.

General Description

This function filters items according to criteria. 

Input and Output 

Input:

The function receives the following input parameters:

• operator (string):
  • "="
  • "!="
  • ">"
  • "<"
  • ">="
  • "<="
• search_key and search_items (string, number, boolean):
  • Arrays or Array items - for example: $children[*], $children[*].age, $children[0].age.
  • Variables - for example: $myAge or $spouseAge.
  • Fixed values - for example: "word", 80, True.
  • A returned value from a different function - for example: SUM() or IS_BOOLEAN().

Output:

The function returns an array of values.

Table 19: Inputs/Outputs

GREATER_THAN()

The following sections describe the GREATER_THAN(value1, value2) function.

General Description

This function checks if the first value is greater than the second value.

Input and Output 

Input:

The function receives two input parameters (number or date), for example:  

• Variables - $number1 or $number2. 
• Fixed number values.
• Returned values from different functions - DATE(), SUM().

Output:

The function returns a boolean value (True or False).

Examples:

Table 20: Inputs/Outputs

GREATER_THAN_EQUAL()

The following sections describe the GREATER_THAN_EQUAL(value1, value2) function.

General Description

This function checks if the first value is greater than or equal to the second value.

Input and Output 

Input:

The function receives two input parameters (number or date), for example:  

• Variables - $number1, or $Number2. 
• Fixed number values.
• Returned values from different functions - DATE(), SUM().

Output:

The function returns a boolean value (True or False).

Examples:

Table 21: Inputs/Outputs

IF()

The following sections describe the IF(logical_expression, value_if_true, value_if_false) function.

General Description

This function checks if a logical expression is true or false.

Input and Output 

Input:

The function receives three input parameters:  

• logical_expression that is comprised of:
  • Comparison operators:
    •  =
    • !=
    • >
    • <
    • >=
    • <=
  • string, boolean, number, or date values:
    • Array items - for example:  $children[0].firstName, $isMarried[1].
    • Variables - for example: $number, $word, $booleanValue.
    • Fixed values - for example: 8, "word", True.
    • Other functions - for examples: DATE(), SUM(), ALL().
• value_if_true and value_if_false:
  • string, boolean, number, or date values:
    • Array items - for example:  $children[0].firstName, $isMarried[1].
    • Variables - for example: $number, $word, $booleanValue.
    • Fixed values - for example: 8, "word", True
    • Other functions - for examples: DATE(), SUM(), ALL().

Output:

The function returns the value_if_true parameter or the value_if_false parameter according to the result of the logical expression. 

Examples:

Table 22: Inputs/Outputs

IF_ARRAY()

The following sections describe the IF_ARRAY(logical_expression, value_if_true, value_if_false) function.

General Description

This function checks if a logical expression is true or false.

Input and Output 

Input:

The function receives three input parameters:  

• logical_expression  that is comprised of:
  • Comparison operators:
    •  =
    • !=
    • >
    • <
    • >=
    • <=
  • string, boolean, number, or date values:
    • Array items- for example:  $children[0].firstName or $isMarried[1].
    • Variables - for example: $number, $word, $booleanValue.
    • Fixed values - for example: 8, "word", True.
    • Other functions - for examples: DATE(), SUM(), ALL().
• value_if_true and value_if_false:
  • string or number Arrays, for example:
    • $children[*].firstName
    • $ages[*].
    • TO_ARRAY().

Output:

The function returns the value_if_true parameter (Array) or the value_if_false parameter (Array) according to the result of the logical expression.

Examples:

Table 23: Inputs/Outputs

INDEX()

The following sections describe the INDEX(reference_array, search_criteria) function.

General Description

This function checks the position of the first item that matches the search criteria. 

Input and Output 

Input:

The function receives two input parameters:  

• reference_array (string, number, boolean):
  • Array or Array items - for example: $kids[*].ages, $isMarried[*], $kids[0].ages, $isMarried[0].
  • Variables - for example: $number, $word, $booleanValue.  
  • Fixed values - for example: 8, "word", True.
  • A returned Array from the TO_ARRAY() function.
• search_criteria (string, number, boolean): 
  • Array items - for example: $kids[0].ages or $isMarried[0].
  • Variables - for example: $number, $word, $booleanValue.  
  • Fixed values - for example: 8, "word", True.
  • A returned value from a different function, for example: SUM(), MIN(), AND().

Output:

The function returns a number - the first index/position (starting from 0) of the item that matches the search criteria.

Examples:

Table 24: Inputs/Outputs

IS_BETWEEN()

The following sections describe the IS_BETWEEN(value_to_compare, lower_number, upper_number, lower_value_is_inclusive, upper_value_is_inclusive) function.

General Description

This function checks if a number is included between two numbers.

Input and Output 

Input:

The function receives five input parameters:  

• value_to_compare, lower_number, upper_number (number):
  • Array items - for example: $kids[0].age or $ages[1].
  • Variables - for example: $num1 or $num2.  
  • Fixed values.
  • A returned number from another function, for example: SUM(), MIN(), MAX().
• lower_value_is_inclusive, upper_value_is_inclusive (boolean): 
  • Array items - for example: $status[0].isMarried or $isMarried[0].
  • Variables - for example: $booleanValue.  
  • Fixed values.
  • A returned value from a different function, for example: OR(), ANY(), AND().

Output:

The function returns the boolean value True or the lower_value_is_inclusive parameter if the value_to_compare parameter is between the lower_number and the upper_number parameters. If not, the function will return False or the upper_value_is_inclusive parameter.

Examples:

Table 25: Inputs/Outputs

IS_BOOLEAN()

The following sections describe the IS_BOOLEAN(value) function.

General Description

This function checks if the type of the input parameter is boolean.

Input and Output 

Input:

The function receives one input parameter of any type (string, number, boolean, date):

• Array items - for example: $status[0].isMarried or $isMarried[0].
• Variables - for example: $variable1 or $variable2. 
• Fixed values - for example: "True", True, 8.
• A returned value from a different function, for example: SUM(), DATE(), AND().

Output:

The function returns a boolean value (True or False).

Examples:

Table 26: Inputs/Outputs

IS_EMPTY()

The following sections describe the IS_EMPTY(value) function.

General Description

This function checks if a variable contains an empty value or not.

Input and Output 

Input:

The function receives one input parameter of any type (string, number, boolean, date):

• Array items - for example: $status[0].isMarried, $isMarried[0].
• Variables - for example: $word, $number, $isBoolean.
• Fixed values - for example: "True", True, 8.
• A returned value from a different function, for example: SUM(), DATE(), AND().

Output:

The function returns a boolean value (True or False).

Examples:

Table 27: Inputs/Outputs

IS_EMPTY_ARRAY()

The following sections describe the IS_EMPTY_ARRAY(array) function.

General Description

This function checks if an Array is empty.

Input and Output 

Input:

The function receives one input parameter, an Array of any kind (string, boolean, number, date), for example:  

• $children[*], $children[*].firstName, $isMarried[*].
• An Array returned from the TO_ARRAY() function.

Output:

The function returns a boolean value (True or False).

Examples:

Table 28: Inputs/Outputs

IS_EVEN()

The following sections describe the IS_EVEM(value) function.

General Description

This function checks if the type of the value of the input parameter is even.

Input and Output 

Input:

The function receives one number type input parameter:

• Array items - for example: $status[0].isMarried or $isMarried[0].
• Variables - for example: $variable1 or $variable2. 
• Fixed values.
• A returned value from a different function, for example: SUM(), MIN(), MAX().

Output:

The function returns a boolean value (True or False).

Examples:

Table 29: Inputs/Outputs

IS_NUMBER()

The following sections describe the IS_NUMBER(value) function.

General Description

This function checks if the type of the input parameter is a number.

Input and Output 

Input:

The function receives one input parameter of any type (string, number, boolean, date):

• Array items - for example: $status[0].isMarried or $isMarried[0].
• Variables - for example: $variable1 or $variable2. 
• Fixed values - for example: "True", True, 8.
• A returned value from a different function, for example: SUM(), DATE(), AND().

Output:

The function returns a boolean value (True or False).

Examples:

Table 30: Inputs/Outputs

IS_ODD()

The following sections describe the IS_ODD(value) function.

General Description

This function checks if the type of the value of the input parameter is odd.

Input and Output 

Input:

The function receives one number type input parameter:

• Array item - for example: $status[0].isMarried or $isMarried[0].
• Variables - for example: $variable1 or $variable2. 
• Fixed values.
• A returned value from a different function, for example: SUM(), MIN(), MAX().

Output:

The function returns a boolean value (True or False).

Examples:

Table 31: Inputs/Outputs

IS_TEXT()

The following sections describe the IS_TEXT(value) function.

General Description

This function checks if the type of the input parameter is a text.

Input and Output 

Input:

The function receives one input parameter of any type (string, number, boolean, date):

• Array items - for example: $status[0].isMarried or $isMarried[0].
• Variables - for example: $variable1 or $variable2. 
• Fixed values - for example: "True", True, 8.
• A returned value from a different function, for example: SUM(), DATE(), AND().

Output:

The function returns a boolean value (True or False).

Examples:

Table 32: Inputs/Outputs

JOIN()

The following sections describe the JOIN(strings_to_join, separator) function.

General Description

This function appends string values between a separator.

Input and Output 

Input:

The function receives two string type input parameters: 

• Array or Array items - for example: $kids[*].ages, $isMarried[*], $kids[0].ages, $isMarried[0].
• Variables - $word1, $word2, $word3.
• Fixed values.
• A function that returns a string value, for example, DATE_FORMAT() or TO_TEXT().

Output:

The function returns a single appended string.

Examples:

Table 33: Inputs/Outputs

LENGTH()

The following sections describe the LENGTH(text) function.

General Description

This function indicates the length of an input text.

Input and Output 

Input:

The function receives one string type input parameter, for example:  

• Array items - $kids[0].firstName or $color[1].
• Variables - $text1 or $text2. 
• Fixed values.
• Returned values from different functions - JOIN() or TO_TEXT().

Output:

The function returns a number.

Examples:

Table 34: Inputs/Outputs

LESS_THAN()

The following sections describe the LESS_THAN(value1, value2) function.

General Description

This function checks if the first value is lower than the second value.

Input and Output 

Input:

The function receives two input parameters (number or date), for example:  

• Variables - $number1 or $number2. 
• Fixed number values.
• Returned values from different functions - DATE(), SUM().

Output:

The function returns a boolean value (True or False).

Examples:

Table 35: Inputs/Outputs

LESS_THAN_EQUAL()

The following sections describe the LESS_THAN_EQUAL(value1, value2) function.

General Description

This function checks if the first input value is lower than or equal to the second input value.

Input and Output 

Input:

The function receives two input parameters (number or date), for example:  

• Variables - $number1 or $number2. 
• Fixed number values.
• Returned values from different functions - DATE() or SUM().

Output:

The function returns a boolean value (True or False).

Examples:

Table 36: Inputs/Outputs

LN()

The following sections describe the LN(value) function.

General Description

This function returns the natural logarithm of the value.

Input and Output 

Input:

The function receives on number type input parameter, for example:  

• Variables - $number1 or $number2. 
• Fixed number value.
• Returned values from different functions - DATE() or SUM().

Output:

The function returns a number.

Examples:

Table 37: Inputs/Outputs

LOOKUP()

The following sections describe the LOOKUP(operator, search_key, search_array, result_array) function.

General Description

This function looks for a value in a search array and returns the value of the corresponding item (in the same position/index) of a result array.

Input and Output 

Input:

The function receives four input parameters:  

• logical_expression  that is comprised of:
  • Comparison operators:
    •  =
    • !=
    • >
    • <
    • >=
    • <=
• search_key (string, number, boolean):
  • Array items - for example:  $children[0].firstName, $isMarried[1].
  • Variables - for example: $number, $word, $booleanValue.
  • Fixed values - for example: 8, "word", True.
  • Other functions, for examples: DATE_FORMAT(), SUM(), ALL().
• search_array and result_array - a string, number, or boolean Array, for example: 
  • $children[*].firstName.
  • $isMarried[*].
  • TO_ARRAY().

Output:

The function returns an item (string, number, boolean) from the result_array.

Examples:

Table 38: Inputs/Outputs

LOOKUP_ARRAY()

The following sections describe the LOOKUP(operator, search_key, search_array, result_array) function.

General Description

This function looks for a value in a search array and returns multiple values of the corresponding item (in the same position/index) of a result array.

Input and Output 

Input:

The function receives four input parameters:  

• logical_expression that is comprised of:
  • Comparison operators:
    •  =
    • !=
    • >
    • <
    • >=
    • <=
• search_key (string, number, boolean):
  • Array items - for example:  $children[0].firstName, $isMarried[1].
  • Variables - for example: $number, $word, $booleanValue.
  • Fixed values - for example: 8, "word", True.
  • Other functions - for examples: DATE_FORMAT(), SUM(), ALL().
• search_array and result_array - a string, number, or boolean Array, for example: 
  • $children[*].firstName.
  • $isMarried[*].
  • TO_ARRAY().

Output:

The function returns multiple values.

Examples:

Table 39: Inputs/Outputs

LOWER()

The following sections describe the LOWER(text) function.

General Description

This function converts the characters of the input text from uppercase to lowercase.

Input and Output 

Input:

The function receives one string type input parameter, for example:  

• Array items - $kids[0].firstName or $color[1].
• Variables - $text1, $text2. 
• Fixed values.
• Returned values from different functions -  JOIN() or DATE_FORMAT().

Output:

The function returns a string.

Examples:

Table 40: Inputs/Outputs

LOWER_ARRAY()

The following sections describe the LOWER_ARRAY(array) function.

General Description

This function converts characters of an input Array from uppercase to lowercase.

Input and Output 

Input:

The function receives one input parameter, for example:  

• String type Array - $kids[*].firstName or $colors[*]. 
• Returned Array from the TO_ARRAY() function.

Output:

The function returns multiple strings.

Examples:

Table 41: Inputs/Outputs

MAX()

The following sections describe the MAX(items) function.

General Description

This function returns the maximum value from a list of numbers, ignoring empty values.

Input and Output 

Input:

The function receives a number Array or multiple number variables/values as input parameters (items), for example:

• Array items - $kids[0].age or $age[1].
• Variables - $num1 or $num2. 
• Fixed values.
• Returned values from different functions -  SUM() or MIM().

Output:

The function returns a number.

Examples:

Table 42: Inputs/Outputs

MIN()

The following sections describe the MIN(items) function.

General Description

This function returns the minimum value from a list of numbers, ignoring empty values.

Input and Output 

Input:

The function receives a number Array or multiple number variables/values as input parameters (items), for example:

• Array items - $kids[0].age or $age[1].
• Variables - $num1, $num2. 
• Fixed values.
• Returned values from different functions -  SUM() or MIM().

Output:

The function returns a number.

Examples:

Table 43: Inputs/Outputs

MONTH()

The following sections describe the MONTH(date) function.

General Description

This function returns the month of a specific date according to a format.

Input and Output 

Input:

The function receives a date parameter returned from a different function, for example:

• MONTH(TO_DATE($customer[0].dateBirth, "yyyy-dd-MM")).
• MONTH(DATE(1985/05/01)). 

Output:

The function returns a number.

Examples:

Table 44: Inputs/Outputs

MULTIPLY()

The following sections describe the MULTIPLY(value1, value2) function.

General Description

This function returns the multiplication product of two numbers.

Input and Output 

Input:

The function receives two number type input parameters:

• Array items - for example: $children[0].age or $salary[1].
• Variables - for example: $Age or $spouseAge.
• Fixed values.
• A returned value from a different function - for example: SUM() or MIM().

Output:

The function returns a number.

Examples:

Table 45: Inputs/Outputs

NOT()

The following sections describe the NOT(logical_expression) function.

General Description

This function returns the opposite value of a boolean input.  

Input and Output 

Input:

The function receives one boolean type input parameter:

• Array items - for example: $personalDetails[0].isMarried or $status[1].
• Variables - for example: $houseOwner or $isMarried.
• Fixed values.
• A returned value from a different function - for example: ALL() or ANY().

Output:

The function returns a boolean value (True or False).

Examples:

Table 46: Inputs/Outputs

NOT_EQUAL()

The following sections describe the NOT_EQUAL(value1, value2) function.

General Description

This function checks if two values are not equal.

Input and Output 

Input:

The function receives two input parameters from any type (number, string, boolean, date):  

• Array items - for example: $children[1] or $children[0].age
• Variables - for example: $word, $number1, $isMarried. 
• Fixed values - for example: "True", True, 8. 
• Returned values from different functions - DATE(), SUM(), ANY().

Output:

The function returns a boolean value (True or False).

Examples:

Table 47: Inputs/Outputs

OR()

The following sections describe the OR(logical_expression) function.

General Description

This function returns False if any of the provided arguments are logically True, and False if all the provided arguments are logically False.  

Input and Output 

Input:

The function receives one boolean type input parameter:

• Array items - for example: $personalDetails[0].isMarried or $status[1].
• Variables - for example: $houseOwner or $isMarried.
• Fixed values.
• A returned value from a different function - for example: ALL() or ANY().

Output:

The function returns a boolean value (True or False).

Examples:

Table 48: Inputs/Outputs

PAD()

The following sections describe the PAD(str, output_length, padding_element, padding format) function.

General Description

This function adds padding to a string up to the output length.

Input and Output 

Input:

The function receives four input parameters:  

• str (string):
  • Arrays items - for example: $children[1].name or $names[0].
  • String variables - for example: $word1 or $word2. 
  • Fixed values. 
  • Returned values from different functions such as TO_TEXT()
• output_length (number):
  • Variables - $number1 or $isMarried. 
  • Fixed values.
• padding_element (string):
  • Variables - for example: $word1 or $word2. 
  • Fixed values - for example: "*" or "/".
• padding_element (number) - 0 (right padding), 1 (left padding).

Output:

The function returns a string.

Examples:

Table 50: Inputs/Outputs

PAD_ARRAY()

The following sections describe the PAD_ARRAY(array, output_length, padding_element, padding format) function.

General Description

This function adds padding to all string values Array items up to their output length.

Input and Output 

Input:

The function receives four input parameters:  

• array (string), for example:
  • $children[*].name. 
  • $names[*]. 
  • Returned Array for the TO_ARRY() function.
• output_length (number):
  • Variables - $number1 or $isMarried. 
  • Fixed values.
• padding_element (string):
  • Variables - for example: $word1 or $word2. 
  • Fixed values - for example: "*" or "/".
• padding_element (number) - 0 (right padding), 1 (left padding).

Output:

The function returns multiple strings.

Examples:

Table 51: Inputs/Outputs

POSITION()

The following sections describe the POSITION(search_text, text_to_search, case_sensitive, starting_at_position) function.

General Description

This function returns the position at which a string/array is first found within text.

Input and Output 

Input:

The function receives five input parameters:

• search_text, text_to_search (string), for example:
  • Array items - $kids[0].firstName or names[1].
  • Variables - $word1 or $word2.
  • Fixed values.
  • Returned values from different functions such as TO_TEXT()or JOIN().
• case_sensitive (boolean) - True or False.
• starting_at_position (number).

Output:

The function returns a number.

Examples:

Table 52: Inputs/Outputs

POWER()

The following sections describe the POWER(base, exponent) function.

General Description

This function returns an exponential number (raised to a power).

Input and Output 

Input:

The function receives two number type input parameters (base and exponent):

• Array items - for example- $children[0].age or $salary[1].
• Variables - for example - $Age or $spouseAge.
• Fixed values.
• A returned value from a different function - for example: SUM() or MIM().

Output:

The function returns a number.

Examples:

Table 53: Inputs/Outputs

PROPER()

The following sections describe the PROPER(base, exponent) function.

General Description

This function capitalizes the first character of a word in a specified string and converts the following characters to lowercase.

Input and Output 

Input:

The function receives one string type input parameter (text):

• Array items - for example: $children[0].name or $name[1].
• Variables - for example: $word1 or $word2.
• Fixed values.
• A returned value from a different function - for example: TO_TEXT().

Output:

The function returns a number.

Examples:

Table 54: Inputs/Outputs

RAND_BETWEEN()

The following sections describe the RAND_BETWEEN(low, high) function.

General Description

This function returns a uniformly random integer between two values, inclusive of those values.

Input and Output 

Input:

The function receives two number type input parameters (low, high):

• Array items - for example: $children[0].age or $age[1].
• Variables - for example: $num1 or $num2.
• Fixed values.
• A returned value from a different function - for example: SUM().

Output:

The function returns a number.

Examples:

Table 55: Inputs/Outputs

REGEX_EXTRACT()

The following sections describe the REGEX_EXTRACT(text, regular_expression) function.

General Description

This function extracts matching substrings according to a regular expression.

Input and Output 

Input:

The function receives two string input parameters:  

• text :
  • Array items - for example: $children[1].name or $names[0].
  • Variables - for example: $word1 or $word2. 
  • Fixed values. 
  • Returned values from different functions such as TO_TEXT().
• regular expression - tokens of regular expressions such as:
  • "\d+"
  • "\d*"
  • "\w+"
  • "\s"

Output:

The function returns a string.

Examples:

Table 56: Inputs/Outputs

REGEX_MATCH()

The following sections describe the REGEX_MATCH(text, regular_expression) function.

General Description

This function checks whether a portion of text matches a regular expression.

Input and Output 

Input:

The function receives two string input parameters:  

• text :
  • Array items - for example: $children[1].name or $names[0].
  • Variables - for example: $word1 or $word2. 
  • Fixed values. 
  • Returned values from different functions such as TO_TEXT().
• regular expression - tokens of regular expressions such as:
  • "\d+"
  • "\d*"
  • "\w+"
  • "\s"

Output:

The function returns a boolean value (True or False).

Examples:

Table 57: Inputs/Outputs

REGEX_REPLACE()

The following sections describe the REGEX_REPLACE(text, regular_expression, replacement_text) function.

General Description

This function replaces matching substrings according to a regular expression.

Input and Output 

Input:

The function receives three string input parameters:  

• text and replacement_text:
  • Array items - for example: $children[1].name or $names[0].
  • Variables - for example: $word1 or $word2. 
  • Fixed values. 
  • Returned values from different functions such as TO_TEXT().
• regular expression - tokens of regular expressions such as:
  • "\d+"
  • "\d*"
  • "\w+"
  • "\s"

Output:

The function returns a string.

Examples:

Table 58: Inputs/Outputs

REMOVE_EMPTY()

The following sections describe the REMOVE_EMPTY(array) function.

General Description

This function removes empty strings and null values from an Array. 

Input and Output 

Input:

The function receives an Array or multiple variables/values of any type as input parameters (array): 

• Array or Array items - for example: $children[*].age, $salary[*], $children[0].age, $salary[1].
• Variables - for example: $Age, $isMarried, $word.
• Fixed values - for example: 8, True, "True".
• A returned value from a different function - for example: DATE(), TO_TEXT(), MIM().

Output:

The function returns multiple strings.

Examples:

Table 59: Inputs/Outputs

REPLACE()

The following sections describe the REPLACE(text, position, length, replacement_text) function.

General Description

This function replaces a portion of a text with a different text.

Input and Output 

Input:

The function receives four input parameters:

• text and replacement_text (string), for example:
  • Array items - $kids[0].firstName or names[1].
  • Variables - $word1 or $word2.
  • Fixed values.
  • Returned values from different functions such as TO_TEXT().
• position and length (number), for example:
  • Variables - $num1 or $num2.
  • Fixed values.

Output:

The function returns a string.

Examples:

Table 60: Inputs/Outputs

ROUND()

The following sections describe the ROUND(value, place) function.

General Description

This function rounds a number to a certain number of decimal places according to standard rules.

Input and Output 

Input:

The function receives two number type input parameters - value and place:

• Array items - $details[0].age or age[1].
• Variables - $num1 or $num2.
• Fixed values.

Output:

The function returns a number

Examples:

Table 61: Inputs/Outputs

SUBSTITUTE()

The following sections describe the SUBSTITUTE(search_text, text_to_search, replace_with, case_sensitive, occurrence_number) function.

General Description

This function replaces existing text with new text in a string. 

Input and Output 

Input:

The function receives five input parameters:

• search_text, text_to_search, replace_with(string), for example:
  • Array items - $kids[0].firstName or names[1].
  • Variables - $word1 or$word2.
  • Fixed values.
  • Returned values from different functions such as TO_TEXT()or JOIN().
• case_sensitive (boolean) - True or False.
• occurrence_number (number).

Output:

The function returns a string.

Examples:

Table 62: Inputs/Outputs

SUBSTITUTE_ARRAY()

The following sections describe the SUBSTITUTE_ARRAY(search_text, text_to_search, replace_with, case_sensitive, occurrence_number) function.

General Description

This function replaces existing text with new text in a string for all items in an array.

Input and Output 

Input:

The function receives five input parameters:

• search_text a string type Array, for example:
  • $kids[*].firstName.
  • names[*].
• text_to_search, replace_with(string), for example:
  • Array items - $kids[0].firstName or names[1].
  • Variables - $word1 or $word2.
  • Fixed values.
  • Returned values from different functions such as TO_TEXT() or JOIN().
• case_sensitive (boolean) - True or False.
• occurrence_number (number).

Output:

The function returns multiple strings.

Examples:

Table 63: Inputs/Outputs

SPLIT()

The following sections describe the SPLIT(str_to_split, split_by) function.

General Description

This function splits one string into an array of strings.

Input and Output 

Input:

The function receives two string type input parameters, str_to_split and split_by:

• Array items - $kids[0].firstName or names[1].
• Variables - $word1 or $word2.
• Fixed values.
• Returned values from different functions such as TO_TEXT().

Output:

The function returns multiple strings.

Examples:

Table 64: Inputs/Outputs

SQRT()

The following sections describe the SQRT(value1) function.

General Description

This function returns the square root of a positive number.

Input and Output 

Input:

The function receives one number type input parameter:

• Array items - for example: $children[0].age or $salary[1].
• Variables - for example: $Age or $spouseAge.
• Fixed values.
• A returned value from a different function - for example: SUM() or MIM().

Output:

The function returns a number.

Examples:

Table 65: Inputs/Outputs

SUBSTRING()

The following sections describe the SUBSTRING(text, start_index, length) function.

General Description

This function returns a segment of a string. Beginning with the start index and up to a specific length.

Input and Output 

Input:

The function receives three input parameters:

• substring (string):
  • Array items - for example: $children[0].age or $salary[1].
  • Variables - for example: $Age or $spouseAge.
  • Fixed values.
  • A returned value from a different function - for example: TO_TEXT().
• start_index and length (number):
  • Variables - for example: $num1, $num2.
  • Fixed values.

Output:

The function returns a string.

Examples:

Table 66: Inputs/Outputs

SUBTRACT()

The following sections describe the SUBTRACT(value1, value2) function.

General Description

This function returns the difference (subtraction result) of two numbers.

Input and Output 

Input:

The function receives two input number type parameters:

• Array items - for example: $children[0].age or $salary[1].
• Variables - for example: $Age or $spouseAge.
• Fixed values.
• A returned value from a different function - for example: SUM() or MIM().

Output:

The function returns a number.

Examples:

Table 67: Inputs/Outputs

SUM()

The following sections describe the SUM(items) function.

General Description

This function returns the sum of multiple numbers. Equivalent to the `+` operator. 

Input and Output 

Input:

The function receives a number Array or multiple number variables/values as input parameters (items): 

• Array items - for example: $children[0].age or $salary[1].
• Variables - for example: $Age or $spouseAge.
• Fixed values.
• A returned value from a different function - for example: MAX() or MIM().

Output:

The function returns a number.

Examples:

Table 68: Inputs/Outputs

SUM_IF()

The following sections describe the SUM_IF(operator, criterion, items) function.

General Description

This function returns a conditional sum across a range.

Input and Output 

Input:

The function receives three input parameters:

• operator (string):
  •  =
  • !=
  • >
  • <
  • >=
  • <=
• criterion (number):
  • Array items, for example:  $children[0].firstName or $isMarried[1].
  • Number variables, for example: $num1 or $num2.
  • Fixed values.
  • Other functions, for examples: MIN(), SUM(), MAX(), UNIQUE(). 
• items - a number Array or multiple number variables/values as input parameters (items):
  • Array items - for example: $children[0].age or $salary[1].
  • Variables - for example: $Age or $spouseAge.
  • Fixed values.
  • Other functions, for examples: MIN(), SUM(), MAX(), UNIQUE(). 

Output:

The function returns a number.

Examples:

Table 69: Inputs/Outputs

SWITCH()

The following sections describe the SWITCH(checked_value, case, result, default) function.

General Description

This function checks a value against cases and returns a matching single value result.

Input and Output 

Input:

The function receives four input parameters:

• checked_value (string, number, boolean):
  • Array items - for example:  $children[0].firstName or $isMarried[1].
  • Variables - for example: $num1, $word, $isMarried.
  • Fixed values - for example: 8, True, "True".
  • Other functions - for examples: MIN(), SUM(), MAX(), UNIQUE(). 
• case - (string, number, boolean): 
  • Array or Array items - for example:  $children[*].firstName, $isMarried[*], $children[0].firstName, $isMarried[1]
  • Variables - for example: $num1, $word, $isMarried.
  • Fixed values - for example: 8, True, "True".
  • Other functions - for examples: TO_ARRAY(), SUM(), MAX(), UNIQUE(). 
• result and default (string, number, boolean): 
  • Array items - for example:  $children[0].firstName, $isMarried[1].
  • Variables - for example: $num1, $word, $isMarried.
  • Fixed values - for example: 8, True, "True".
  • Other functions - for examples: MIN(), SUM(), MAX().

Output:

The function returns a single value (string, number, or boolean).

Examples:

Table 70: Inputs/Outputs

SWITCH_ARRAY()

The following sections describe the SWITCH_ARRAY(checked_value, case, array_result, default) function.

General Description

This function checks a value against cases and returns a matching array result.

Input and Output 

Input:

The function receives four input parameters:

• checked_value (string, number, boolean):
  • Array items - for example:  $children[0].firstName or $isMarried[1].
  • Variables - for example: $num1, $word, $isMarried.
  • Fixed values - for example: 8, True, "True".
  • Other functions - for examples: MIN(), SUM(), MAX(), UNIQUE(). 
• case, array_result, and default (string, number, boolean): 
  • Array or Array items, for example:  $children[*].firstName, $isMarried[*], $children[0].firstName, $isMarried[1]
  • Variables - for example: $num1, $word, $isMarried.
  • Fixed values - for example: 8, True, "True".
  • Other functions - for examples: TO_ARRAY(), SUM(), MAX(), UNIQUE(). 

Output:

The function returns multiple values (string, number, or boolean)

Examples:

Table 71: Inputs/Outputs

TO_ARRAY()

The following sections describe the TO_ARRAY(str) function.

General Description

This function converts a provided set of values into an Array.

Input and Output 

Input:

The function receives an Array or multiple variables/values of any type as input parameters (str): 

• Array or Array items - for example: $kids[*].birthDay, $birthdays[*], $kids[0].birthDay, $birthdays[1].
• Variables - for example: $num, $word, $isMarried.
• Fixed values - for example: "2202/05/01", True, 8.
• Returned values from different functions, such as: DATE(), TO_TEXT(), TO_NUM().

Output:

The function returns multiple strings.

Examples:

Table 72: Inputs/Outputs

TO_DATE()

The following sections describe the TO_DATE(string_date, format) function.

General Description

This function converts a string date with a predefined format to a date type.

Input and Output 

Input:

The function receives two input parameters:  

• string_date (string):
  • Array items - for example: $kids[0].birthDay or $birthdays[1].
  • Variables - for example: $birthDay.
  • fixed values - for example: "2202/05/01". 
• format - comprised of a month, day, and year. For example: 
  • "d/M/y" or "d-M-y" - (1/5/85 or 1-5-85).
  • "dd/MM/yy" or "dd-MM-yy"  (01/05/85 or 01-05-85).
  • "ddd/MMM/yyyy" or "ddd-MMM-yyyy" (Mon/Feb/1985 or Mon-Feb-1985). 
  • "dddd/MMMM/yyyy" or "dddd-MMMM-yyyy" (Monday/February/1985 or Monday/February/1985).

Output:

The function returns a date.

Examples:

Table 73: Inputs/Outputs

TO_NUM()

The following sections describe the TO_NUM(str, separator_style) function.

General Description

This function converts a string to a number.

Input and Output 

Input:

The function receives two string type input parameters:

• str:
  • Array items - for example: $kids[0].birthDay or $birthdays[1].
  • Variables - for example: $word1.
  • Fixed values - for example: "1234".
  • Returned values from different functions, such as TO_TEXT().
• separator_style - an optional parameter that determines the separator style between digits, for example:
  • Fixed values - for example: "us" (default) or "de". 
  • Variables - for example: $separatorType.

Output:

The function returns a number.

Examples:

Table 74: Inputs/Outputs

TO_TEXT()

The following sections describe the TO_TEXT(value) function.

General Description

This function converts a value to a string.

Input and Output 

Input:

The function receives one input parameter - value (string, number, boolean):

• Array items - for example: $kids[0].birthDay or $isMarried[1].
• Variables - for example: $word1, $num1, $isMarried.
• Fixed values - for example: "1234", True, 8.
• Returned values from different functions, such as SUM(), ANY(), MAX().

Output:

The function returns a string value.

Examples:

Table 75: Inputs/Outputs

TRIM()

The following sections describe the TRIM(text, padding_element_to_trim) function.

General Description

This function removes leading and tailing padding elements in a string.

Input and Output 

Input:

The function receives two string type input parameters (text, padding_element_to_trim):

• Array items - for example: $children[0].name, $name[1].
• Variables - for example: $word.
• Fixed values.
• A returned value from a different function - for example: TO_TEXT().

Output:

The function returns a string.

Examples:

Table 76: Inputs/Outputs

TYPE()

The following sections describe the TYPE(value) function.

General Description

This function returns the type of value/variable that was entered into the function.

Input and Output 

Input:

The function receives one input parameter from any type (string, number, boolean, date):

• Array items - for example: $children[0].age or $salary[1].
• Variables - for example: $Age or $spouseAge.
• Fixed values - for example: True, "True", 8.
• A returned value from a different function - for example: TO_TEXT(), MIN(), or DATE().

Output:

The function returns a string.

Examples:

Table 77: Inputs/Outputs

UNIQUE()

The following sections describe the UNIQUE(array) function.

General Description

This function returns the unique items of an Array. 

Input and Output 

Input:

The function receives one input parameter, an Array (string, boolean, number):  

• Array - for example: $children[*], $children[*].firstName, $isMarried[*].
• Array items - for example: $children[0], $children[1].firstName, $isMarried[2].
• Variables - for example: $word1, $num1, isMarried.
• Fixed values - for example: "1234", True, 8.
• An Array returned from the TO_ARRAY() function.

Output:

The function returns multiple string values.

Examples:

Table 78: Inputs/Outputs

UPPER()

The following sections describe the UPPER(text) function.

General Description

This function converts the characters of the input text from lowercase to uppercase.

Input and Output 

Input:

The function receives one string type input parameter, for example:  

• Array items - $kids[0].firstName and $color[1].
• Variables - $text1, $text2. 
• Fixed values.
• Returned values from different functions -  JOIN() or DATE_FORMAT().

Output:

The function returns a string.

Examples:

Table 79: Inputs/Outputs

UPPER_ARRAY()

The following sections describe the UPPER_ARRAY(array) function.

General Description

This function converts characters of an input Array from lowercase to uppercase.

Input and Output 

Input:

The function receives one input parameter, for example:  

• String type Array - $kids[*].firstName and $colors[*]. 
• Returned Array from the TO_ARRAY() function.

Output:

The function returns multiple strings.

Examples:

Table 80: Inputs/Outputs

VALUE()

The following sections describe the VALUE(reference_array, position) function.

General Description

This function returns a value from an Array according to the provided position.

Input and Output 

Input:

The function receives two input parameters:

• reference_array (string, number, boolean):
  • Array - for example: $kids[*].names or names[*].
  • A returned Array from the TO_ARRAY() function.
• position (number):
  • Variable.
  • Fixed value.

Output:

The function returns a value (string, number, boolean).

Examples:

Table 81: Inputs/Outputs

WEEK_DAY()

The following sections describe the WEEK_DAY(date) function.

General Description

This function returns a number representing the day of the week of the date provided (Sunday=1).

Input and Output 

Input:

The function receives one date type input parameter from date type functions such as:

• DATE() 
• TO_DATE()

Output:

The function returns a number.

Examples:

Table 82: Inputs/Outputs

WEEK_NUM()

The following sections describe the WEEK_NUM(date, week_starts_on) function.

General Description

This function returns a number representing the week of the year of the date provided.

Input and Output 

Input:

The function receives one date type input parameter from date type functions such as DATE() or TO_DATE() and an optional string type parameter which represents the index of the first day of the week: 

• 0 - Sunday
• 1 - Monday
• 2 - Tuesday
• 3 - Wednesday
• 4 - Thursday
• 5 - Friday
• 6 - Saturday

Output:

The function returns a number.

Examples:

Table 83: Inputs/Outputs

YEAR()

The following sections describe the YEAR(date) function.

General Description

This function returns the year of a specific date according to a format.

Input and Output 

Input:

The function receives a date parameter returned from a different function, for example:

• YEAR(TO_DATE($customer[0].dateBirth, "yyyy-dd-MM")).
• YEAR(DATE(1985/05/01)). 

Output:

The function returns a number. 

Examples:

Table 84: Inputs/Outputs

Formatting Functions ()

The following sections describe the Formula Editor's formatting functions.

FORMAT_TIN()

The following sections describe the FORMAT_TIN(tin_to_format) function.

General Description

This function converts a 9-digit number to a Taxpayer Identification Number (TIN) format.

Input and Output 

Input:

The function receives one string type input parameter:

• Array items - for example: $format[0].tin or $tin[0].
• Variables - for example: $variable1 or $variable2. 
• Fixed values.

Output:

The function returns a TIN formatted string.

Examples:

Table 85: Inputs/Outputs

FORMAT_PRECISION()

The following sections describe the FORMAT_PRECISION(number_to_format, precision) function.

General Description

This function formats a number to a given precision. 

Input and Output 

Input:

The function receives two input parameters:

• number_to_format (string):
  • Array items - for example: $salary[0].amount or $salary[0].
  • Variables - for example: $variable1 or $variable2. 
  • Fixed values.
  • A returned string from the TO_TEXT() function.
• precision (number):
  • Array items - for example: $salary[0].amount or $salary[0].
  • Variables - for example: $variable1 or $variable2. 
  • Fixed values.
  • A returned number from the TO_NUM() function.

Output:

The function returns a precise number (string).

Examples:

Table 86: Inputs/Outputs

FORMAT_SSN()

The following sections describe the FORMAT_SSN(ssn_to_format) function.

General Description

This function converts a 9-digit number to a Social Security Number (SSN) format.

Input and Output 

Input:

The function receives one string type input parameter:

• Array items - for example: $format[0].ssn or $ss[0].
• Variables - for example: $variable1 or $variable2. 
• Fixed values.

Output:

The function returns an SSN formatted string.

Examples:

Table 87: Inputs/Outputs

FORMAT_THOUSANDS()

The following sections describe the FORMAT_THOUSANDS(number_to_format, separator_style) function.

General Description

This function formats a number by adding separators.

Input and Output 

Input:

The function receives two parameters:

• number_to_format (string/number):
  • Array items - for example: $salary[0].amount or $salary[0].
  • Variables - for example: $variable1 or $variable2. 
  • Fixed values - for example: 10000 or "10000".
  • A returned string from the TO_TEXT() function.
• separator_style (string) - an optional parameter that determines the separator style between digits:
  • Fixed values - for example: "us" (default), "de".
  • Variables - for example: $separatorType.

Output:

The function returns a number (string) separated by separators.

Examples:

Table 88: Inputs/Outputs

FORMAT_CUSTOM()

The following sections describe the FORMAT_CUSTOM(text, format_pattern) function.

General Description

This function converts and formats text according to a specified pattern.

Input and Output 

Input:

The function receives two string type input parameters:

• text:
  • Array items - for example: $format[0].ssn or $ss[0].
  • Variables - for example: $variable1 or $variable2. 
  • Fixed values.
• format_pattern - the desired formatting pattern comprised of a pound sign (#) and separators.

Output:

The function returns a custom formatted string.

Examples:

Table 89: Inputs/Outputs

Use Case Examples

The following sections describe a few use case examples of the Formula Editor feature.

Using a Function within a Function

We want the ALL() function to check if a value that is being returned from the MIN() function is lower than the value of a specific variable, and to return a boolean value, True or False. Figure 15 describes a data structure that contains four string type variables (1): 

• num1
• num2
• num3
• num4

The data structure also contains a boolean type variable, result (2), that will contain the ALL() function returned value. 

Figure 15: Data Structure

To use the formula editor, perform the following steps (see Figure 16 to Figure 19):

Figure 16: Formula Option

1. Click the result variable (1).
2. Click the Value dropdown (2).
3. Click Formula (3).

Result:

The Edit Formula button (4) appears:

Figure 17: Edit Formula

4. Click the Edit Formula button (4).

Result:

The Formula Editor window appears:

Figure 18: Adding the ALL() Function

5. Locate the ALL() function (5).
6. Double click it or click the Use button (6).

Result:

The function is added:

Figure 19: Added ALL() Function

The function will be configured according to Table 90:

Table 90: ALL() Function Parameters and Values

The function will look like this (see Figure 20):

Figure 20: Configured Function

According to the configuration of the function, the result variable will contain True if the returned value from the MIN() function is smaller than the value of the num4 variable, else, False.

Returning an Array

We want to display customer information according to a specific ID. 

Figure 21 describes a data structure that contains an object with nested variables (1):

• personalDetails (object)
• firstName (string)
• lastName (string)
• age (string)
• birthDate (string)
• idNumber (string)

The data structure also contains a string type variable named result_array (2) that will contain a returned Array and a string type variable, idNumber (3), that will be used to determine the Array values that will be returned.

Figure 21: Data Structure

To use the formula editor, perform the following steps (see Figure 22 to Figure 25):

Figure 22: Formula Option

1. Click the resultArray variable (1).
2. Click the Value dropdown (2).
3. Click Formula (3).

Result:

The Edit Formula button (4) appears:

Figure 23: Edit Formula

4. Click the Edit Formula button (4).

Result:

The Formula Editor window appears:

Figure 24: Adding the IF_ARRAY() Function

5. Locate the IF_ARRAY() function (5).
6. Double click it or click the Use button (6).

Result:

The function is added:

Figure 25: Added IF_ARRAY() Function

The function will be configured according to Table 91:

Table 91: IF_ARRAY() Function Parameters and Values

The function will look like this (see Figure 26):

Figure 26: Configured Function

According to the configuration of the function, the resultArray variable will contain the values returned from the TO_ARRAY() function if the value of the idNumber variable equals the value of the personalDetails[0].idNumber item. The JOIN() function enables displaying multiple strings with a comma separating between values. 

Using a Formula for conditioning

We want to display spouse information components only if the status of the end-user is Married. 

Figure 27 describes a data structure that contains the following variables (1):

• isMarried (boolean)
• spouseDetails (object)
• fullName (string)
• birthDate (string)
• idNumber (string) 

Figure 27: Data Structure

To use the Formula editor, perform the following steps (see Figure 28 to Figure 31):

Figure 28: Formula Option

1. Click the spouseDetails variable (1).
2. Click Condition (2).
3. Click the dropdown and select Formula (3).

Result:

The Edit Formula button (4) appears:

Figure 29: Edit Formula

4. Click the Edit Formula button (4).

Result:

The Formula Editor window appears:

Figure 30: Adding the AND() Function

5. Locate the AND() function (5).
6. Double click it or click the Use button (6).

Result:

The function is added:

Figure 31: Added AND() Function

The function will be configured according to Table 92:

Table 92: AND() Function Parameter and Value

The function will look like this (see Figure 32):

Figure 32: Configured Function

The function will return a True value if the isMarried variable contains a True value as well, else False. Because the formula was created using the Visibility condition dropdown, the appearance of the components connected to data items that are nested under the spouseDetails data item while using the digital process depends on the returned value of the AND() function:

• True - visible
• False - not visible

Formula Editor Simulator 

(See Figure 33)

The simulator helps platform users tune up functions’ performance and optimize the Formula editor usage. Once a function is added and configured (1), the simulator can be accessed by clicking the Simulate button (2).

Figure 33: Accessing the Simulator

Use Case Example

(See Figure 34)

Figure 34 displays an IF() function that checks if the value of the num1 variable is even by comparing the IS_EVEN() function result to the boolean value True: 

Figure 34: IF() Function

To check if the function works correctly using the simulator, perform the following steps:

1. Click Simulate (1).

Result:

The Input (2) section appears:

Figure 35: Input Values

2. Input values according to the parameters (2).

Result:

A Result (3) appears:

Figure 36: Simulated Result

3. View the simulated results (3).
4. Repeat steps 2 and 3 as needed to simulate additional input scenarios and results.

Appendix A - Formula Behaviors

The following appendix describes different formula behaviors.

Math Operators

See Table 93:

Table 93: Math Operators Behaviors

Comparison Operators

See Table 94 and Table 95:

Table 94: Comparison of Operators Behaviors

Table 95: Comparison of Operators Behaviors

Logical Operators

See Table 96:

Table 96: Logical Operators

Date Functions

The following sections describe date functions behaviors.

DATE_ADD() Function

See Table 97:

Table 97: DATE_ADD() Function

DATE_DIFF() Function

See Table 98:

Table 98: DATE_DIFF() Function 

DATE() Function

See Table 99:

Table 99: DATE() Function 

Other DATE Functions

See Table 100:

Table 100: Other Date Functions 

Math Functions

The following sections describe math functions behaviors.

COUNT(), COUNT_UNIQUE(), SUM(), AVERAGE(), MIN(), MAX() Functions

See Table 101:

Table 101: COUNT(), COUNT_UNIQUE,SUM(), AVERAGE(), MIN(), MAX() Functions

SUBTRACT(), DIVIDE(), MULTIPLY() Functions

See Table 102:

Table 102: SUBTRACT(), DIVIDE(), MULTIPLY() Functions 

IS_EVEN(), IS_ODD Functions

See Table 103:

Table 103: IS_EVEN(), IS_ODD Functions 

SQURT() Function

See Table 104:

Table 104: SQURT() Function 

COUNT_IF(), SUM_IF() Functions

See Table 105:

Table 105: COUNT_IF(), SUM_IF() Functions 

IS_BETWEEN() Function

See Table 106:

Table 106: IS_BETWEEN() Function 

POWER() Function

See Table 107:

Table 107: POWER() Function 

ROUND() Function

See Table 108:

Table 108: ROUND() Function 

Information Functions

The following sections describe information functions behaviors.

IS_EMPTY() Function

See Table 109:

Table 109: IS_EMPTY() Function

IS_NUMBER() Function

See Table 110:

Table 110: IS_NUMBER() Function

IS_EMPTY_ARRAY() Function

See Table 111:

Table 111: IS_EMPTY_ARRAY() Function

IS_BOOLEAN(), IS_TEXT(), TYPE() Functions

See Table 112:

Table 112: IS_BOOLEAN(), IS_TEXT(), TYPE() Functions

Textual Functions

The following sections describe textual functions behaviors.

JOIN(), CONCAT() Functions

See Table 113:

Table 113: JOIN(), CONCAT() Functions

LENGTH(), LOWER(), UPPER(), PROPER() Functions

See Table 114:

Table 114: LENGTH(), LOWER(), UPPER(), PROPER() Functions

PAD() Function

See Table 115:

Table 115: PAD() Function

POSITION() Function

See Table 116:

Table 116: POSITION() Function

REPLACE() Function

See Table 117:

Table 117: REPLACE() Function

SUBSTITUTE() Function

See Table 118:

Table 118: SUBSTITUTE() Function

SUBSTRING() Function

See Table 119:

Table 119: SUBSTRING() Function

TRIM() Function

See Table 120:

Table 120: TRIM() Function

SPLIT() Function

See Table 121:

Table 121: SPLIT() Function

REGEX_EXTRACT, REGEX_MATCH() Functions

See Table 122:

Table 122: REGEX_EXTRACT(), REGEX_MATCH() Functions

REGEX_REPLACE() Function

See Table 123:

Table 123: REGEX_REPLACE() Function

Array Functions

The following sections describe array functions behaviors.

LOOKUP(), LOOKUP_ARRAY() Functions

See Table 124:

Table 124: LOOKUP(), LOOKUP_ARRAY() Functions

ARRAY_LENGTH() Function

See Table 125:

Table 125: ARRAY_LENGTH() Function

LOWER_ARRAY, UPPER_ARRAY(), REMOVE_EMPTY(), UNIQUE() Functions

See Table 126:

Table 126: LOWER_ARRAY(), UPPER_ARRAY(), REMOVE_EMPTY(), UNIQUE() Functions

FILTER() Function

See Table 127:

Table 127: FILTER() Function

PAD_ARRAY() Function

See Table 128:

Table 128: PAD_ARRY() Function

SUBSTITUTE_ARRAY() Function

See Table 129:

Table 129: SUBSTITUTE_ARRAY() Function

VALUE() Function

See Table 130:

Table 130: VALUE() Function

Conversion Functions

The following sections describe conversion functions behaviors.

TO_ARRAY() Function

See Table 131:

Table 131: TO_ARRAY() Function

TO_NUM() Function

See Table 132:

Table 132: TO_NUM() Function

TO_TEXT() Function

See Table 133:

Table 133: TO_TEXT() Function

DATE_FORMAT() Function

See Table 134:

Table 134: DATE_FORMAT() Function

FORMAT_TIN(), FORMAT_SSN, FORMAT_THOUSANDS() Functions

See Table 135:

Table 135: FORMAT_TIN(), FORMAT_SSN, FORMAT_THOUSANDS Functions

FORMAT_PRECISION() Function

See Table 136:

Table 136: FORMAT_PRECISION() Function

Logical Functions

The following sections describe conversion functions behaviors.

NOT() Function

See Table 137:

Table 137: NOT() Function

AND(), OR() Functions

See Table 138:

Table 138: AND(), OR() Functions

ALL(), ANY() Functions

See Table 139:

Table 139: ALL(), ALL() Functions

IF(), IF_ARRAY() Functions

See Table 140:

Table 140: IF(), IF_ARRAY() Functions

SWITCH(), SWITCH_ARRAY() Functions

See Table 141:

Table 141: SWITCH(), SWITCH_ARRAY() Functions