web
You’re offline. This is a read only version of the page.
close
Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
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

I have the same question (0)
  • 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,729 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

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Mansi Soni – Community Spotlight

We are honored to recognize Mansi Soni as our August 2025 Community…

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans