Logic in SourceExpr

Jensa

Hi,

I am quite new to programming in Dynamics Nav 5.0. I am trying to create a field in the sub-formular for sales order which shown the margin in precentage in relation to Unit Price and Unit Cost, eg. ("Unit Price" - "Unit Cost") / Unit Price. This results in execution error because division by zero is not possible since the user needs to put in the Unit Price before first calculation.

Where do I add this logic ? And how do I get a calc-execution each time the user fills in a new Unit Price ?

B.rgd

Jens

DenSter

You would create a function in the table that returns the value, and you take care of division by zero in that function. The function can be set as the SourceExpr of a form.

Jensa

Thanks for the quick response. I created a function in table 37 (Sales Line) and but got thinking .... do you mean I also should create a column that stores this value or do you mean I just return a decimal from the function ?

Otherwise I don't need the column, isn't it better to create a function inside the form itself that passes two varibles which would be the Unit Price and Unit Cost ?

B.rgd

Jens

Savatage

We Use this..

IF "Unit Price" = 0
 THEN
  PercentMarkup := 0
 ELSE
  PercentMarkup := (("Unit Price - "Unit Cost") / "Unit Price)*100;

DenSter

No the function should be in the table. Give it a name like "MyMargin", and a return value of the type that you need. Now back on the form, you can add a textbox to the form, and set the SourceExpr to "Sales Header".MyMargin. That's it, the system will take care of populating the fields.

tinoruijs

In the example Harry gave you, PercentMarkup should be the Return Value in the function MyMargin on the Sales Header table (like Daniel wrote).

Jensa

Thanks guys....

I added the function to Sales Line (not Sales Header), but I had to add a varible on Form 46 (Sub Order Form) to Sales Line table (Record). I put a text box on the Sub Order Form which gets SalesLine.CalcMarginPercentage()

The problem now is that everything gets valued(evaluted) as zero(no Unit Price set, but it's set on the form), which means it always shows 0%. I assume this has something do to that the current record is not correctly referenced when I added Sales Line on the C/AL globals.

Any hints for a newbi ?

B.rgd

Jens

Savatage

Just as a quick add-on question here - I have noticed a field 67 called Profit%. But it doesn't seem to do anything. Every sales line I have has it as 0 and the sales line table has nothing when I search for "Profit" in any code. What is it used for and if it is used for "nothing" cold it be used for this purpose instead of creating a new field.

Savatage

*Code should be on the table not the form* for many reasons discussed before and I won't get into it here (you can search the forum for that bit of advice)

Also you will have to call it from OnValidate of the"No." field
and again on the Validate of "Unit Price" in case it's manually changed.

Unless you want it to also calculate correctly if an Item discount is entered or some other senarios :-k

Ohh..now reading the other posts I thought this was about adding a Markup PER ITEM. Do you want the Order total Markup?

I know the Profit% field on the Order Statistics (F9) works fine.

DenSter

Jensa wrote:

Thanks guys....

I added the function to Sales Line (not Sales Header), but I had to add a varible on Form 46 (Sub Order Form) to Sales Line table (Record). I put a text box on the Sub Order Form which gets SalesLine.CalcMarginPercentage()

The problem now is that everything gets valued(evaluted) as zero(no Unit Price set, but it's set on the form), which means it always shows 0%. I assume this has something do to that the current record is not correctly referenced when I added Sales Line on the C/AL globals.

Any hints for a newbi ?

B.rgd

Jens

The subform is based on the Sales Line table, so instead of referencing a variable of that table, you reference the table itself. Make the SourceExpr of the textbox "Sales Line".CalcMarginPercentage, and delete the variable that you created. Because the variable does not have actual records, it thinks it is always zero (default value).

Jensa

Thanks!

Now everything works fine, the problem were that you should only write CalcMarginPercentage() in the SourceExpr. It does not work to reference Sales Line in the same form.

It seems to be updated as soon the user changes the price so I can't see any need to add the call on onValidate etc.

How do I format the column to contain a percentage mark,%, ?


B.rgd

Jens

Savatage

you can change your sourceExp to
'%'+FORMAT(yourPercentMarkupfield,5)

Jensa

Now everything is solved.


Thanks everyone.

B.rgd

Jens