Variable Name

Resulting Value


[folder]

Full folder of the path of the current file

Importing 'C:\import\folder1\doc.tif' returns 'C:\import\folder1'

[foldername#]

Segment of the folder structure to be used. 

If importing folder 'C:\import' and the importer finds 'C:\import\folder1\doc.tif' you can use [foldername0] for the value 'folder1' and [foldername1] for the value 'import' and [foldername2] for the value 'C:'

[file]

The full path and file name of the imported file.

Importing 'C:\import\folder1\doc.tif' returns 'C:\import\folder1\doc.tif'

[filename]

The file name of the imported file.

Importing 'C:\import\folder1\doc.tif' returns 'doc.tif'

[filenameonly]

The file name of the imported file without the extension

Importing 'C:\import\folder1\doc.tif' returns 'doc'

[extension]

The extension of the imported file.

Importing 'C:\import\folder1\doc.tif' returns 'tif'

[FILEDATE]

Date of the imported file's last write time

1/20/2018

[FILETIME]

Time of the imported file's last write time

10:33 AM

[FILEDATETIME]

Date and time of the imported file's last write time

1/20/2018 10:33 AM

[FILECREATEDATE]

Date of the imported file's date of creation

1/23/2018

[FILECREATETIME]

Time of the imported file's date of creation

2:45 PM

[FILECREATEDATETIME]

Date and time of the imported file's date of creation

1/23/2018 2:45 PM

[FILESIZE]

Size of imported file in bytes

123456789

[FILESIZEKB]

Size of imported file in kilobytes

120563

[FILESIZEMB]

Size of imported file in megabytes

117

[CSVLINE]

When importing from a CSV this will hold the entire line of the CSV file that is being processed.


[DATE]

The current date and time. Standard .NET date formatting can also be applied as in the following examples.

2/16/2018

[DATE(#)]

Current date + or - the numeric value of days

2/17/2018

[DATE(#,y)]

Current date + or - the numeric value of years

2/16/2019

[DATE(#,y,d)]

Current date + or - the numeric value of years and formatted with the 'd 'string. See the full list of formatting options below.

2/16/2019

[DATE(#,y,d,SUN)]

Current Sunday following + or - the numeric value of years and formatted with the 'd 'string. See the full list of formatting options below. See Day Of Week options below.

2/16/2019

[YEAR]

Current 4-digit year

2018

[MONTH]

Current 2-digit month

02

[MONTHNAME]

Current month name

February

[DAY]

Current 2-digit day number

16

[DATETIME]

Equivalent to [DATE(0,g)]

2/16/2018 2:45 PM

[TIME]

Equivalent to [DATE(0,t)]

2:45 PM

[DATEADD(DATE|Y|M|D|H|m|s)]

Add to given date the number of Years, Months, Days, Hours minutes and Seconds provided. You may omit any trailing zero values but must include any leading zeros. 


[DATEADD(1/1/2018|1)] = 1/1/2019

[DATEADD(1/1/2018|0|3)] = 4/1/2018

[ISDATE(Val|YesVal|NoVal)]

Checks to see if the invoice VAL is a date. If it is a date then the YesVal value is returned. If not then the NoVal is returned.

[ISDATE(1/1/2018|YEP|NOPE)] = YEP


[ISDATE(1/1/2018|1/1/2018|)] = 1/1/2018


[ISDATE(BadDate|YEP|NOPE)] = NOPE


[DAY] / [DOM]


New date can be sent in

Current day of month 

[DAY] = 12


[DAY(1/3/2016)] = 3


[DOW]


New date can be sent in

Day of week 


Values are 0 - 6

0 = Sunday

1 = Monday

2 = Tuesday

3 = Wednesday

4 = Thursday

5 = Friday

6 = Saturday


[DOW] = 3


[DAY(1/3/2016)] = 0

[DOY]


New date can be sent in

Day of year

[DOY] = 73


[DOY(1/3/2016)] = 3

[HOUR]

Hour of time in 24-hour format

[HOUR] = 17 (for 5:23:15 PM)


[HOUR(1/1/2001 11:23:15 PM)] = 23

[MINUTE]

Minute of time

[MINUTE] = 13 (for 5:13:15 PM)


[MINUTE(1/1/2001 11:13:15 PM)] = 13

[SECOND]

Second of time

[SECOND] = 15 (for 5:13:15 PM)


[SECOND(1/1/2001 11:13:15 PM)] = 15

[IF(val1|operator|val2|true|false)]


Exception for BETWEEN (B):

[IF(val1|B|val2|val3|true|false)]


Compares val1 to val2 using the operator and if true returns the true value else returns the false value. 


for BETWEEN (B) operations you must supply 6 parameters with the 4th one being the TO value for the FROM (3rd) and TO (4th) combination.


Example 1: [IF(1|=|2|YES|NO)] will return NO

 

Example 2: [IF(12=|2|YES|NO)] will return YES.


Example 3: [IF(1|B|2|3|YES|NO)] will return NO 


Example 4: [IF(2|B|1|3|YES|NO)] will return YES


[IFNULL]

[ISNULL]

Returns value is not null but if null returns alternate value

[IFNULL([VAL]|ABC)] - Returns ABC is [VAL] is null else it returns [VAL]'s value.

[CASE(val|var1|res1|var2|res2|else)]

Goes through the list of vars and returns the associated res when a var is matched. If no vars are matched, it returns the 'else' value or empty if no 'else' is specified.


The var/res pairs can be repeated as many times as needed.


This is similar to a SELECT CASE in VB or SWITCH statement in C# or JavaScript.

[CASE(X|A|1|x|2|z|3|0)] = 2 (matched on x)


[CASE(F|A|1|x|2|z|3|0)] = 0 (no match so else is used)


[CASE(test|test|yes|testx|no)] = yes


[ABS(#)]

Computes the Absolute value of the number

[ABS(-13)] = 13

[DIFF(val1|val2|period)]

Computes the difference between two values. For numbers the third parameter is ignored.


Possible date period values: 

Y = Years as compared by year number 


[DIFF(1/1/2009|12/31/2009|Y)] = 0 

[DIFF(12/31/2009|1/1/2010|Y)] = 1

[DIFF(6/1/2009|5/30/2010|Y)] = 1

[DIFF(6/1/2009|6/1/2010|Y)] = 1

y = Years as compared by # of days

[DIFF(1/1/2009|12/31/2009|y)] = 0

[DIFF(12/31/2009|1/1/2010|y)] = 0

[DIFF(6/1/2009|5/30/2010|y)] = 0

[DIFF(6/1/2009|6/1/2010|y)] = 1

M = Months

D and d = Days

H and h = Hours

m = Minutes

[DIFF(1|3)] = 2 

[DIFF(3|1)] = -[DIFF(1/1/2014|1/4/2017|Y)] = 3 (3 years different). See Period options below.

[SUM(val1|val2|VAL3|etc)]

Returns SUM of all values

[SUM(1|6|5|3)] returns 15

[MIN(val1|val2|VAL3|etc)]

Returns MIN of all values 

[MIN(1|6|5|3)] returns 1

[MAX(val1|val2|VAL3|etc)]

Returns MAX of all values

[MAX(1|6|5|3)] returns 6

[COUNT(val1|val2|VAL3|etc)]

Returns COUNT of all values

[COUNT(1|6|5|3)] returns 4

[AVG(val1|val2|VAL3|etc)]

Returns AVERAGE of all values

[AVG(1|6|5|3)] returns 3.75

[RANDOM]

[RND]

Returns random numbers

[RANDOM] returns number between 1 and 100


[RANDOM(0|10)] returns number between 1 and 10

[ALPHANUM(Value|ReplaceChar)]

Replaces non-alphanumeric values with the value supplied. If ReplaceChar blank then the non-conforming characters are removed.

[ALPHANUM(a,b12,3)] = ab123


[ALPHANUM(a,b12,3|x)] = axb12x3


[ALPHA(Value|ReplaceChar)]

Replaces non-alpha values with the value supplied. If ReplaceChar blank then the non-conforming characters are removed.

[ALPHA(a,b12,3)] = ab


[ALPHA(a,b12,3|x)] = axbxxxx


[NUM(Value|ReplaceChar)]

Replaces non-numeric values with the value supplied. If ReplaceChar blank then the non-conforming characters are removed.

[NUM(a,b12,3)] = 123


[NUM(a,b12,3|x)] = xxx12x3


[REPLACE(string|value1|value2|Start|Len|IgnoreCase)]

Replaces one value with another value


Start and Len are used to specify the section of the string to replace.


Set IgnoreCase to 'True' to make sure the replacement is not case sensitive

[REPLACE(test|t|x)] returns "xesx"


[REPLACE(test|t|x|0|1)] returns "xest"

[REPLACE(test|t|x|3|1)] returns "tesx"


[REPLACE(test|T|X)] returns "test"


[REPLACE(test|T|X|||true)] returns "XesX"


[REPLACEEXT(string|from1|to1|from2|to2|...)]

Replaces several strings in one operation. Use as many from and to variables as needed. They work in pairs.

[REPLACEEXT(This is a test|this|That| is | was |test|chore)]

converts "This is a test" to "That was a chore"


[REMOVE(string|str1|str2...)]

Removes from "string" any text elements that come after.


This operation is not case sensitive

[REMOVE(Testing|ing)] = Test


[REMOVE(Testing 1, 2, 3|,)] = Testing 1 2 3


[REMOVE(Testing 1, 2; 3|,|;| )] = Testing123



[PAD], [PADRIGHT]

Pads string with another up to length. 

[PAD(ABC|5|x)] returns "ABCxx"

[PADLEFT]

Pads left side of string. 

[PADLEFT(ABC|5|x)] returns "xxABC"

[FORMAT]

Formats value based on its formatting rules according to .NET. Works on numeric and date formats.

 - Numeric

 - Date Formats

Numeric:

[FORMAT(12.3|c)] = $12.30


Date:

[FORMAT(1/31/2015|YY/DD)] = 15/01

[NUMELS(string|delimeter)]

Returns the number of elements that will be returned when using the SPLIT variable

[NUMELS[(this is a test| )] will return 4

[SPLIT(value|separator|index)]

Split "value" by the given "separator" and return element number "index".


Using a positive number for the index will return the element starting from the front. 


Using a negative number for the index will return the element starting from the end. 


Using 0 for the index will return the split as a JSON array.


[SPLIT[(this is a test| |2)] will return the text "is".


[SPLIT[(this is a test| |-1)] will return the text "test".


[SPLIT[(this is a test| |-3)] will return the text "is".


[SPLIT[(this is a test| |0)] will return the following array:

   ["this","is","a","test"]



[JOIN]

Joins collection of values. Works only on Record, Data and E-Form variable names. 

[JOIN(PO|;)] with a record that has 3 PO values of 12, 35 and 77 returns "12;35;77"

[STRUCT(value|start,len|start,len|start,len|...)]

Returns a string that is made up of pieces of an incoming string. Like SUBSTR but can processes multiple SUBSTR in one pass


value = the incoming string value to restructure


start = starting character (0-based) of the string to use for this portion


len = the number of characters to grab from the starting position. If you omit this portion then this will read to the end of the string.


start,len can be repeated as many times as necessary


[STRUCT(testing|0,2|5|2,3)] returns "tengsti"



[LEFT(value|#)]

[RIGHT(value|#)]

Returns the left or right # characters from value

[LEFT(ABCDEFG|2)] = AB


[RIGHT(ABCDEFG|2)] = FG

[SUBSTRING(value|startpos|length)]

Returns a string from inside of another string


startpos is 0-based so the first character is 0. If set to negative then this is the number of characters from the end to start.


length is optional. If you omit this then this will return everything starting from startpos

[SUBSTRING(ABCDEFG|0|1)] returns "AB"


[SUBSTRING(ABCDEFG|1|2)] returns "BC"


[SUBSTRING(ABCDEFG|4|3)] returns "EFG"


[SUBSTRING(ABCDEFG|2)] returns "CDEFG"


[SUBSTRING(ABCDEFG|-4)] returns "DEFG"


[SUBSTRING(ABCDEFG|-4|2)] returns "DE"


[LEN(value)]

Returns the number of characters in the value passed to it.

[LEN(testing)] = 7

[TRIM(value)]

Trims the white space from the front and end of string

[TRIM( test )] returns "test"

[TRIMRIGHT(value)]

Trims the white space from the end of string

[TRIMRIGHT( test )] returns " test"

[TRIMLEFT(value)]

Trims the white space from the front of string

[TRIMLEFT( test )] returns "test "

[TRIMFROM(value|from|reverse)]

Trims off the end of a string starting with the first occurrence of the "from" string. Set "reverse" = true to trim the front of the string instead of the end.

[TRIMFROM(test_123_abc|_)] returns "test"


[TRIMFROM(test_123_abc|_|true)] returns "123_abc"


[TRIMFROMEND(value|from|reverse)]

Trims off the end of a string starting with the last occurrence of the "from" string. Set "reverse" = true to trim the front of the string instead of the end.

[TRIMFROM(test_123_abc|_)] returns "test_123"


[TRIMFROM(test_123_abc|_|true)] returns "abc"


[CONDENSE()]

Condenses extra spaces, tabs, line feeds and carriage returns into single spaces

[CONDENSE(This   is  a  test)] returns "This is a test"


[CONDENSE(This

is

a  test)] returns "This is a test"


[LOWER]

Converts text to lower case

[LOWER(AbCdE)] = abcde

[UPPER]

Converts text to upper case

[UPPER(AbCdE)] = ABCDE

[PROPERCASE] / [TITLECASE]

Converts string to proper case or title case


Proper/Title Case is where the first letter in each word is capitalized and all others are lower

[PROPERCASE(john q public)] = John Q Public

[REPEAT]

Repeats the given string N times

[REPEAT(ABC|2)] = ABCABC

[REPEAT(*|10)] = **********


[NEWLINE(#)]

Returns a new line string. Can accept one parameter that is the number of lines to add. If omitted it assumes 1.


[NEWLINE()] - returns one line


[NEWLINE(4)] - returns 4 lines

[TAB(#)]

Returns a tab character. Can accept one parameter that is the number of tabs to add. If omitted it assumes 1.

[TAB()] - returns one tab


[TAB(2)] - returns 2 tabs

[CHAR(ascii|#)]

Returns a character based on the ASCII code in the first parameter. Can accept second parameter that is the number of characters to add. If omitted it assumes 1.


ASCII chart reference (external site): 

http://www.asciitable.com/

[CHAR(9)] - returns one tab character (ASCII 9 is tab)


[CHAR(65|3)] - returns "AAA" (ASCII 65 is A)

[RNDSTR(NumChars|SpecialChars)]

[RANDOMSTR(NumChars|SpecialChars)]

[RANDOMSTRING(NumChars|SpecialChars)]


Generates a random string of the given length. 


NumChars - The number of characters long to make the string


SpecialChars = whether or not to accept "special" characters. Currently - _ ~ and . are included if this flag is true.

[RNDSTR(10)] = ljahUy54JE


[RNDSTR(20|true)] = jh8Gf.f4~f_hgT6C9qK-



[GUID]

Unique GUID number

ABCDEF-87HJ-ABFECD-43343

[REGEXREPLACE(St|regex|replace)]

Uses REGEX expression to replace sub-strings inside string.


NOTE - Use [~[REGEXEXPRESSION]~] to make sure your REGEX expression does not get stripped by the replacement system.

[REGEXREPLACE(AbCd-123|[~[^a-z]~])] = bd


[REGEXREPLACE(AbCd-123|[~[^a-zA-Z]~])] = AbCd


[REGEXREPLACE(AbCd-123|[~[^a-zA-Z0-9]~]|:)] = AbCd:123


[MATH]

MATH allows you to solve simple mathematical equations. For instance you can do [MATH(1+2*3)] and it would return 7. MATH allows for parentheses in your equations so you can change the last example to [MATH((1+2)*3)] to return 9. 



Available mathematic operations are as follows and are evaluated in this order.

  • ( and ) = Parentheses (operational grouping)
  • ^ = Exponent
  • * = Multiplication
  • / = Division
  • \ = Integer Division
  • % = Modulus (remainder)
  • + = Addition
  • - = Subtraction



Operators with equal precedence are evaluated left to right in the order in which they appear in the expression. This means that if your equation is "10 + 4 - 2 * 3" the order of operation would be:

  1. 2 * 3 = 6 [10 + 4 - 6]
  2. 10 + 4 = 14 [14 - 6]
  3. 14 - 6 = 8



[VALIDFILENAME]

Returns string that contains only characters that are allowed in file names.

[VALIDFILENAME(abc/def)] = abcdef

[LI(#|Field)]

Retrieves the value in line # with the name “Field”

[LI(1|Code)] = brings back the Code value in line number 1 of the line items.


* Important Note! Variables can be combined like these examples:

       [DIFF([DATE]|[DATE(3,d)]|d)] will calculate the days difference between now and now +3 days - i.e. 3

       [DIFF([DATE]|[DATE(-3,d)]|d)] will calculate the days difference between now +3 now days - i.e. -3

       [ABS([DIFF([DATE]|[DATE(-3,d)]|d)])] will calculate the ABS value of days difference between now +3 now days - i.e. 3


Check the .NET standard date formats for more date formatting options.


Possible values for Day Of Week are S, Sun, Sunday, M, Mon, Monday, T, Tue, Tuesday, W, Wed, Wednesday, H, Thu, Thursday, F, Fri, Friday, A, Sat, Saturday.


Possible Period values: 

Y = Years as compared by year number 

Example 1: [DIFF(1/1/2009|12/31/2009)] = 0 

Example 2: [DIFF(12/31/2009|1/1/2010)] = 1

Example 3: [DIFF(6/1/2009|5/30/2010)] = 1

Example 4: [DIFF(6/1/2009|6/1/2010)] = 1

y = Years as compared by number of days between 

Example 1: [DIFF(1/1/2009|12/31/2009)] = 1 

Example 2: [DIFF(12/31/2009|1/1/2010)] = 0

Example 3: [DIFF(6/1/2009|5/30/2010)] = 0

Example 4: [DIFF(6/1/2009|6/1/2010)] = 1

M = Months

D and d = Days

B = Business Days

H and h = Hours

m = Minutes

S and s = Seconds



NOTE: 

Some variables are not allowed in certain contexts. For instance, FileName is a reserved replacement name for the name of the file being imported. If you also have a CSV column header with this value that you want to use you will need to use square brackets and curly braces together - i.e.  [{variable}]. So if you want to be able to capture the FileName CSV value you can use [{FILENAME}] in your configuration.


The [{var}] method also works for double replacements on incoming values. For instance, if you have a field named "AcctNumber" in docMgt but the field coming in is "Acct Num" you can use a Name of "AcctNumber" and a value of [Acct Num] (notice the square brackets)  to move the "Acct Num" value into the "AcctNumber" field. However, if you also want to do a replacement on the value while it is being moved to the new name you will have to use the [{ }] method to avoid the initial replacement. If you wanted to pad the "Acct Num" value with leading zeros while renaming it then you have to be careful. Here are some examples:


Assuming the field "Acct Num" is 123:


[PADLEFT([Acct Num]|5|0)] = 123


[{PADLEFT([Acct Num]|5|0)}] = 00123


The reason for the difference is the order in which the variables are evaluated. If you use only square brackets then the [Acct Num] value is replaced at the same time that the [PADLEFT] command is evaluated and the [PADLEFT] won't have the incoming value from the CSV. If you use the [{ }] method then that defers the evaluation of that variable until after the other variables have been read and evaluated. 


Long story short - if you are trying to perform any variable actions on values that are coming into the data stream then use [{ }] to ensure the evaluation happens after all data values have been read.