Expression Evaluator
Several Insight Works apps provide calculated expression value capabilities, many of which use a common expression evaluator. This evaluator provides calculation capabilities and can also be used with customizations if they include the “Insight Works Rule Builder” library.
To use the expression evaluator in your own extension, include this dependency in your app.json:
Then reference: codeunit 70098512 “IWX Rule Expression Evaluator”.
Standard formula syntax is used, such as (1+2)*3
Formulas
Several formulas are supported, and developers can add custom formulas to meet specific requirements. The following built-in formulas are supported:
| Function | Description | Example |
| ABS(Value) | Calculates the absolute value of the argument. | ABS(-1) ABS(Length-5) |
| ACOS(Value) | Returns the arccosine, or the inverse cosine of a number. | acos(1/3)=1.23095941 |
| ACOT(Value) | Returns the angle that is the value of the specified number. | Acot(3)=0.32175 |
| ALL(Value1; Value2; Value3; Value4; Value5) | Logical operator. Returns 1 if all the values are non-zero.
Requires RuleBuilder 1.3 and newer. |
ALL(1;0;3.14;[VariableB])
ALL(1;2;3) Would result in 1. ALL(1;2;3;0) Would result in 0. |
| ANY(Value1; Value2; Value3; Value4; Value5) | Logical operator. Returns 1 if any of the values are non-zero.
Requires RuleBuilder 1.3 and newer. |
ANY(1;0;3.14;[VariableB])
ANY(0;0;5) Would result in 1. ANY(5;0) Would result in 1. ANY(0;0) Would result in 0. |
| ASIN(Value) | Returns the angle with the sine that is the specified number | Asin(0.3) = 0.30469 |
| ATAN(Value) | Returns the angle with the tangent that is the specified number. | Atan(0.3) = 0.29146 |
| AVG(Value1; Value2; Value3; Value4; Value5) | Calculates the average of the supplied parameters.
Requires RuleBuilder 1.3 and newer. |
AVG(9.98; 10.01; 10.00; 9.99; 10.02; 10.03; 9.97; 10.05)
Would result in 10.00625 |
| Ceiling(Value) | The smallest integral value that is greater than or equal to the specified decimal number. | Ceiling(2.01) = 3 |
| Cos(Value) | The cosine of the value. | Cos(Value) |
| Cosh(Value) | The hyperbolic cosine of the value. | Cosh(value) |
| Cot | The cotan of the value, equivalent to cos(value)/sin(value). | Cot(value) |
| Degrees(Value) | This converts radians to degrees. | – |
| Floor(value) | The smallest integral value. | Floor(2.9)=2 |
| IEEEREMAINDER(Value1; Value2) | The remainder resulting from the division of Value1 from Value2. | – |
| Log(Value1) | The natural log of Value1. | – |
| Log10(Value1) | The base 10 log of Value1. | – |
| Pi() | 3.1415926535897931 | – |
| E() or EULER() | 2.7182818284590451 | – |
| Radians(Value1) | Converts degrees to radians. | – |
| Sign(Value1) | Returns an integer that indicates the sign of Value1. | Sign(-123)=-1
Sign(123)=1 |
| Sin(Value1) | Returns the sine of the value. | – |
| Sinh(Value1) | Returns the hyperbolic sign of the value. | – |
| SQRT(Value1) | Returns the square root of the supplied value. | SQRT(49) = 7 |
| TAN(Value1) | – | – |
| TanH(Value1) | – | – |
| TRUNCATE(Value1) or TRUNC(Value1) | The integral part of the supplied value. | Truncate(Pi())=3 |
| Min(Value1; Value2)
Min(Value1; Value2; Value3; Value4; Value5 ) |
Calculates the smallest of two values passed in.
Rulebuilder 1.2 and older support two. Rulebuilder 1.3 and newer support up to 100 parameters. |
MIN(Var1; 10)
MIN(Var1; 10; 100; [VariableB]; 3) |
| Mod(Value1; Value2) | Modulus, provides the integer remainder of a division calculation. The result has the same sign as the divisor. | MOD(3;2) = 1 |
| Max(Value1; Value2)
Max(Value1; Value2; Value3; Value4; Value5 ) |
Calculates the largest of multiple values passed in.
Rulebuilder 1.2 and older support two parameters. Rulebuilder 1.3 and newer support up to 100 parameters. |
MAX(Var1; 10)
MAX(Var1; 10; 100; [VariableB]; 3) MAX(2;20;5;1) Would result in 20. MAX(0;-0.00000001;0.00000001;0) Would result in 0.00000001 |
| POW(Number; Power) | Raises a value to a power. | POW(2;8) |
| Random( OptionalValue1 )
Or Rand( OptionalValue1) |
When OptionalValue1 is supplied, this returns a random number between 0 and 100,000 similar to Excel’s Random function.
If you supply OptionalValue1, this returns a number between 0 and OptionalValue1. |
Random(100) = a number between 0 and 100 |
| Randbetween( Value1; Value2) | Similar to Excel’s RANDBETWEEN, this returns a value between Value1 and Value2. | RandBetween(100;200) = returns a value between 100 and 200. |
| Sequence(OptionValue1) | Similar to SQL’s sequence ability, this provides a simple sequential number that increments.
When OptionalValue1 is supplied, this starts at that value in the expression. When OptionalValue1 is not supplied, this starts at the last sequence used, or 1. |
Sequence() = 1
Sequence() = 2 Sequence() = 3 |
| Round(Value; Precision) | Rounds a value to a certain number of decimal places. The precision argument uses NAV rounding precision notation (e.g., 0.01 means round to 2 decimal places). | Round(OptionA; 1) |
| RoundUp(Value; Precision) | Similar to Round, but always rounds up instead of the nearest value. | – |
| RoundDn(Value; Precision) | Similar to Round, but always rounds down instead of the nearest value. | – |
| IfDivBy0(DivisionFormula; Result) | If the formula in DivisionFormula results in a divide-by-zero error, the value of the Result parameter is used instead. If no divide-by-zero error occurs, the division result is returned. | IfDivBy0(1/Length; 0) |
| EQUALS(Value1;Value2) or EQ(Value1;Value2) | If Value1=Value2 then 1 is returned, otherwise 0. | Equals(3.1;3.10)=1
Equals(3.1;3.101)=0 |
| GREATERTHAN(Value1;Value2) or GT(Value1;Value2) | If Value1>Value2 then 1 is returned, otherwise 0. | GT(3.1;3.0)=1
GT(3.1;3.101)=0 |
| LESSTHAN(Value1;Value2) or LT(Value1;Value2) | If Value1=Value2 then 1 is returned, otherwise 0. | Lt(3.1;3.101)=1
Lt(3.101;3.1)=0 |
| IF(Value1;Value2;Value3) | Similar to Excel’s “IF” function.
If the expression in Value1 is greater than or equal to 1, this returns value2; otherwise, this returns value3. |
If ( Equals(1;1.01); 2; 3 ) = 3
If ( Equals(1;1.00); 2; 3 ) = 2 |
| CustomFunctionName(Value1;Value2) | You can add your own custom function in an extension by subscribing to:
OnEvaluateCustomFunction(ptxtFunctionName: Text; pdFuncParam1: Decimal; pdFuncParam2: Decimal; var pdResult: Decimal; var pdicVariableBuffer: Dictionary of [Text, Decimal]; var pbHandled: Boolean) In |
– |
| SUM(Value1; Value2; Value3; Value4; Value5) | An alternative to using +, can be used when dynamic text values are supplying values to sum and it’s unclear how many values might exist.
Requires RuleBuilder 1.3 and newer. |
SUM(0;1;2;3;4;5;6.6;7.777)
Would result in 29.377 |
| MAD(Value1; Value2; Value3; Value4; Value5) | Median Absolute Deviation (Not Mean Absolute Deviation). This is used to measure how consistent a set of inspection readings are while being resistant to outliers. Ideal for detecting unstable or unreliable measurement systems. Use to help detect noisy or inconsistent measurements, or just for outlier resistance. MAD tells you how noisy your measurements are.
Requires RuleBuilder 1.3 and newer. |
Let’s presume you had the following thickness measurements and put them in the formula:
MAD(9.98; 10.01; 10.00; 9.99; 10.02; 10.03; 9.97; 10.05) This would result in 0.02. |
| VARP(Value1; Value2; Value3; Value4; Value5) | Calculates the variance of a population. This can be used to help measure how spread out measurements are from average.
Requires RuleBuilder 1.3 and newer. |
Let’s presume that you have the following thickness measurements:
1.00, 1.02, 0.99, 1.01, 0.98 VARP(1.00; 1.02; 0.99; 1.01; 0.98) Would result in an average of 1.00. Deviations would be 0.00,0.02,–0.01,-0.02. Squares would then be 0,0.0004,0.0001, 0.0001, 0.0004 Resulting average of those squares (VARP) would then be 0.0002 |
| RANGE(Value1; Value2; Value3; Value4; Value5) | Calculates the range of the supplied values. This is effectively the highest measurement less the lowest measurement in the supplied values.
Range can be used as a quick spread check, allowing someone to help spot obvious instability, or used to help trigger further actions. Requires RuleBuilder 1.3 and newer. |
Let’s presume you have the following thickness measurements.
RANGE(10.01; 10.00; 9.98; 10.02) In this example the range would be 10.02 -9.98 = 0.04 |
| MODE(Value1; Value2; Value3; Value4; Value5) | Calculates the mode of the list of values by using the first value that achieves the highest frequency.
Requires RuleBuilder 1.3 and newer. |
MODE(1;2;2;3)
Would result in 2. MODE(1;1;2;2;3) Would result in 1, because it’s the first even though it has the same frequency as 2. |
| MEDIAN(Value1; Value2; Value3; Value4; Value5) | Calculates the median of the list of values.
Requires RuleBuilder 1.3 and newer. |
MEDIAN(10;30;20)
Would result in 20. MEDIAN(10;20) Would result in 15. MEDIAN(1;2;3;4) Would result in 2.5 |
| STDDEVS(Value1; Value2; Value3; Value4; Value5) | This function calculates the sample standard deviation. Use this function when the range of values represents a sample of values, rather than an entire population.
Requires RuleBuilder 1.3 and newer. |
Let’s presume you have the following thickness measurements:
9.98; 10.01; 10.00; 9.99; 10.02; 10.03; 9.97; 10.05 STDDEVS(9.98; 10.01; 10.00; 9.99; 10.02; 10.03; 9.97; 10.05) Would result in 0.0266926956300783. The calculation broken down: Mean would be xˉ=80.05/8=10.00625 Squared deviations would be: Value Deviation Square 9.98 -0.02625 0.0006890625 10.01 0.00375 0.0000140625 10.00 -0.00625 0.0000390625 9.99 -0.01625 0.0002640625 10.02 0.01375 0.0001890625 10.03 0.02375 0.0005640625 9.97 -0.03625 0.0013140625 10.05 0.04375 0.0019140625 Sum of squares: 0.0049875 STDDEV (Sample) = s= √0.0007125 = ~0.0267 (0.0266926956300783) |
| STDDEVP(Value1; Value2; Value3; Value4; Value5) | This function calculates the population standard deviation. Use this function when the range of values represents the entire population.
Requires RuleBuilder 1.3 and newer. |
Let’s presume you have the following thickness measurements:
9.98; 10.01; 10.00; 9.99; 10.02; 10.03; 9.97; 10.05 STDDEVP(9.98; 10.01; 10.00; 9.99; 10.02; 10.03; 9.97; 10.05) Would result in 0.0249687304442977. The calculation broken down: Mean would be xˉ=80.05/8=10.00625 Value Deviation Square 9.98 -0.02625 0.0006890625 10.01 0.00375 0.0000140625 10.00 -0.00625 0.0000390625 9.99 -0.01625 0.0002640625 10.02 0.01375 0.0001890625 10.03 0.02375 0.0005640625 9.97 -0.03625 0.0013140625 10.05 0.04375 0.0019140625 Average of squared deviations = 0.000623 STDDEV (Sample) = √0.000623 = 0.02496 mm (0.0249687304442977) |
| PERCENTILE( Percentile;Value1; Value2; Value3; Value4; Value5) | Percentile indicates the measurement value below which a specified percentage of inspected parts fall.
Percentile is useful to see how close most of production is to an upper tolerance without being distorted by a single extreme value. The first parameter is the percentile to compare with, and in the range 1-100. The other parameters are the values to use. Requires RuleBuilder 1.3 and newer. |
90th percentile example.
PERCENTILE(90;9.98; 10.01; 10.00; 9.99; 10.02; 10.03; 9.97; 10.05; 0.90) Would result in approximately 10.04 |
| CPK(LowerLimit;UpperLimit;Value1; Value2; Value3; Value4; Value5) | CPK is the process capability index / process capability ratio, and is the statistical measure of process capability.
First parameter is the lower limit. Second is the upper limit. 3rd and remaining parameters are the values to analyze. The numeric result directly describes how safely the process fits inside the tolerance. CPK combines the spread(variation) and centering (distance from limits). A high CPK result only happens when both are good. CPK can be used to help with ongoing capability monitoring, but typically not used in pass/fail metrics for individual inspections. More information can be found here: https://en.wikipedia.org/wiki/Process_capability_index Example interpretations: < 0 Process mean is outside tolerance 0.5 Large portion of parts will be non-conforming 1.0 Process barely fits tolerance (high risk) 1.33 Generally acceptable production capability 1.67 Good, low defect risk 2.0+ Very stable, highly capable process Requires RuleBuilder 1.3 and newer. |
This example the lower limit would be 9.95, upper would be 10.05, and the values would be 9.98; 10.01; 10.00; 9.99; 10.02.
CPK(9.95; 10.05; 9.98; 10.01; 10.00; 9.99; 10.02) would result in 1.11. Mean = 10.00 Std Dev ≈ 0.01581138830084156 Lower Cpk = (Mean − LSL) / (3σ) = (10.00 − 9.95) / (3 × 0.015) = 1.11 Upper Cpk = (USL − Mean) / (3σ) = (10.05 − 10.00) / (3 × 0.015) = 1.11 CPK = 1.0540925533894971 |
Available Variables
The following table summarizes the available variables you can use:
| App | Variable | Notes |
| Configurator | Any option code can be used within the calculation. | Example: If you have a LENGTH option, you could calculate the amount of child component required using a formula like “Length * 1.1”.
To access parent variables in a subconfiguration, prefix the option name with “PARENT”. For example, “ParentLength *1.1” |
| Forecast Worksheet | avg_daily_usage | Average daily usage of an item. Equivalent to usage_qty
/ forecast_days. Safety stock expressions can often just be set to avg_daily_use. |
| Forecast Worksheet | avg_daily_forecast | Average daily forecast of an item. This is equivalent to forecast_qty/forecast_days
You can often use avg_daily_forecast to determine the reorder point with: avg_daily_forecast * lead_time_days |
| Forecast Worksheet | lead_time_days | Lead time of an item.
If there is a SKU and it has a “Lead Time Calculation” defined, this is used. If no lead time is available on the SKU, this analyzes the Item, and if the item has a “Lead Time Calculation” defined, that is used. If no lead time is yet available, the Item has a “Vendor No.” supplied, and that vendor had a “Lead Time Calculation” defined, that lead time from that related vendor card is used. |
| Forecast Worksheet | forecast_qty | This is the forecasted quantity for the period that is supplied by the Sales & Forecast extension. |
| Forecast Worksheet | forecast_days | Number of forecasted days in the period. |
| Forecast Worksheet | usage_qty | Usage quantity of an Item for the given period.
This considers all negative item ledger entries except for transfers in the period. This Usage Quantity is calculated differently than the Item Planning Review. |
| Item Planning Review | All numeric fields on the item planning buffer table. | All numeric fields on the item planning buffer table can be used, and the notation is to use square brackets to reference them.
Examples of available variables that can be used:
|
| Item Planning Review | lead_time_days | Lead time of an item.
If there is a SKU and it has a “Lead Time Calculation” defined, this is used. If no lead time is available on the SKU, it analyzes the Item, and if the item has a “Lead Time Calculation” defined, that is used. If no lead time is yet available, the Item has a “Vendor No.” supplied, and that vendor had a “Lead Time Calculation” defined, that lead time from that related vendor card is used. |
| Item Planning Review | Days | This is the number of days between the supplied start and end on the Item Planning Review. |
| Item Planning Review | avg_daily_usage | Average daily usage of an item. This is equivalent to usage_qty / days.
Safety stock expressions can often just be set to avg_daily_use. |
| Item Planning Review | usage_qty | Usage quantity of an item for the given period.
This considers assembly consumption, assembly output, consumption, negative adjustments, output, and sales by default; you can change this by adjusting the Item Ledger Filter on the Item Planning Review Setup page. This Usage Quantity is calculated differently than the Forecast Worksheet. |
| Quality Inspector | [Item:FieldName] | Provides an ability to do item lookups based on the item that’s on the Quality Inspection Test.
[Item:No.] would look up the item number.[Item:Description] would look up the item description.
Any field on the item card can be used. The item used is the source item that’s defined on the quality inspection test. If the quality inspection test does not have a source item, this has no effect. Minimum Business Central 18 is required. |
| Quality Inspector | [Attribute:AttributeName] | Provides the ability to do item attribute lookups based on the item that’s on the Quality Inspection Test.
[Attribute:Color] would look up the item attribute ‘color’ for the associated item relating to the quality inspection test.[Attribute:Depth] would lookup the depth item attribute from the related item for the test.
Any attribute can be used as long as it exists on that item. The item used is the source item defined on the quality inspection test. If the quality inspection test does not have a source item, this has no effect. Minimum Business Central 18 is required. |
| Quality Inspector | [Routing:FieldName] | Intended for use with production related tests.
[Routing:No.] would look up the routing no. for the associated routing for the quality inspection test.
If the quality inspection test does not have a source item, this has no effect. Minimum Business Central 18 is required. |
| Quality Inspector | [Vendor:FieldName] | Intended for use with receiving related tests that can be connected to a vendor (such as purchase receipts).
[Vendor:No.] would look up the vendor no. from the vendor card.
If the quality inspection test does not have a vendor, this has no effect. Minimum Business Central 18 is required. |
| Quality Inspector | [Measure:FieldName] | Intended for use with production order-related tests where Business Central quality measures are used.
This will allow you to look up a field from the related production order routing quality measure. [Measure:Min. Value] would look up the Min. Value field from the related measure. The measure it looks up would be based on a mapped field or by naming convention for the specific test line that the expression is used on.If the quality inspection test is not related to production or a mapped field, there is no effect. Minimum Business Central 18 is required. |
| Quality Inspector | [Lookup(TableName;FieldName;Field1=Value1,Field2=Value2)] | Provides very simple “first match” single value lookups to other tables in the system.
[Lookup(Item;Description;No.=[Source Item No.])]
would be functionally equivalent to [Item:Description] Example: You have a table containing thresholds you want to use in your pass condition, but it is based on a choice made in a previous test value, your formula would be similar to this: [Lookup(YourTable;FieldWithValue;KeyField=[ChoiceField])] Minimum Business Central 18 is required. |
| Quality Inspector | [Replace(InputText;SearchFor;ReplaceWith)] | Allows simple text replacements.
Example: The expression “[Replace(Cat;C;H)]” would result in “Hat”. Minimum Business Central 18 is required. |
| Quality Inspector | [Classify(InputText;CompareWith;Replacement)] | Allows simple whole-value classification, for example to convert lookups of text values into numerical limits.
When the compare text does not match the expression will result in a blank string. When the compare text does match then the expression will result in the replacement text. Example: There is a field called [MyChoice], and it has the possible values of A and B. When the value is A, we want 3, and when the value is B, we want 456. You could use: [Classify([MyChoice];A;3)][Classify([MyChoice];B;456)]Minimum Business Central 18 is required. |
| Quality Inspector | [Copystr(InputText;startPosition;Length)] | Allows simple substring extractions with a 1 based index.
Example: You have the text “ABC123DEF” and you wanted “123” from that. [Copystr(ABC123DEF,4,3)]This would begin extracting at the 4th position (where 1 is), and read 3 characters after that. Minimum Business Central 18 is required. |