Variable

Action or Value

Example

[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 

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

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

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

Available Operators:

=   Equal

<>  Not Equal

!=  Not Equal

<   Less than

>   Greater than

<=  Less than or equal to

=>  Less than or equal to

>=  Greater than or equal to

=>  Greater than or equal to

B   Between

C   Contains

S   Starts With

E   Ends With


* String operations are NOT case sensitive


[FORMAT(num|pattern)]

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

 - Numeric Formats

 - Date and Time Formats


Numeric:

[FORMAT(12.3|c)] = $12.30


Date:

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


[ISNUMBER(Val|YesVal|NoVal)]

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

[ISNUMBER(11234|NUM|)] = NUM


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


[ISNUMBER(BadNumber|YEP|NOPE)] = NOPE


[DIFF(num1|num2)]


Computes the difference between two values. 


[DIFF(1|3)] = 2 


[DIFF(3|1)] = -2 

[ABS(num)]

Absolute value of incoming variable

[ABS(-22)] returns 22

[MIN]

Minimum of values


This can take the following inputs:


- Series of values

- Field Name

- JSON Collection


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


[MIN(Age)] = returns the smallest value found in all the fields named Age. You need to have a separate numeric value in each Age field. Typically by using the "Allow more than one value for the same field" option in the advanced field settings.


[MIN([{ID: 1, Age:3},{ID: 2, Age:5}]|Age)] returns 3

[MAX]

Maximum of values


This can take the following inputs:


- Series of values

- Field Name

- JSON Collection

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


[MAX(Age)] = returns the largest value found in all the fields named Age. You need to have a separate numeric value in each Age field. Typically by using the "Allow more than one value for the same field" option in the advanced field settings.


[MAX([{ID: 1, Age:3},{ID: 2, Age:5}]|Age)] returns 5

[COUNT(item|delimiter)]

Count of values


item = the item to count. 


If this is a field name and no delimiter is supplied then it counts the number of fields with that name.


If this is a JSON array it counts the number of elements in the array.


If it is a string then it will be split by the delimiter and the resulting number of elements will be returned.

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


[COUNT([{ID: 1, Age:3},{ID: 2, Age:5}])] returns 2


[AVG]

Average of values


This can take the following inputs:


- Series of values

- Field Name

- JSON Collection

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


[AVG(Age)] = returns the average of values found in all the fields named Age. You need to have a separate numeric value in each Age field. Typically by using the "Allow more than one value for the same field" option in the advanced field settings.


[AVG([{ID: 1, Age:3},{ID: 2, Age:5}]|Age)] returns 4

[SUM]

Sum of values


This can take the following inputs:


- Series of values

- Field Name

- JSON Collection

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


[SUM(Age)] = returns the sum of values found in all the fields named Age. You need to have a separate numeric value in each Age field. Typically by using the "Allow more than one value for the same field" option in the advanced field settings.


[SUM([{ID: 1, Age:3},{ID: 2, Age:5}]|Age)] returns 8

[RANDOM]

[RND]

Returns random numbers

[RANDOM] returns number between 1 and 100


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

[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 mathematical 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


[CLEANCUR(val|not)]

Inspects val and if it is not a valid number the variable attempts to auto-correct known patterns. If val is a number then the return is a formatted currency value $123.00.


Currently corrects the following patterns:

  • USD 3,600.00
  • $3,600.00 USD
  • 55 25 (missing decimal point)
  • $3,600,00 (decimal point converted to comma)
  • $600,00 (decimal point converted to comma)



val = value that should be a number.


ret = If it cannot auto-correct then this value is returned.

[CLEANCUR(USD 123.00)] = $123.00


[CLEANCUR(123)] = $123.00


[CLEANCUR(123 00)] = $123.00


[CLEANCUR(123,00)] = $123.00


[CLEANCUR($1,123,00)] = $1,123.00


[DEVIATION(CheckValue|BasisValue)]

Calculates the percentage difference between 2 values using the following formula:


((CheckValue - BasisValue) / BasisValue)


[DEVIATION(105|100)] = .05