Skip to main content

Notifications

Microsoft Dynamics NAV forum
Unanswered

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

Posted on by 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 ;)

Categories:
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    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.

  • 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 Profile Picture
    Community Member Microsoft Employee on at
    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?

  • 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 Profile Picture
    Community Member Microsoft Employee on at
    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 ...

  • 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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,524 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,469 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans