Formula Language

The Validation Formula Language is used to validate or automatically populate fields based on values of other fields.

Validation
For example the Total amount in an invoice should be validated against the values of the Subtotal and the Tax amount. The formula would be FT_TOTAL = FT_SUBTOTAL + FT_TAX. The software will automatically validate the extracted data to make sure it is correct. Invalid fields are marked with a red frame to let you easily see and fix them. Hover over the field to see a tool tip with some more information about the error. The software will not let you export the data until all the data is valid.
Calculation
We might want to automatically calculate and populate a field’s value if it is not present in a document, e.g. FT_SUBTOTAL = FT_TOTAL - FT_TAX

To enter a formula for a field open the Form Definition Screen and put the formula in the Formula column. To refer to a field in a formula use the field’s ID. Field IDs are usually in all caps and start with FD_ like FD_TOTAL but this is just a convention, you can use any character sequence.

../_images/form-designer.png

Arithmetic Operators

The Validation Formula Language (VFL) of SmartSoft Invoices supports basic arithmetic operators. Complex validation rules can be built by combining these simple operators with predefined or custom functions.

List of supported arithmetic operators:

Operator Description
+ 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 the underscore symbol _.

Colon symbol : and pound symbol # can be used for creating parametric variables.

Sample variable name: “FT_SUBTOTAL”.

Sample formula with variables: “FT_SUBTOTAL + FT_TAX”, “FT_GRANDTOTAL*0.20 + 1000”, etc.

Parametric variables

Parametric variables are used to apply one formula to all the cells in a table column. For each row the parameter will be replaced with the row’s index.

Sample parametric variable name: FT_LINE_TOTAL:#

Sample 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 line 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 uppercase and/or lowercase letters there are no restrictions, but keep in mind that during variable lookup the names are case sensitive. Most samples in this documentation use capital letters preceded by _FT, like FT_TAX, but this is just a convention.

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(FT_TAX), FT_SUBTOTAL + FT_TAX, SELF). The formula above can be interpreted like this: If there is a value in the FT_TAX field then the Grand Total field is populated with FT_SUBTOTAL + FT_TAX, if there is no value in the FT_TAX field then the Grand Total will not be calculated - its original value (SELF) will be used for the field.

ERROR - For specifying error conditions. Example formula for the Grand Total field: IF(VAREXISTS(FT_TAX), FT_SUBTOTAL + FT_TAX, ERROR). The formula above can be interpreted like this: If there is a value in the FT_TAX field then the Grand Total field is populated with FT_SUBTOTAL + FT_TAX, if there is no value in the FT_TAX field then we cannot calculate the Grand Total field’s value and the result of the formula must be error (fail in evaluation).

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 for maximum flexibility functions can be embedded in each other, for example: MIN(MIN(6,5),MAX(1,2),SUM(FT_TAX,3,SUM(2,3,5))).

The sample 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)
       )
     )

Arithmetic functions

List of supported arithmetic functions:

Function Description
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:
Arg1 is the number to be rounded
Arg2 (optional) sets the precision. Integer. By default it is 0.
Samples:
ROUND(3.14) -> 3
ROUND(3.1415, 2) -> 3.14
ROUND(3.1415, 3) -> 3.142
VARCOUNT
Function that returns number of fields.
The function accepts two arguments:
Arg1 is the field ID of a table column
Arg2 is a Boolean value indicating whether empty fields should be counted (optional): 1 - empty rows will be skipped while counting; 0 (default) - empty rows will also be counted
Samples:
VARCOUNT(L(FT_PRODUCT_DESC), 1) -> returns the number of all non empty FT_PRODUCT_DESC fields (which is equal to the number of non-empty rows)
VARCOUNT(L(FT_PRODUCT_DESC)) -> returns the number of all FT_PRODUCT_DESC fields including any empty ones (which is equal to the total number of rows)

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

Logical functions always return:

  • 0 - logical FALSE value
  • 1 - logical TRUE value

List of supported logical functions:

Function Description
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)
VAREXISTS Tests if a given variable exists and can be retrieved using variable lookup VAREXISTS(VAR1). Used to test whether a field has been populated or is empty.
IF Conditional operator function with following argument list: IF(logical_test, value_if_true, value_if_false) 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. Samples: IF(LESS(1, 2), 1+2, 1-2) -> 3 (the result of 1+2) IF(NOT(LESS(1, 2)), 1+2, 1-2) -> -1 (the result of 1-2) IF(NOT(VAREXISTS(FT_TAX)), FT_SUBTOTAL * 0.2, SELF)

All logical functions can be combined with variables and other functions.

Number of arguments:

  • 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

String functions

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”.

List of supported string functions:

Function Description
STRCMP
String comparison function with case sensitive logic.
The function requires two string arguments: arg1 and arg2. 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. Samples: STRCMP("apples", "oranges") -> -1 STRCMP("food", "bar") -> 1 STRCMP("apples", "apples") -> 0
STRICMP Same as the function above, but the string comparison is case insensitive.
STRJOIN
Joins all string arguments in a single string.
Samples: STRJOIN ("Hip", "po", "pot", "amus") -> "Hippopotamus"
SUBSTR
Returns a substring from a given string.

arg1 is the input string arg2 is the zero based starting index arg3 (optional) is the number of characters to be extracted. Samples:

SUBSTR("ABCDEFGH", 2, 4) -> "CDEF" SUBSTR("ABCDEFGH", 2) -> "CDEFGH"

STRLEFT  
STRRIGHT
Returns the starting right or left symbols from a given string.

arg1 is the input string arg2 is the number of characters to be extracted. Samples:

STRLEFT("ABCDEFGH", 3) -> "ABC"

SPLITGET
Splits a string by a separator to substrings and returns one of them.

arg1 is the input string arg2 is a set of separator characters (can be more than one) arg3 is the zero based index of the substring to return Samples:

SPLITGET ("ABC-DEF-GH", "-", 1) -> "DEF"

SPLITGETREV
Splits a string by a separator to substrings and returns one of them in reverse order.

arg1 is the input string arg2 is a set of separator characters (can be more than one) arg3 is the backward zero based index of the substring to return Samples:

SPLITGETREV("ABC-DEF-GH", "-", 0) -> "GH"

STRCONTAINS
Returns 1 if the input string contains the specified substring. Otherwise returns 0.

arg1 is the input string arg2 is the substring to look for Samples:

STRCONTAINS("ABC-DEF-GH", "-DEF") -> 1 (TRUE) STRCONTAINS("ABC-DEF-GH", "-AFB") -> 0 (FALSE)

STRLEN
Returns the number of characters in the specified input string.

arg1 is the input string. Samples:

STRLEN("Apple") -> 5

STRREPLACE
Replaces all occurrences of a substring in the inpit string with a new one and returns the modified string.

arg1 is the input string. arg2 is the string to search for. arg3 is the string to replace the substrings. Samples:

STRREPLACE ("Bad luck!", "Bad", "Good") -> "Good luck!" STRREPLACE ("Far With Functions", "F", "J") -> "Jar With Junctions"

STRUPPER
Returns an uppercase version of the input string.

Samples:

STRUPPER ("Fast Fox") -> "FAST FOX"

STRLOWER
Returns an lowercase version of the input string.
Samples:
STRUPPER ("Fast Fox") -> "fast fox"
STRTRIM
Removes any starting or trailing spaces from the input string and returns it.

Samples:

STRTRIM(" Fast Fox ") -> "Fast Fox"

SPRINTF
Formats a string with the data specified by the arguments.

arg1 is the format specifier which uses the printf syntax of the C language. Samples:

SPRINTF("The %s %d is bigger than %f", "number", 42, 3.14) -> "The number 42 is bigger than 3.14"

Date functions

Functions that 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]
  • Sample date string: 2 June 2014 must be formatted as 20140602.

All date functions work correctly with leap years.

List of supported logical functions:

Function Description
DATEDIFFDAYS
Calculates the difference between two dates in days.
Basically this function subtracts the second date from the first date and returns the span between two dates in number of days. Sample: DATEDIFFFAYS(“20140325”, “20140125”) -> 59
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.
Sample: DATEEQUAL(“20140325”, “20140125”) -> 0 (FALSE)
DATEGREATER
Tests if the date from first argument is greater than the date from second argument.
Sample: DATEGREATER(“20140325”, “20140125”) -> 1 (TRUE)
DATELESS
Tests if the date from first argument is less than the date from second argument.
Sample: DATELESS(“20140325”, “20140125”) -> 0 (FALSE)
MAKEDATE
Creates date string from given year, month and day of month

arg1 the year arg2 the one based month number arg3 the one based day of month Samples:

MAKEDATE (2015, 11, 26) -> “20151126”

DATEADDDAYS
Adds number of days to the specified date and returns the new date.
arg1 is the starting date arg2 is the number of days to add (can be negative too) Sample: DATEADDDAYS (“20151126”, 3) -> “20151129”
TODAY Creates a date initialized with the current day.

Array functions

List of supported array functions:

Function Description
MAKEARRAY
Creates an array of the function parameters. If this function is used for a field with DynamicList flag set then the result will be visible in the field’s dropdown.

Sample:

MAKEARRAY(1, 2, 3, 5, 8, 13) ->[1, 2, 3, 5, 8, 13]

SPLIT
Splits a string using the specified delimiter and returns an array.

arg1 - a string to split arg2 - one or more separator characters Sample:

SPLIT(“This is a test”, ” “) -> [“This”, “is”, “a”, “test”]

JOIN
Joins the elements in the specified array into single string using the specified separator.

arg1 - An array to join arg2 - A separator string. Can be empty, one or more than one characters. Sample:

JOIN(MAKEARRAY(1,2,3), “-“) ->”1-2-3”

ARRGET
Returns the element at index.

ARRGET(array, index) Sample:

ARRGET(MAKEARRAY(1,2,3),2) ->3

ARRCOUNT
Returns the size of array.

ARRCOUNT(array) Sample:

ARRCOUNT(MAKEARRAY(1,2)) - > 2

ARRCONTAINS
Returns true if the given element exist and false if does not exist.

ARRCONTAINS(array, element) Sample:

ARRCONTAINS(MAKEARRAY(“Go”, “Back”),”Back”) - 1(True) ARRCONTAINS(MAKEARRAY(“Go”, “Back”),”BACK”) - 0(False) ARRCONTAINS(MAKEARRAY(“Go”, “Back”),”Return”) - 0(False)

ARRINDEXOF
Returns the index of a given element or if does not exist -1.

ARRINDEXOF(array, element) Sample:

ARRINDEXOF(MAKEARRAY(“Go”,”GO”,”BACK”,”Back”),”Go”) - > 0 ARRINDEXOF(MAKEARRAY(“Go”,”GO”,”BACK”, “Back”),”Back”) - > 3 ARRINDEXOF(MAKEARRAY(“Go”,”GO”,”BACK”,”Back”),”Goes”) - > -1

ARRUNIQUE
Returns an array of numbers with the unique values of other array of numbers.

ARRUNIQUE(array) Sample:

ARRUNIQUE(MAKEARRAY(1,2,3,4,5,2,3,4,5,3,4,5,6,4,5,6,7)) - > [1, 2, 3, 4, 5, 6, 7]

ARRAVERAGE
Returns the average value of an array of numbers.

ARRAVERAGE(array) Sample:

ARRAVERAGE(MAKEARRAY(2.71, 3.14, 0.707)) - >2.185667

ARRMAX
Returns the maximum value of an array of numbers.

ARRMAX(array) Sample:

ARRMAX(MAKEARRAY(2.71, 3.14, 0.707)) - >3.14

ARRMIN
Returns the minimum value of an array of numbers.

ARRMIN(array) Sample:

ARRMIN(MAKEARRAY(2.71, 3.14, 0.707)) - >0.707

VARARRAY
Returns array, containing fields.

The function uses two arguments: Arg1 is the field ID of the table column Arg2 is Bool (optional) 1 - empty rows will be skipped while counting; 0 (default) - empty rows will also be counted

Sample:

VARARRAY(L(FT_PRODUCT_DESC), 1) -> returns an array containing all non empty FT_PRODUCT_DESC fields in the same order as in the table. VARARRAY (L(FT_PRODUCT_DESC)) -> returns an array containing all FT_PRODUCT_DESC fields including the empty ones in the same order as in the table.

PROC
Function 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.

Sample:

PROC(@sum=(2+3), @sum=(@sum*@sum)) -> 25

FOR

Function for iterative execution of an operation. The function uses five arguments: Arg1 is the initialization block where the counter is initialized. Multiple variables initialization can be done with function PROC() Arg2 is Bool - the loop is repeated until this condition becomes true Arg3 is the operation which will be executed on every iteration Arg4 is used to increment the iterator Arg5 is the returned variable after all iterations are done

Sample:

FOR(PROC(@it=0, @fact=1), LESS(@it, 10), @fact=(@fact*(@it+1)), @it=(@it+1), @fact) -> returns factorial of 10

Address functions

List of supported functions for extraction of text from address(currently we support only USA addresses):

Function Description
ADDRGETSTATE
Returns state from an address. The argument must be a string.

ADDRGETSTATE(address) Samples:

For the example we will assume that FT_ADDRESS contains : “PO BOX 24902 El Paso, TX 79914” ADDRGETSTATE(FT_ADDRESS) -> TX

ADDRGETCITY
Returns city from address. The argument must be a string.
ADDRGETCITY(address) For the example we will assume that FT_ADDRESS contains : “PO BOX 24902 El Paso, TX 79914” ADDRGETCITY(FT_ADDRESS) -> El Paso
ADDRGETZIP
Returns Zip code from an address. The argument must be a string.
ADDRGETZIP(address) For the example we will assume that FT_ADDRESS contains : “PO BOX 24902 El Paso, TX 79914” ADDRGETZIP(FT_ADDRESS) -> 79914
ADDRGETSTREET
Returns street from an address. The argument must be a string.
ADDRGETSTREET(address) For the example we will assume that: FT_ADDRESS contains “PO BOX 24902 El Paso, TX 79914” ADDRGETSTREET(FT_ADDRESS) ->PO BOX 24902 FT_ADDRESS contains “Baker Str. 25, El Paso, TX 79914” ADDRGETSTREET(FT_ADDRESS) ->Baker Str. 25

ERP functions

List of supported ERP functions:

Function Description
SQLEXEC
Executes query string 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.

Sample:

SQLEXEC(“SELECT VendorName FROM Vendors”) -> [“Vendor 1”, “Vendor 2”, …]

DBTYPE
This function is usable only with SQL Integration.

It returns string value that shows what database source is set for importing from in SQL integration. Sample:

Your import string is like: Driver={SQL Server};Server=myServerAddress;Database=myDataBase; DBTYPE()->MSSqlServer

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

Important

For dynamic lists for work properly AutoCalc, Calculable and DynamicList flags must be raised for that particular :ref:`field or column<flags_fields>.

Variable functions

List of supported variable functions:

Function Description
@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 Samples:

STRCMP(@var1= FT_SUBTOTAL,”123.4”) IF(STRICMP(@var2= SQLEXEC(“SELECT FIRSTNAME ……..”),”Thomas”),@var2,”“) @var3= MAKEARRAY(1,2,3,4)

Note: 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.

Macros

Macros can be used in formulas in Verify. Click here to see supported macros in Verify.

Function Description
EVALEX

Displays the value of one or more applied macros. Auto-calc flag must be applied for such fields. An example below shows a formula that displays the Batch Description and the document GUID value stored in the data base.

EVALEXPR("$(BatchDesc)-$(DocUuid)")