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

Videos

Formulas

This video looks at formulas, the language used, basic functions, and more.

Split multi-line fields

This video explains how you can split a multi-line field by row.