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

  • Jojop1972 Profile Picture
    Jojop1972 on at
    RE: Replicating the Aged Debt report Receivables in SQL

    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!

  • Verified answer
    Brad_P Profile Picture
    Brad_P 1,549 on at
    RE: Replicating the Aged Debt report Receivables in SQL

    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
    RE: Replicating the Aged Debt report Receivables in SQL

    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

  • Suggested answer
    Brad_P Profile Picture
    Brad_P 1,549 on at
    RE: Replicating the Aged Debt report Receivables in SQL

    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.

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

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Adis Hodzic – Community Spotlight

We are honored to recognize Adis Hodzic as our May 2025 Community…

Kudos to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Microsoft Dynamics NAV (Archived)

Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans