How to do some calculations with the previous/subsequent line?

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hey guys,

I would like to do the following:

I have an ordinary Sales Order, which consists of 5 lines.

In the Sales Line table I have added two new fields: Amount & Amount including extra charges.

Ih the first line Amount is 100 and "Amount including extra charges" is 100,too.

It is also 100, because in the first line Amount and "Amount including extra charges" arel always the same.

Now in the second line "Amount including extra charges" is calculated out of "Amount including extra charges" from the first line (so 100) and 70 from Amount from the second line (given by the user)

So "Amount including extra charges" in the second line will be 170 and that logic should be the same for all other lines.

How can I achieve this?

Thank you ;)

All responses (6)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: How to do some calculations with the previous/subsequent line?

I put a MESSAGE() on the OnValidate trigger and the message is executed, but there is no calculation..."Amount including extra charges" stays in all lines empty.
Lars Lohndorf-Larsen on at

Like (0)

Report

RE: How to do some calculations with the previous/subsequent line?

That's strange. I would put a MESSAGE('I am here.'); on the OnValidate trigger, just to check that it runs at all. Also restart NST in case it is running on Object Cache.
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to do some calculations with the previous/subsequent line?

That's how I did it too, but unfortunately nothing is calculated ...Maybe your code has to be wrapped in a function and should be called in the OnAfterGetRecord trigger?
Lars Lohndorf-Larsen on at

Like (0)

Report

RE: How to do some calculations with the previous/subsequent line?

Hello,

I put it on the Sales Line table, on the new field MyAmount OnValidate trigger, so that the code runs when I enter MyAmount. I also added the two fields to page 46 "Sales Order Subform". I hope that shows something?
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to do some calculations with the previous/subsequent line?

Many thanks for your great help ;)

Did you integrate the code in the Sales Line table or on one of the pages? I just tried the code in the Sales Line table and unfortunately nothing happened ...
Lars Lohndorf-Larsen on at

Like (1)

Report

RE: How to do some calculations with the previous/subsequent line?

Hi,

I gave this a try and its actually a bit tricky, depending on the exact requirements. I added these two new fields to table 37:

Field No. Field Name Data Type
50100 MyAmount Decimal
50101 Amount including extra charges Decimal

And then the logic to calculate the second field:

MyAmount - OnValidate()
RunningTotal := 0;
SalesLineRunningTotal.SETRANGE("Document Type","Document Type");
SalesLineRunningTotal.SETRANGE("Document No.","Document No.");
IF SalesLineRunningTotal.FINDFIRST THEN BEGIN
WHILE SalesLineRunningTotal."Line No." < "Line No." DO BEGIN
RunningTotal := RunningTotal + SalesLineRunningTotal.MyAmount;
SalesLineRunningTotal."Amount including extra charges" := RunningTotal;
SalesLineRunningTotal.MODIFY;
SalesLineRunningTotal.NEXT;
END;
"Amount including extra charges" := RunningTotal + MyAmount;
END;

First I tried with a simple REPEAT UNTIL loop, but I found that when trying to modify the current line, it went into a lock. Then there is the situation that when you are inserting a line, you do not know what the "Line No." will be - users could be inserting a line in-between existing lines. This example does not accout for that situation. And what if a user edits the lines and updates MyAmount. So calculating this live while users are editing the order and adding lines, I think is complicated.

I think the only safe way to implement this, would be to have it as an action that the user can run to update the amounts. Just like "Calculate Invoice Discount". And of course you can run this when posting the order, in case users forget.