How to Calculate Values from a Formula, written in Algebraic Expression, in MS Dynamics Ax 2012?

Sometimes we come across a requirement of providing solution to our customer, where there is a demand for some automation related to calculation of BOM quantity through some predefined formula. These predefined formula, provides some, linking calculation for some output quantity and input quantities.

To provide solution, normally a need arises to provide a job to calculate the desired quantities. Say for example Calculate the value of A*B+(B*B*C+A)/B, and if value of A is 4, B is 7, and C is 8. Then the Calculation will be 4*7+(7*7*8+4)/7, which is equal to 84.57142857142857.

To achieve this evalBuf function could be used.

What is evalBuf Function?

Evaluates the input string of X++ code, then returns the result as a string.

str evalBuf(str expressionString)

For example, an input of 2 + 4 results in the string 6, not in the integer 6.

Further, if an attacker can control input to the evalBuf function, it presents a security risk. Therefore, this function runs under Code Access Security. Calls to this function on the server require permission from the ExecutePermission Class.

For Example

static void JobEvalBufDemo(Args _args)

{

    ExecutePermission perm;

    str strCodeToExecute = "2 + 5";

    str strResult;

    ;

    perm = new ExecutePermission();

    if (perm != null)

    {

        // Grants permission to execute the EvalBuf function.

        // EvalBuf runs under code access security.

        perm.assert();

        print "Next will execute the string of code.";

        pause; // Click Yes when asked to continue.

        strResult = EvalBuf(strCodeToExecute);

        // Close the code access permission scope.

        CodeAccessPermission::revertAssert();

    }

    print "strResult is: [", strResult ,"]";

    pause;

    //

    // Expected: "strResult is: [7]".

}

This evalBuf Function can calculate the values of any Numerical Expression, rather than an Algebraic Expression. So, the formula represented in Algebraic Expression needed to be converted into a Numerical Expression.

Now, the problem is to replace the values in the place of variables, like A, B, C (refer our example).

To resolve this we can use strReplace Method.

What is strReplace Method?

str strReplace(str _str, str _fromStr , str _toStr)

Replace all of those occurrence in the text string specified by _str of substring equal to _fromStr with the string specified in _toStr and return the new string. That is, starting first character subtring in _str equal to _fromStr will be replace with _toStr.

strReplace("a.b.c.d", ".", " ") //returns the text string "a b c d"

To provide a better solution, two tables can be created, one will contain the Formula, and other will contain its respective variables and its values.

Say,

1.    Table: _SNMCalculateFormula

a.    Field: FormulaID

b.    Field: FormulaDetail

2.    Table _SNMCalculateData

a.    Field: FormulaID

b.    Field: FormulaVarName

c.    Field: FormulaVarVal

Also put some data as shown in the screenshots.

 

Created a job named as jobCalculateOne, as shown below.

static void jobCalculateOne(Args _args)

{

    ExecutePermission perm;

    str strCodeToExecute;

    str strResult;

    str 10 strFormulaID="Two";

    _SNMCalulateFormula snmCalFormula;

    _SNMCalculateData snmCalData;

    ;

    select firstOnly snmCalFormula

        where snmCalFormula.FormulaID == strFormulaID;

    if (snmCalFormula.FormulaID)

    {

        info(strFmt("Name : %1", snmCalFormula.FormulaDetail));

        strCodeToExecute= snmCalFormula.FormulaDetail;

    }

     while select snmCalData

         where snmCalData.FormulaID==strFormulaID

    {

        info(strFmt(

            "%1 - %2",

            snmCalData.FormulaVarName ,

            snmCalData.FormulaVarVal));

            strCodeToExecute=strReplace(strCodeToExecute, snmCalData.FormulaVarName,  snmCalData.FormulaVarVal);

    }

    perm = new ExecutePermission();

    if (perm != null)

    {

        perm.assert();

        strResult = EvalBuf(strCodeToExecute);

        CodeAccessPermission::revertAssert();

    }

    info(strFmt("Result is: %1", strResult));

}

In the job value of strFormulaID is “Two”, on running this job the following value are displayed.

This variable strFormulaID could be parameterised, for better use.