Determine criteria used by the built-in Sage Aged Overdue Sales Invoices Summary report...

Question

I need to find out what internal criteria is used by the Sage Aged Overdue Sales Invoices Summary report. I'm trying to build a list of customers that show up on this report and automatically export it for other purposes. I don't want to have to go in and run/export/etc the report. I just want the criteria used by this report so I can hit the table directly in the db and get a list of customers. Is there anywhere that I can see the table/criteria being used to generate this report? 
 -Jason

Richard S. Ridings · Accepted Answer

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.

Sage 50 Canada

Determine criteria used by the built-in Sage Aged Overdue Sales Invoices Summary report...

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING