Number / Math Variables
|
Variable |
Action or Value |
Example |
|
[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 Number Formats: - Standard - Custom |
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 |
|
[GETNUMVALUE(val)] |
Strips currency symbols from a string and returns the pure numeric value. Will convert non numeric or an empty string into a 0 so it can be used in calculations. val = Value to convert into number |
[GETNUMVALUE()] = 0 [GETNUMVALUE($12.30)] = 12.3 [GETNUMVALUE(asdf)] = 0 |
|
[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)] = 22 |
|
[ROUND(num|places|fromzero)] |
Rounds a number to the number of places based on the "fromzero" (Away From Zero) setting. num = Number to round places = The number of decimal places to round to fromzero = true/false value on how to round (default = true). Based on the MidpointRoundingEnum in .NET. true = round to nearest number away from zero. I.e. 12.365 rounded to 2 places is 12.37 false = round to the nearest even number. I.e. 12.365 rounded to 2 places is 12.36. |
[ROUND(12.365|2)] = 12.37 [ROUND(12.365|2|false)] = 12.36 [ROUND(12.345|2)] = 12.35 [ROUND(12.3|0)] = 12 |
|
[MIN(nums)] [MIN(field)] [MIN(json|prop)] [MIN(string|delim)] |
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(nums)] [MAX(field)] [MAX(json|prop)] [MAX(string|delim)] |
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(nums)] [AVG(field)] [AVG(json|prop)] [AVG(string|delim)] |
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(nums)] [SUM(field)] [SUM(json|prop)] [SUM(string|delim)] |
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(equation)] |
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. Starting in Version 4.13, MATH will ignore the current currency symbol. |
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 |
|
[CURRENCY()] [CURRENCY(invariant)] |
Returns the currency symbol for the current culture. invariant = Returns the non-culture specific currency symbol which is ¤. The cloud-OCR system uses the invariant culture for its processing so its currency symbol is the ¤ symbol. Once the data is written to the database and read back out it is converted to the current culture. |
[CURRENCY()] = $ (in the US culture) [CURRENCY(true)] = ¤ |
|
[DEVIATION(Value|Basis)] |
Calculates the percentage difference between 2 values using the following formula: ((Value - Basis) / Basis) |
[DEVIATION(105|100)] = .05 |
|
[AUTONUM(name|start)] [AUTONUMRESET(name|start)] [AUTONUMDELETE(name)] [AUTONUMEXISTS(name|yesvalue|novalue)] |
AUTONUM will return the next available number based on a named sequence. If the named sequence does not exist it will be created. AUTONUMRESET will reset the starting value for the named sequence. If the named sequence does not exist it will be created. AUTONUMDELETE will remove the named sequence if it exists. AUTONUMEXISTS will return yesvalue if the named sequence exists and novalue is it does not name = the name of the sequence. Each sequence name needs to be unique and each has their own number system. It is normal to have one for each Record Type using the RecordType name or number in combination. ONLY ALPHA-NUMERIC VALUES ARE VALID FOR THE NAME IMPORTANT! There are some reserved names that you cannot use. They include the basic object types in the system such as Records, Documents, Events, WorkItems, Addins, ActionSets, Users, Teams, etc. If you get an error similar to "There is already an object named 'name' in the database" then you have tried to use a reserved name. start - for AUTONUM this sets the starting number ONLY ON THE FIRST USE. After the sequence is created this will be ignored. For AUTONUMRESET this sets the next number that will be returned. |
[AUTONUM(PONumber|1000)] = Returns 1000 for the first time, then 1001, 1002, etc. [AUTONUMRESET(PONumber|100)] = Does not return a value but sets the next available number for PONumber to 100. [AUTONUMDELETE(PONumber)] = Does not return a value but deletes the AUTONUM sequence completely [AUTONUMEXISTS(PONumber|yes|no)] = Returns 'yes' if the PONumber sequence exists and 'no' if it does not |
|
[EXP2DECIMAL(value)] |
Converts exponential notation number (scientific number) to its closest floating point equivalent. Value = the value to convert. The value must have the letter E in it and must have numbers to either side of the E. Valid examples: 1.6E-35 3.25+2 |
|