Skip to main content

Notifications

Microsoft Dynamics NAV (Archived)

Find "Remaining Amount" through SQL query.

Posted on by 1,835

Dear Experts,

How to retrieve the "Remaining Amount" from "Vendor Ledger Entry" table with help of SQL server. It is a flowfield.
I am trying to retrieve it help of [XYZ Private Limited$Detailed Vendor Ledg_ Entry] table but always comes the wrong value.

Please help me to short this. My sql query is below.

select sum(DVLE.Amount) "Remaining Amount",VLE.[Document No_],VLE.[Document Date],VLE.[Currency Code],VLE.[Vendor No_],[XYZ Private Limited$Vendor].Name "Vendor Name"
from [XYZ Private Limited$Vendor Ledger Entry] VLE
left outer join [XYZ Private Limited$Detailed Vendor Ledg_ Entry] DVLE on DVLE.[Document No_] = VLE.[Document No_]
and DVLE.[Vendor No_] = VLE.[Vendor No_] and DVLE.[Vendor Ledger Entry No_] = VLE.[Entry No_] and DVLE.[Posting Date] = VLE.[Posting Date]
left outer join [XYZ Private Limited$Vendor] on [XYZ Private Limited$Vendor].No_ = VLE.[Vendor No_]
where VLE.[Document Type] = 2 and DVLE.[Document Type]=2 and DVLE.Amount >0
and VLE.[Vendor No_] = 'V-DO-0451' and VLE.[Document No_] ='GV-101-16-11-111'
group by VLE.[Document No_],VLE.[Document Date],VLE.[Currency Code],VLE.[Vendor No_],[XYZ Private Limited$Vendor].Name

*This post is locked for comments

  • Veeravi Profile Picture
    Veeravi 5 on at
    RE: Find "Remaining Amount" through SQL query.

    Hi NAV Beginners,

    how did you solve this isssue we are also trying to get the sql query for vendor and customer with "remaining amount"

    Tks rgd

    Vee

  • manish.yadav Profile Picture
    manish.yadav 1,835 on at
    RE: Find "Remaining Amount" through SQL query.

    Hi Ashwini,

    Thank you for your reply i got a solution.

    Thank you.

    Please close this thread.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Find "Remaining Amount" through SQL query.

    Hi MKY,

    Remaining amount filed is sum index field which is calculated from amount filed ,it wont store in vendor ledger entry table(database) this is virtual filed not real.

    Thank you

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Find "Remaining Amount" through SQL query.

    Thi sblog will inspire you to find the correct SQL query:

    https://blogs.msdn.microsoft.com/melyassir/2009/12/28/astuces-5-comment-rcuprer-la-requte-sql-pour-calculer-les-flowfields/

    The blog is in French and for NAV 2009 ;)

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans