I'm looking for which tables to use for creating a report or query to calculate the average days to pay per client. Any feedback is appreciated.
I'm looking for which tables to use for creating a report or query to calculate the average days to pay per client. Any feedback is appreciated.
In AR_Customer:
AvgDaysPaymentInvoice
As Kevin M. illustrated below, Sage does calculate the average days to pay for you. I am not sure how it calculates the number of days (black magic). I have seen the number go south on occasion. If you need more granularity, the AR_Cashreceiptshistory table is a good table to look in. HOWEVER, it does not store the invoice date in the table, making a days to pay number impossible to calculate. To remedy, add an Invoice Date UDF to it and pull the invoice date from the Cash receipt detail table. And while you are doing this, consider adding a Comments UDF as well, pulling the invoice comments field from the Cash receipts detail table. You can then query the CashReceiptsHistory table for the information (calculating the days to pay by invoice using the deposit and invoice dates). This provides information to analyze payment trends. Having the comments may provide insight regarding any payment issues (if the person posting cash receipts enters comments).
BTW, be sure to set your cash receipts history retention (AR Setup Options) to retain adequate history.
There is a KB article on the Sage calculation: https://us-kb.sage.com/portal/app/portlets/results/viewsolution.jsp?solutionid=224924450059262&page=1&position=0
I don't read code very well but it gives me the impression that it only looks at the last 20 invoices? Is that a correct reading?
*Community Hub is the new name for Sage City