Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Sales Line Outstanding...
Microsoft Dynamics NAV (Archived)

Sales Line Outstanding Amount (LCY) Ex Vat

(0) ShareShare
ReportReport
Posted on by Ian Fletcher 2,445

Hi, we are using Nav 2009 R2, I am trying to create a report which is a pipeline or forecast or Sales Order report, I need to report on what is still to ship from a Sales Line by Value, but I need this to be (LCY) and ex vat, there does not seem to be a standard field to show this.

Can someone either help me to say if this is possible in the Sales Line Table or if I am using Jet Essentials how I can calculate this Value?

The Outstanding Amount (LCY) field contains VAT, as otherwise this would be what I require, so ideally this field minus VAT, so I cant work out how to get this Value?

Thanks

*This post is locked for comments

  • Ian Fletcher Profile Picture
    Ian Fletcher 2,445 on at
    RE: Sales Line Outstanding Amount (LCY) Ex Vat

    Thanks Both,

    With some help from Jet Reports themselves, I have managed to create an IF formula in the Jet Report that calculates the value depending on the value in the VAT % field, this appears to be what I require & the report is now working as expected. My partner is also reviewing this, so I will see what they come back with, in regards to adding this field into Table 37 as per your suggestion, as it can be used it future reports & will also improve reporting speed, but for now I appear to have the answer that I need.

    As Jens said, I could not use the VAT Base Amount field due it not always containing a value.

    Thank you for your feedback

    Ian

  • Suggested answer
    Jens Glathe Profile Picture
    Jens Glathe 6,092 on at
    RE: Sales Line Outstanding Amount (LCY) Ex Vat

    Hi Jonathan,

    this only works for orders that have been released, otherwise this field will be empty. :)

    Regarding the original problem, I would suggest to add an extra field and wire it into the C/AL logic of table 37. This way you actually can fetch the info with Jet Reports, I have my doubts if you can do this otherwise with Jet.

    You can derive the new "Outstanding Amount excl. VAT (LCY)" field in the function InitOutstandingAmount(). In this function, AmountInclVAT is calculated. You can calculate _Amount (new variable) by doing the opposite of what is done for AmountInclVAT.

    At the end of the function you need to fill in the new "Outstanding Amount excl. VAT (LCY)" field like "Outstanding Amount" is filled, but additionally convert it into LCY (like it is done in the OnValidate trigger in "Outstanding Amount".

    You need to have a developer (Application Builder at least) license to do it, though. Your NAV partner should be able to do it in relatively short time.

    with best regards

    Jens

  • keoma Profile Picture
    keoma 32,675 on at
    RE: Sales Line Outstanding Amount (LCY) Ex Vat

    try "VAT Base Amount" * "Oustanding Qty." / Qty.

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,711 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,458 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans