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
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
Hi Ashwini,
Thank you for your reply i got a solution.
Thank you.
Please close this thread.
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
Thi sblog will inspire you to find the correct SQL query:
The blog is in French and for NAV 2009 ;)
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,240 Super User 2024 Season 2
Martin Dráb 230,149 Most Valuable Professional
nmaenpaa 101,156