Formula language
Two videos are available to accompany this topic: Formulas and Split multi-line fields.
Arithmetic operators
The Validation Formula Language (VFL) of Decipher IDP supports basic arithmetic operators. Complex validation rules can be built by combining these simple operators with predefined or custom functions.
Supported arithmetic operators:
+ |
Addition - binary (requires two arguments) function: a + b |
- |
Subtraction - Binary or unary (one argument) function: -a - b |
* |
Multiplication - Binary function: a * b |
/ |
Division - Binary function: a / b |
Operations of addition, subtraction, multiplication, and division correspond literally to their respective mathematical operators with standard priority.
Variables
There is integrated variable support in VFL for user defined variables accessible through the special variable lookup interface. Formulas with variables will be parsed and during the evaluation these variables will be replaced with their values returned by the variable lookup.
Variable names must start with a letter and can contain alphanumeric characters and underscores ("_").
Additionally, colon (":") and diesis ("#") characters can be used for creating parametric variables.
- Example variable name: "FT_SUBTOTAL".
- Example formula with variables: "FT_SUBTOTAL + FT_TAX", "FT_GRANDTOTAL*0.20 + 1000", etc.
Parametric variables
Parametric variables can be used to specify formulas for table columns. For each row the parameter will be replaced with the row's index.
- Example parametric variable name: "FT_LINE_TOTAL:#".
- Example formula with parametric variables: "FT_LINE_TOTAL:# - FT_LINE_DISCOUNT:# + 1000".
The formula above will be converted automatically to "FT_LINE_TOTAL:0 - FT_LINE_DISCOUNT:0 + 1000" for the first row of the item table and to "FT_LINE_TOTAL:1 - FT_LINE_DISCOUNT:1 + 1000" for the second row of the item table, and so on for the next rows.
Variable names
Variable names can be constructed from both uppercase and lowercase letters and there are no restrictions, but keep in mind that during variable lookup the names are case sensitive.
Special variables
SELF - Self reference in formula, this variable is replaced with the value of the field which is validated with the specified formula. For example we can use the following formula for the grand total field: "IF(VAREXISTS(L(FT_TAX)), FT_SUBTOTAL + FT_TAX, SELF)". The formula above can be interpreted like this: If there is a value for the variable FT_TAX then the grand total field must be equal to "FT_SUBTOTAL + FT_TAX", if there is no value for FT_TAX then the grand total will not be calculated - the initial value will be used for the field.
Predefined functions
The VFL supports wide range of predefined functions in different categories: arithmetic, logical, string manipulation, date-time, etc.
Function names are case insensitive – therefore the following names are equivalent: "varexists", "VarExists", "VAREXISTS".
Functions can be combined with variables, and also be embedded into other functions for maximum flexibility. For example: "MIN(MIN(6,5),MAX(1,2),SUM(FT_TAX,3,SUM(2,3,5)))".
The example above can be written more clearly using scoped indentation like in programming languages:
MIN
(
MIN(6,5),
MAX(1,2),
SUM
(
FT_TAX,
3,
SUM(2,3,5)
)
)
Any formula that returns an array will only return the first value. To access the array, set the field as a dynamic list so you can select a single value, or use one of the predefined array functions.
Function descriptions
MIN |
Returns the smallest of its arguments: MIN(2, 5, -8, 12) -> -8 |
MAX |
Returns the largest of its arguments: MAX(2, 5, -8, 12) -> 12 |
SUM |
Computes the sum of its arguments: SUM(1,2,3) -> `` 6`` |
PRODUCT |
Computes the product of its arguments: PRODUCT(-2,2) -> -4 |
AVERAGE |
Returns the average of its arguments: AVERAGE(5,6) -> 5.5 |
SIN |
Computes the sine of its argument (degrees): SIN(90) -> 1 |
COS |
Computes the cosine of its argument (degrees): COS(90) -> 0 |
SQRT |
Computes the square root of its argument: SQRT(100) -> 10 |
POW |
Computes the value of the first argument raised to the power specified with the second argument: POW(10, 2) -> 100 |
ROUND |
Function that rounds with precision. The function uses two arguments:
Examples:
|
Number of arguments:
- Functions SIN, COS and SQRT require only one argument
- The power function POW requires exactly two arguments - the base and the exponent
- Functions MIN, MAX, SUM, PRODUCT and AVERAGE can be used with variable number of arguments
Logical functions always return:
- 0 - logical FALSE value
- 1 - logical TRUE value
AND |
Logical AND: AND(1,2,0) -> 0 (FALSE) |
OR |
Logical OR: OR(1,2,0) -> 1 (TRUE) |
NOT |
Logical NOT: NOT(1) -> 0 (FALSE) |
EQ |
Equality test function: EQ(2,-2,2,2) -> 0 (FALSE), EQ(1,1,1) -> 1 (TRUE) |
GREATER |
Tests if the first argument is greater than the second argument: GREATER(3.25, 3.5) -> 0 (FALSE) |
LESS |
Tests if the first argument is less than the second argument: LESS(3.25, 3.5) -> 1 (TRUE) |
GREATEREQ |
Tests if the first argument is greater than or equal to the second argument: GREATEREQ(3.25, 3.25) -> 1 (TRUE) |
LESSEQ |
Tests if the first argument is less than or equal to the second argument: LESSEQ(3.25, 3.25) -> 1 (TRUE) |
IF |
Conditional operator function with following argument list: IF(logical_test, value_if_true, value_if_false) The first the first argument logical_test expression is evaluated. If the result of this evaluation is logical TRUE, then the second argument value_if_true is evaluated and the result of this evaluation is returned. If the result of logical_test is logical FALSE, then the third argument value_if_false is evaluated and the result of this evaluation is returned. Examples:
|
Number of arguments:
- All logical functions can be combined with variables and other functions.
- The functions AND, OR and EQ can be used with variable number of arguments.
- The functions NOT and VAREXISTS are one-argument functions, additionally VAREXISTS can be used only with variables as argument of the function.
- The functions GREATER, LESS, GREATEREQ, LESSEQ are two argument functions.
- The conditional operator function IF requires exactly 3 arguments as specified in the table above.
The VFL of SSI supports string literals and variables with string values which can be used with functions specifically designed for strings.
String literals can be inserted in formulas in quotes - "sample string literal".
Note the new line character \n is supported in string formulas and can be used to read multi-line data.
STRCMP |
Compares strings with case sensitive logic. The function uses two arguments. The function will return 1 if arg1 is greater than arg2. The return value is -1 if arg1 is less than arg2. The return value is 0 if arg1 and arg2 are equal. Examples:
|
STRICMP |
Same as the function above, but the string comparison is case insensitive. |
STRJOIN |
Joins all string arguments in a single string. Example:
|
SUBSTR |
Returns a substring from a given string. The function uses three arguments:
Examples:
|
STRLEFT STRRIGHT |
Returns the starting right or left symbols from a given string. The function uses two arguments:
Example:
|
SPLIT |
Splits a string using the specified delimiter and returns an array. The function uses two arguments:
Example:
|
SPLITGET |
Splits a string by a separator to substrings and returns one of them.
Example:
|
SPLITGETREV |
Splits a string by a separator to substrings and returns one of them in reverse order.
Example:
|
STRCONTAINS |
Returns 1 if the input string contains the specified substring. Otherwise returns 0. The function uses two arguments:
Examples:
|
STRLEN |
Returns the number of characters in the specified input string. The function use one argument:
Example:
|
STRREPLACE |
Replaces all occurrences of a substring in the input string with a new one and returns the modified string. The function uses three arguments:
Examples:
|
STRUPPER |
Returns an uppercase version of the input string. The function uses one argument. Example:
|
STRLOWER |
Returns an lowercase version of the input string. The function uses one argument. Example:
|
STRTRIM |
Removes any starting or trailing spaces from the input string and returns it. The function uses one argument. Example:
|
SPRINTF |
Formats a string with the data specified by the arguments. The function can have unlimited number of arguments:
Examples:
|
INDEXOF |
Returns the character/string index from a given string or if it does not exist returns -1. The function uses three arguments:
Arg3 (optional) offset at which the search should start (0 by default). Examples:
|
Functions which operate on dates accept string formatted with the following requirements:
- The date string must be exactly 8 symbols long.
- The first 4 symbols are reserved for the year.
- The next 2 symbols are reserved for the month - [1 - 12].
- The last 2 symbols are reserved for the day - [1 - 31].
Example date string: '2 June 2014' must be formatted as "20140602".
All date functions work correctly with leap years.
DATEDIFFDAYS |
Calculates the difference between two dates in days. The function use two arguments.
Example:
|
DATEDIFFMONTHS |
Same as DATEDIFFDAYS, but the difference is calculated in months. |
DATEDIFFYEARS |
Same as DATEDIFFDAYS, but the difference is calculated in years. |
DATEEQUAL |
Tests if two dates are equal. The function use two arguments.
Example:
|
DATEGREATER |
Tests if the date from first argument is greater than the date from second argument. The function use two arguments.
Example:
|
DATELESS |
Tests if the date from first argument is less than the date from second argument. The function use two arguments.
Example:
|
MAKEDATE |
Creates date string from given year, month and day of month. The function use three arguments:
Example:
|
DATEADDDAYS |
Adds number of days to the specified date and returns the new date. The function use two arguments:
Example:
|
TODAY |
Creates a date initialized with the current day. |
MAKEARRAY |
Creates an array of the parameters. If this function is used for a field with DynamicList flag set then the result will be visible in the field’s dropdown. It also can be called with the alias "A". Example:
|
JOIN |
Joins the elements in the specified array into single string using the specified separator. The function uses two arguments:
Example:
|
ARRGET |
Returns the element at index. The function uses two arguments:
Example:
|
ARRCOUNT |
Returns the size of array. The function uses one argument:
Example:
|
ARRCONTAINS |
Returns true if the given element exist and false if does not exist. The function uses two arguments:
Example:
|
ARRINDEXOF |
Returns the index of a given element in an array or if does not exist -1. The function uses three arguments:
Example:
|
ARRJOIN |
Appends one or more elements to the specified array. It can also join two or more arrays. The function uses all arguments:
Example:
|
ARRUNIQUE |
Returns an array of numbers with the unique values of other array of numbers. The function uses one argument:
Example:
|
ARRAVERAGE |
Returns the average value of an array of numbers. The function uses one argument:
Example:
|
ARRMAX |
Returns the maximum value of an array of numbers. The function uses one argument:
Example:
|
ARRMIN |
Returns the minimum value of an array of numbers. The function uses one argument:
Example:
|
FOR |
Iteratively executes an operation. The function uses five arguments:
Example:
|
Currently, only USA addresses are supported.
ADDRGETSTATE |
Returns state from an address. The argument must be a string. The function uses one argument.
Example: For the example we will assume that FT_ADDRESS contains:"PO BOX 24902 El Paso, TX 79914"
|
ADDRGETCITY |
Returns city from address. The argument must be a string. The function uses one argument.
For the example we will assume that FT_ADDRESS contains:"PO BOX 24902 El Paso, TX 79914".
|
ADDRGETZIP |
Returns Zip code from an address. The argument must be a string. The function uses one argument.
For the example we will assume that FT_ADDRESS contains:"PO BOX 24902 El Paso, TX 79914".
|
ADDRGETSTREET |
Returns street from an address. The argument must be a string. The function uses one argument.
For the example we will assume that: FT_ADDRESS contains "PO BOX 24902 El Paso, TX 79914"
FT_ADDRESS contains "Baker Str. 25, El Paso, TX 79914"
|
SQLEXEC |
Executes query strings and returns the result as an array. If a field with that formula has the DynamicList flag set the result is filled in the field’s dropdown list. Use the STRJOIN function to create advanced queries which use data from other fields. The function uses two arguments:
Example:
|
DBTYPE |
Usable only with SQL Integration. It returns string value that shows what database source is set for importing from in SQL integration. Example: Your import string is like: Driver={SQL Server};Server=myServerAddress;Database=myDataBase;
Also it can be used like this: IF ( STRCMP(DBTYPE(),"MSSqlServer"), SQLEXEC("SELECT col1 FROM table1"), SQLEXEC("SELECT col1 FROM table2") ) This formula will execute the second SQLEXEC statement if you are using MSSqlServer for importing data, and it will execute the first SQLEXEC statement if you are using other database source for importing data. |
ERPEXEC |
Alias for SQLEXEC |
PROC |
Used for sequential evaluation of one or more arguments. The function uses all given arguments: Can be used for creating a procedure of one or more actions. It evaluates all arguments in the given order and returns the result of the last one. Example:
|
DEFPROC |
Defines dynamic procedures which can later be invoked like a normal function. Always returns empty result. The function uses three arguments:
Example:
|
ARGS |
Defines arguments for dynamic procedure. The arguments are defined only in the scope of the dynamic procedure. The function uses all given arguments: Example:
|
VARGET |
Returns cell value of a column. The function uses two arguments:
Example:
|
VARCOUNT |
Returns number of fields. The function uses two arguments:
Examples:
|
VARARRAY |
Returns array, containing fields. The function uses two arguments:
Example:
|
VAREXISTS |
Tests if a given variable exists and can be retrieved using variable lookup. The function uses one argument:
Example:
|
@variable |
You can use variables to store result from functions and constants and later use them without having to evaluate again.Variables are declared with"@" symbol in front of its name(@var1) and initialized with "="and you have to declare it where you use for first time the value that you want stored. @variable =2 Examples:
The second and the third argument of the IF() function are evaluated separately and only one of them is evaluated depending on the condition value. Declaring variable in the second argument won’t take effect in the third and vice versa. |
PLATFORM |
Returns the parent process architecture The function has no arguments Example: PLATFORM() -> x86 |
ERROR |
Sets the tooltip error to the specified message The function uses one argument:
Example:
|
EVALEXPR |
Returns the evaluated expression - Macros. The function uses one argument:
Example:
|
PAGENUM |
Returns the page from which the value is. The function uses one argument:
Example:
|
To define a new line in reqex formulas, in order to read multi-line data, you can use \r?\n.
In automatically populated text, lines are separated by \r\n, a carriage return followed by a new line. For manually added text, lines are separated by the new line character \n. Using \r?\n in regex formulas ensures that all line breaks are captured. For example, ([A-Z]+[\r?\n ]+[A-Z]+[\r?\n ]*)([\r?\n ]*[A-Z]+)*.
REGEXMATCH |
Boolean function which returns a value of 1 if the specified regular expression matches the entire string. The function uses two arguments:
Example:
|
REGEXSEARCH |
Returns an array of the matches, including capture groups, or an empty array if no match occurs. The function uses two arguments:
Example:
|
REGEXSEARCHPOS |
Returns an array of the positions of all matches, including capture groups, or an empty array if no match occurs. The function uses two arguments:
Example:
|
REGEXREPLACE |
Replaces matches with specific regex and returns replaced string. The function uses three arguments:
Example:
|
REGEXSPLIT |
Returns an array of the text between the matches, or an array with one element containing the entire string. The function uses three arguments:
Example:
|
REGEXSPLITGET |
Returns an array of the text between the matches, or an array with one element containing the entire string. The function uses four arguments:
Example:
|