Evaluate Formulas in Dynamics NAV

ERP software knows math, or do they? Dynamics NAV can do simple arithmetic in a decimal field, eg. type “14*50+10″ and the system will evaluate this to the correct result “710″. But if you start nesting your expression with parenthesis it gets a bit confused and tells you “Please specify a decimal or calculation expression”. In certain scenarios, you will require complex setups for eg. item pricing or salesperson commissions, that is defined by a formula with certain constants in it (read: other field values).

I have as well as others, previously constructed a custom function to handle this within NAV, that evaluates a formula breaking it down to single expressions and calculating the result of this. As some of the other implementations, that are available on MIBUSO, like Calc by Yaroslav Gaponov and Mathematical Expression Parser by Justas Janauskas, they usually have certain scenarios where they fail. Or you have to format your expression a certain way for it to work. My scenario was a simple expression like “20*-10″ that failed. As it expected to have a value between each operator it saw it like this “20 * 0 – 10″ which obviously isn’t correct. I did fix the code i had, but at the same time i found a much easier solution for the problem.

In the programming languages VB and Java you have a function called Eval(). It can take an expression, and evaluate it into a decimal value. With automation in Dynamics NAV you have access to this via the automation control ‘Microsoft Script Control 1.0′.ScriptControl (GUID: {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}). Here is the few lines of code needed:

EvaluateFormula(Formula : Text[1000]) : Decimal
CREATE(MSScriptControl);
MSScriptControl.Language('VBScript');
Result := MSScriptControl.Eval(Formula);
CLEAR(MSScriptControl);
in addition with a small function to insert parameters you now have a powerful function to do calculations:

InsertVariableValue(VAR Formula : Code[250];VariableName : Code[30];VariableValue : Decimal)
WHILE STRPOS(Formula,VariableName) <> 0 DO BEGIN
  Pos := STRPOS(Formula,VariableName);
  Formula := DELSTR(Formula,Pos,STRLEN(VariableName));
  Formula := INSSTR(Formula,FORMAT(VariableValue),Pos);
END;
So lets take at an example formula for Commission, in this scenario it uses fields from Item Ledger Entry and Functions:

(([SALES AMOUNT (ACTUAL)]+{NETFREIGHTBYAMOUNT}-{COMMCOST})-([SALES AMOUNT (ACTUAL)]+{FREIGHTBYAMOUNT})*.005)*{RATE}
Fields in [] are fields from the Item Ledger Entry table that are filled with RecRef functionality, and the fields in {} are functions and/or constants. The functionality should be very easy to extend, and as i just wanted to introduce the Eval() function, this article does not dwelve deeper into specifics. Enjoy.