web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Replicating the Aged D...
Microsoft Dynamics NAV (Archived)

Replicating the Aged Debt report Receivables in SQL

(0) ShareShare
ReportReport
Posted on by Jojop1972

Hello

My client has asked me to replicate the Aged Debt report receivables report in SQL as the current NAV report they use does not include all the information they need.  

I have located that the report uses the Customer Ledger and Detailed Customer Ledger tables.

I have also received the C/AL code behind the report from our NAV vendor.  The C/AL code references CALCFIELDS "Remaining Amt." which I am informed by my vendor this is what's needed to calculate the balance for a customers aged debt.  However, my vendor is unable to provide me with the calculation and criteria behind this calcfield to calculate the remaining amt.  I need to replicate the CALCFIELD "Remaining Amt." in SQL to be able to complete the report. I would be very grateful if someone could provide me with the calculation or advise me whether replicating the Aged Debt report is possible in SQL.

Many thanks in advance.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Brad_P Profile Picture
    Brad_P 1,549 on at

    I will assume that you're familiar with the table/company naming of the SQL tables behind the Dynamics NAV tables. Something like this should help you:

    SELECT SUM([Amount]) FROM [<company name>$Detailed Cust_ Ledg_ Entry] DCL WHERE CL.[No_] = DCL.[Entry No_]

    CL is the Customer Ledger Entry table. You basically link the Detail Cust_ Ledg_ Entry table to the Customer Ledger Entry table by Entry No.

  • Jojop1972 Profile Picture
    Jojop1972 on at

    Hi Brad,

    Thanks for your reply.

    Yes I've joined together the tables via entry number and sum the amount column for the detail ledger.  This query works for the majority of customers and I am able to match up the balance in the NAV report.  As I can't match the balance for all the customers I am wondering if there is any additional criteria that is required for the calculation?

    Many thanks

  • Verified answer
    Brad_P Profile Picture
    Brad_P 1,549 on at

    It sounds like an issue where you are linking the Customer to the Customer Ledger Entry. You'd want to link the [<>$Customer].[No_] to the CL.[Customer No_] to get the ledger entries. Receivables is based off the "Bill-to" Customer, not the Sell-to.

  • Jojop1972 Profile Picture
    Jojop1972 on at

    Hi

    Thanks so much for your help. Have tried this just now and the problem has worked, I had indeed linked to the sell-to-customer and not the bill-to.  Thanks again for your time helping me with my problem - it's much appreciated!

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

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November 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