Is it possible to dynamically compute a formula?

chandrurec

Hi all,

Actually I want to add a field named calculation formula which is text field in Production BOM line table.For example the formula includes 2 fields named height,width.Based on the formula given in Calculation formula, I need to calculate and populate it in a new field.

Is it possible to compute dynamically based on the calculation formula given by the user?

Thanks in advance.

Regards,
chandru.

ssingla

Yes you can...
Refer to formula calculation in Account Schedule, Structure, Excise Duty elements calculation .........

David_Singleton

chandrurec wrote:

Hi all,

Actually I want to add a field named calculation formula which is text field in Production BOM line table.For example the formula includes 2 fields named height,width.Based on the formula given in Calculation formula, I need to calculate and populate it in a new field.

Is it possible to compute dynamically based on the calculation formula given by the user?

Thanks in advance.

Regards,
chandru.

You are kidding right? Did you actually even try? #-o

Just go to any decimal field and type 4x5+3

ssingla

I think he wants

Text Box 1 (field name "Height")
Text Box 2 (Field name "Length")
Text Box 3 formula entered by user (x+y)*x/y where x = Textbox 1 and y = textbox2.

IsakssonMi

You can use the Eval method in the ScriptControl-class found in Microsoft Script Control 1.0, here is an example:

create(scriptControl);
scriptControl.Language:='vbscript';
MESSAGE('%1', FORMAT(scriptControl.Eval('1+1+1+1/2*2^3')));

chandrurec

Hi IsakssonMi,

I tried your method but the CalcFormula is a text field and this field contains something like (width*height)/100 where width and height are 2 fields.

CREATE(scriptControl);
scriptControl.Language:='vbscript';
MESSAGE('%1',FORMAT(scriptControl.Eval(CalcFormula)));

I have used like this.I am passing the CalcFormula in the Eval parameter but I am not getting the output.

If you have any idea on this,kindly let me know.

Thanks & Regards,
chandru.

IsakssonMi

Pass a valid formula string to the method and it will work. Check what you are passing first and you may find the error.

chandrurec

Hi IsakssonMi ,

I have created a test form for testing. In this form I have created 3 variables.

Name DataType Subtype Length
CalcFormula Text 250
Width Decimal
Depth Decimal

The above is the variables created.

I have created a command button and in the on push trigger ,I have given the following code.

CREATE(scriptControl);
scriptControl.Language:='vbscript';
MESSAGE('%1',FORMAT(scriptControl.Eval(CalcFormula)));

When the user run the form, he need to give values for width,depth and enter the calculationformula and based on this automatically when the user clicks the calculate button based on the formula entered by the user ,it should dynamically calculate.Now if i run the form, and click on Calculate , its showing 0.

can u help me on this?

Thanks & Regards,
chandru

IsakssonMi

Here you have a better example, make sure that your comma sign suits your windows setting.

width:=50.55;
height:=30.33;

CREATE(scriptControl);
scriptControl.Language:='vbscript';
calcFormula :=  CONVERTSTR(FORMAT(width),',','.') + 
                '*' + 
                CONVERTSTR(FORMAT(height),',','.') + 
                '/100';

MESSAGE('%1', scriptControl.Eval(calcFormula));

chandrurec

Hi IsakssonMi,

In the previous code snippet, you r hardcoing the formula.But I want the formula to get computed dynamic based on what value I give in the CalcFormula text field.

I mean i will be getting three input from the user.

One is for Width,Depth and calcformla.And in calcformula, the user can give any formula based on 2 fields width and depth.

For Ex:

1.width*depth
2.Width*Depth/100
3.(Width*100)/depth

Thanks & regards,
chandru.

Luc_VanDyck

chandrurec wrote:

One is for Width,Depth and calcformla.And in calcformula, the user can give any formula based on 2 fields width and depth.

As forum user ssingla already pointed out, you should look at the functionality which is offered by the Accound Schedules (Financial Management -> Analysis & Reporting). When you setup Column Layouts, you can define your own formula's.

Dynamics NAV online help wrote:

Here you can enter a formula. The result of the formula will appear in the column when the account schedule is printed.

You can use this field to perform calculations on other columns. Other columns are referenced through their column number.

You can use the following symbols:

+ (addition)

- (subtraction)

* (multiplication)

/ (division)

^ (exponentiation)

() (parentheses)

% (percent) – If used between two terms, the result is the same as for / (division), but multiplied by 100. When used at the end of a formula, (where the formula only contains one term) the amount from the most recent line with Totaling Type = Set Base for Percent is used as the second term. The first term is then divided by the Set Base for Percent result. The resulting amount is multiplied by 100, and then displayed followed by the percent sign.

You can enter a maximum of 80 characters, both numbers and letters.

Sog

so you want in calcformula to have
math operatorswidthmath operatorsheightmath operators
and replace it with
math operatorsrec.width.valuemath operatorsrec.width.valuemath operators
then evaluate the parsed calcformula and pass it to either the parser for formula's in NAV or the scriptcontrol?

That is the requirement i see.
But step one (replacing the keyword width and height with the actual values) is never been done. (As far as I can see).
so first prepare your statement before you parse it.

chandrurec

Hi Sog,

I converted the text into value using string functions and then passed the text variable in eval function. Now I am getting the output.

Thanks & Regards,
Chandru.