ERP software knows math, or do they? ![\"\"](\"http:\/\/45.63.48.66\/wp-content\/uploads\/2010\/06\/061510_2223_EvaluateFor1-300x221.jpg\" "\\"061510_2223_EvaluateFor1.jpg\\"")
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).<\/p>\n
<\/p>\n
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<\/a>, like Calc by Yaroslav Gaponov<\/a> and Mathematical Expression Parser by Justas Janauskas<\/a>, 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.<\/p>\n 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<\/strong> (GUID: {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}<\/em>). Here is the few lines of code needed:<\/p>\n in addition with a small function to insert parameters you now have a powerful function to do calculations:<\/p>\n So lets take at an example formula for Commission, in this scenario it uses fields from Item Ledger Entry and Functions:<\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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”. […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[3],"tags":[30,77,31,32,33],"class_list":["post-338","post","type-post","status-publish","format-standard","hentry","category-navision","tag-automation","tag-navision","tag-formula","tag-math","tag-script"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts\/338","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/comments?post=338"}],"version-history":[{"count":22,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts\/338\/revisions"}],"predecessor-version":[{"id":372,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts\/338\/revisions\/372"}],"wp:attachment":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/media?parent=338"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/categories?post=338"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/tags?post=338"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\nEvaluateFormula(Formula : Text[1000]) : Decimal\r\nCREATE(MSScriptControl);\r\nMSScriptControl.Language('VBScript');\r\nResult := MSScriptControl.Eval(Formula);\r\nCLEAR(MSScriptControl);\r\n<\/pre>\n\r\nInsertVariableValue(VAR Formula : Code[250];VariableName : Code[30];VariableValue : Decimal)\r\nWHILE STRPOS(Formula,VariableName) <> 0 DO BEGIN\r\n Pos := STRPOS(Formula,VariableName);\r\n Formula := DELSTR(Formula,Pos,STRLEN(VariableName));\r\n Formula := INSSTR(Formula,FORMAT(VariableValue),Pos);\r\nEND;\r\n<\/pre>\n
\r\n(([SALES AMOUNT (ACTUAL)]+{NETFREIGHTBYAMOUNT}-{COMMCOST})-([SALES AMOUNT (ACTUAL)]+{FREIGHTBYAMOUNT})*.005)*{RATE}\r\n<\/pre>\n