SmartSoft Invoices - Online Help

Online documentation

Contact Us




Overview
Quick Start
10 Tips for Best Results
Using SmartSoft Invoices
User Interface
Main Window
Image View Panel
Form View Panel
Session View Panel
Loading Documents
From a Folder
From a Scanner
From an Email Account
From a Watched Folder
From Shared Watched Folder
Training the System
Validation
Data Verification
Verification
Batch Verification
Auto Complete
Multi-page Documents
Automatic Detection
Manual Separation
Blank Pages
Selecting ERP
Software settings
General
Interface
Scanner
Macros
Exporting
Exporting to CSV
Exporting to PDF
Exporting to XML
Exporting to TIFF
Exporting to SQL Database
Exporting to QuickBooks
Exporting to SAP
Exporting to MS Dynamics
Archiving
Customizing the Form
Form Template Designer
Adding New Fields
Adding New Line Items
Mapping
Validation Formula Language
Lists
Importing Lists
Importing Custom Lists
Synchronous Lists
Selective Processing
Migrating to a new computer
Migrating document templates
Migrating custom forms
Scan Quality
Client/Server Deployment Option
Deployment
Input Station
Verify Station
Licensing
Support

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: "-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 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, date-time, 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, 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
  • 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:

    FunctionDescription
    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

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

    FunctionDescription
    DATEDIFFDAYSCalculates 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

    DATEDIFFMONTHSSame as DATEDIFFDAYS, but the difference is calculated in months.
    DATEDIFFYEARSSame as DATEDIFFDAYS, but the difference is calculated in years.
    DATEEQUALTests if two dates are equal.

    Sample:
    DATEEQUAL("20140325", "20140125") -> 0 (FALSE)

    DATEGREATERTests 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), "-") ->"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 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.