Expression Evaluator

Expression Evaluator

You are here:

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:

{

“id”:  “853c571e-14fe-4798-9b80-437473eeef77”,

“name”:  “Insight Works Rule Builder”,

“publisher”:  “Insight Works”,

“version”: “1.1.0.0”

}

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:

Formula 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
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
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) Calculates the smallest of two values passed in. MIN(Var1; 10)
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) Calculates the largest of two values passed in. MAX(Var1; 10)
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.
RoundDown(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
codeunit 70098512 “IWX Rule Expression Evaluator”

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. For example, if you have a LENGTH option, you could calculate the amount of child component required using a formula like “Length * 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:

  • [Unit Price]
  • [Unit Cost]
  • [Standard Cost]
  • [Last Direct Cost]
  • [Indirect Cost %]
  • [Reorder Point]
  • [Maximum Inventory]
  • [Reorder Quantity]
  • [Discrete Order Quantity]
  • [Minimum Order Quantity]
  • [Maximum Order Quantity]
  • [Safety Stock Quantity]
  • [Order Multiple]
  • [Reserved Qty. on Prod. Order]
  • [Res. Qty. on Prod. Order Comp.]
  • [Res. Qty. on Req. Line]
  • [Dampener Quantity]
  • [Overflow Level]
  • [Planning Transfer Ship. (Qty).]
  • [Planning Worksheet (Qty.)]
  • [Qty. on Purch. Return]
  • [Qty. on Sales Return]
  • [Periods]
  • [Total Quantity]
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.

Was this article helpful?
0 out Of 5 Stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
5
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Need help?

Leave A Comment

Go to Top