You might as well start with the DataDict.pdf in the Manuals folder of the program installation directory.  You are looking for the tables tcustomr, tcustr and tcustrdt to get all the information for that report.

Hi there. Yes, I pull data from tcustomr, but I don't see anything in tcustr/tcustrdt that will tell me which customers qualify for the Sage Aged Overdue Sales Invoices Summary report. Is there any way to look at the criteria being used by a built-in report?

JasonCC said:

Is there any way to look at the criteria being used by a built-in report?

As far as I know, Sage does not publish their programming code.

You have to look at he problem you are trying to solve and use the logic to get there.  Their report is a template but you have to define the logic you want.

Any customer that owes money should be on the report.  Therefore you have to figure out who owes money.  You have to build what you want based on what your criteria are (exactly what pieces of information do you want to show).

For example, I would start with which invoices are still owing and what is the customer name?  One approach is if the sum of the invoice (a positive number in tcustrdt) and the payment(s) for that invoice (negative numbers using the same tcustrdt.lCusTrId) is not equal to zero, then it is either owing or a credit on the account.  You then have to decide if you want both or just the amounts owing.  If the sum is zero, the invoice is paid and should not be on the report.  As always in bookkeeping, dates are very important to reporting as well.

Then you have to look at the Net Days owing and the invoice date to figure out how old it is.  Doing math on those numbers and the date the report is run will give you a negative if it is still under the Net Days and a positive if it is overdue.  As I said, this is one approach and you may choose to do your math differently but as long as you understand it and it gets the answers and format you want, then it's ok to do it differently than I have indicated.

There is more to it all than this but this is the basic theory you are looking for when reviewing the design of this particular report.  You can use most of this theory for the Customer Aged Summary/Detail reports but only do the math on the report date and invoice date, and ignore the Net Days (unless you wish to include a column in the detail showing the due date).

As you indicated, all you want is the list of customers who owe money.  So you don't need to get into displaying lots of data, just display the customer name in whatever program you are using to access the database, after you figure out if an invoice is still owing.

Thank you, this pointed me in the right direction. Here is the query I ended up with in case anyone is looking for this in the future. It just pulls Customer Name (but it could pull anything, really) and Amount due, where the total Amount in the customer transaction table is not zero. This appears to match the list of Customers (and Total Amounts) from the Sage Aged Overdue Sales Invoices Summary report.

SELECT sName, ROUND(SUM(dt.dAmount),2) AS Amount
FROM tcustr AS tr
LEFT JOIN tcustrdt AS dt ON tr.lId = dt.lCusTrId
LEFT JOIN tcustomr AS cust ON tr.lCusId = cust.lId
GROUP BY cust.sName
HAVING ROUND(SUM(dt.dAmount),2) <> 0

Based on your requirements of only needing to know which customers still owe money, your SQL looks good.  Bear in mind because you did not include any date parameters, any possible future transaction will be included.

What do you mean by future transaction? I want this to update as time goes on and new customers have outstanding balances. Is that what you're referring to?

JasonCC said:

What do you mean by future transaction?

Literally anything posted with future dates.

If you run the report today, Jan 19/23 and a customer named ABC Company has an invoice post-dated to Jan 31/23 already posted, then it will be picked up by your SQL statement because it will be in the tables.

Knowing nothing about your business, I can't say for sure if you post-date entries but because you posted the code for others to see and use, I thought it best to mention it.  Some companies may be posting their rent/lease/monthly service fee charges for Feb 1/23 right now to mail out.  If they ran your code, those invoices will be included if they are not already paid, but they may only be interested in the invoices from the past and if they are paid.

Therefore your SQL code as is, is not suitable for that purpose.  It would need to be modified with a date qualifier based on either the tcustrdt date of transaction or the tcustr date of transaction depending on need.

If you run your Aged Overdue Receivables report with an end date of Dec 31/22, you will see your code does not match if your database has invoices/payments entered in Jan/23.

I've seen the example myself, and there are many, many posts on forums where people are using code from "somewhere" and complaining that it doesn't work because they don't understand how it works.  I thought it prudent to mention the specifics of the lack of date-sensitivity and that it may not be suitable for all uses.  It is suitable for your use because you created and tested it for your purpose.  Based on the code you created from an English description, you obviously understand it's intent.  Others may not.

OK, thanks. I don't think that will be an issue. There aren't any future dates in any of the customer transaction tables. I don't believe it's used that way. However, if this is an issue, I could always just use:

SELECT sName, ROUND(SUM(dt.dAmount),2) AS Amount
FROM tcustr AS tr
LEFT JOIN tcustrdt AS dt ON tr.lId = dt.lCusTrId
LEFT JOIN tcustomr AS cust ON tr.lCusId = cust.lId
WHERE tr.dtDate <= GETDATE()
GROUP BY cust.sName
HAVING ROUND(SUM(dt.dAmount),2) <> 0

WHERE tr.dtDate <= GETDATE()

In English this says where the Invoice/Deposit date is less than the date selected and show me customers with amounts owing.

If you enter a date like Dec 31/22, then if a payment is applied on Jan 15/23, the invoice will not be included because it is paid.

If you wish to see payments applied as of the date included in that date range, you would use

WHERE dt.dtDate <= GETDATE()

I just tested your code on my own file where I posted a payment today for a Dec/22 invoice.  Using today's date for GETDATE(), I don't get that customer owing with the tr.dtDate but I don't get it owing when I enter Dec 31/22 either.

Using dt.dtDate and Dec 31/22 I get more customers owing because they paid their invoices in Jan/23.

Again, it depends on which logic you need that determines if you use the tr date or the dt date.

That's fine. I changed it to use the detail table's date instead of the header table's date. For me, it produces the same result, since we have no dates in the future.

That's fine. I changed it to use the detail table's date instead of the header table's date. For me, it produces the same result, since we have no dates in the future.