Aged Accounts Receivable

Business Central/NAV Developers Forum
1

Apologies if this has been covered previously, this is my first post.

I am trying to recreate the Aged Accounts Receviable report in a PHP script, I am able to query the SQL database to acces the tables Cust Ledg_ Entry and Details Cust_ Ledger Entry and it appears that beween the 2 tables I have the correct details, my issue is knowing when I use the values from the detailed table and when not to.

I have been told I need to check the balance at the time the report ends, so I have a report that ends 30th of April so I have the following SQL.

SUM(CASE WHEN([company$Detailed Cust_ Ledg_ Entry.[Initial Entry Due Date] < ‘2011-04-30’ THEN ['Amount (LCY)] END) AS BAL

If the balance is 0 then the customer entry ledger info is not included in the report. This is the point I am going wrong, any help greatly appreciated.

Thanks

2

There is no need of customer ledger table, you can only use the detail table to build the report.

3

Thanks for that, if I stick to the detailed table it doesnt appear to match the hard copy I have from Navision. I am using the following SQL statement for a report ending 30-04-2011 for the current month, most of the time this appears correct but in some cases it doesnt match,

SUM(CASE WHEN ([$Detailed Cust_ Ledg_ Entry].[Initial Entry Due Date] between ‘2011-04-01’ AND ‘2011-04-30’) THEN [Amount (LCY)] END) as currentm

Is this incorrect, am I missing something?

Thanks for you help.

4

Instead of initial entry due date why don’t you try with posting date.

5

The values using Posting Date appear more wrong than when using the initial entry due date. Is there any point that I should take the value from the cust ledge entry table rather than the detailed cust entry table, a combination of them both appear the most correct.

1 example I have gets £205 not due from the detailed table but gets £602 from the cust ledger table, the 602 is the correct one according to the hard copy I have received.

Is there any example code you can give me for creating the report?

Thanks.

6

Have you looked at the code of the actual NAV report to see what fields it is using? I understand you’re doing this in another language, but you should be able to get some information from the report code.

7

You can check navision reports like thr customer trial balance to check the fields used to calculate the balance amount.

8

Sorry, I am really new to this and have never seen Navision in use, how do I access the code behind the reports?

Thnaks

9

You need to design the report (Ctrl+F2) and in there use the C/AL code to see the code.

10

Dear All,

i want to display Aged Account Receivable report in fixed slot wise

0-3days 4-7days 8-12days 13-16days 17-21days above 21days

shall i need to hardcode it or we can do this by standard report.