Formulas
Overview
- Formulas can be used to manipulate or transform the data on the fly. In this way you can adapt the data to match the destination system at the same time as transferring the data. They are also called data transformation functions, expression formulas or scripting expressions.
- A formula can only be used in the Source Field in either the Table Link or in the Field List.
- To tell the system that you want this field to be a formula, you have to start the line with two #-signs (hash). For example: ##CURRENTDATE().
- In formulas, single quote '' is used to enclose a text string - like in 'this is a text string'. And double quote "" is used to enclose field names (only needed if the field name contains blanks or special characters) - like in "Address 2".
Formula Function and Operator
ABS
AFTER
BEFORE
BEFORELAST
BLANKVALUE
CASE
CONVERT
COPY
CURRENTDATE
CURRENTDATETIME
CURRENTTIME
DBLOOKUP
DEL
EQUALS
ERROR
FORMAT
GETBITS
GETBIT
GETFIELDVALUE
IF
IMPLODE
ISBLANK
ISEMAIL
ISPHONE
LAST
LENGTH
LOCALTIME
LSLOOKUP
MESSAGE
MIDDLE
NULL
POS
POSTDEL
PREPEND
READFILE
SKIPFIELD
SPLIT
TODATE
TODATETIME
TOTIME
TOFLOAT
TOINTEGER
UPPERCASE