Number / Math Variables
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:
|
[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:
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 |