
Using SmartSoft Invoices


User Interface


Loading Documents


Data Verification


Multipage Documents


Software settings


Exporting


Customizing the Form


Lists


Migrating to a new computer


Client/Server Deployment Option



Formula Language
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 (two arguments) function: "a + b"



Subtraction Binary or unary (one argument) function: "ab"

*

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 letter and can contain alphanumeric characters and underscore ("_").
Additionally colon (":") and diesis ("#") 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 can be used to specify formulas for table columns. 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 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.
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 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 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.
ERROR  For specifying error conditions. Example formula for grand total:
"IF(VAREXISTS(FT_TAX), FT_SUBTOTAL + FT_TAX, ERROR)".
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 we cannot calculate the grand total field 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, datetime, etc.
Function names are case insensitive  therefore the following names are equivalent: "varexists", "VarExists", "VAREXISTS".
Functions can be combined with variables also for maximum flexibility each function 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 is Integer (optional) sets the precision.By default it is 0.
Samples:
ROUND(3.14) > 3
ROUND(3.1415, 2) > 3.14
ROUND(3.1415, 3) > 3.142

VARGET

Function that returns cell value of a column.
The function uses two arguments:
Arg1 is the ID of the column
Arg2 is Integer  the index (zero based) of the row the desired cell is
Samples:
VARGET(L(FT_PRODUCT_DESC), 1) > returns the value in the second cell of the FT_PRODUCT_DESC column

VARCOUNT

Function that returns number of 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
Samples:
VARCOUNT(L(FT_PRODUCT_DESC), 1) > returns the number of all non empty FT_PRODUCT_DESC fields (which equals non empty row count)
VARCOUNT(L(FT_PRODUCT_DESC)) > returns the number of all FT_PRODUCT_DESC fields including the empty ones (which equals the total row count)

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 conjunction (aka logical and): "AND(1,2,0)" > 0 (FALSE) 
OR 
Logical disjunction (aka logical or): "OR(1,2,0)" > 1 (TRUE) 
NOT 
Logical negation (aka 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 given variable exists and can be retrieved using variable lookup "VAREXISTS(L(VAR1))"

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, 12)" > 3 (the result of 1+2)
"IF(NOT(LESS(1, 2)), 1+2, 12)" > 1 (the result of 12)
"IF(NOT(VAREXISTS(FT_TAX)), FT_SUBTOTAL * 0.2, SELF)"

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 oneargument 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 ("ABCDEFGH", "", 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("ABCDEFGH", "", 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("ABCDEFGH", "DEF") >1 (TRUE) STRCONTAINS("ABCDEFGH", "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. Also supports array. If the number of elements is greater than the number of elements in arg3 the last element in arg3 is used to match.
arg3 is the string to replace the substrings. Also supports array. If the number of elements is greater than the number of elements in arg2 the redundant elements are ignored.
Samples:
STRREPLACE ("Bad luck!", "Bad", "Good") >"Good luck!"
STRREPLACE ("Far With Functions", "F", "J") >"Jar With Junctions"
STRREPLACE ("The quick brown fox jumps over the lazy dog!", A("brown", "fox", "jumps over", "dog"), A("red", "squirrel", "eats", "hazelnut")) >"The quick red squirrel eats the lazy hazelnut!"

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:
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. It also can be called with the alias "A".
Sample:
MAKEARRAY(1, 2, 3, 5, 8, 13) >[1, 2, 3, 5, 8, 13]
A(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), "") >"123"

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 false
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):
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:
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.
Arg1 is the actuall query that will be executed
Arg2 is Bool and optional  if true the result of the query will be cached (up to 5 queries are cached) no caching otherwize. By default the value is true
Sample:
SQLEXEC("SELECT VendorName FROM Vendors") > ["Vendor 1", "Vendor 2"…] (result cached)
SQLEXEC("SELECT VendorName FROM Vendors", 1) > ["Vendor 1", "Vendor 2"…] (result cached)
SQLEXEC("SELECT VendorName FROM Vendors", 0) > ["Vendor 1", "Vendor 2"…] (result not cached)

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 
Variable functions
List of supported variable functions:
@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.

Copyright ©, SmartSoft. All rights reserved.
