Overdue Invoice Chasing

[Rufus1983]

Hi,

We are using Sage 50 Accounts Professional 2013 (v. 19.01.11.285) with Windows 7 Professional (64-bit).

I am trying to set up reminder invoices for when they become due, I have managed to set up customer reports (amended from default Customer Invoices Overdue report) which list invoices due on any given date using the following filters (with account status being used to denote if their payment is due from invoice date or from period end):

Invoice Date:
SALES_LEDGER.PAYMENT_DUE_DAYS <> 0 AND AUDIT_HEADER.AGED_BALANCE <> 0 AND ((AUDIT_HEADER.DATE + SALES_LEDGER.PAYMENT_DUE_DAYS) = AUDIT_HEADER.DATE_TO) AND SALES_LEDGER.STATUS_NUMBER = 0

Period End:
SALES_LEDGER.PAYMENT_DUE_DAYS <> 0 AND AUDIT_HEADER.AGED_BALANCE <> 0 AND ((CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE),DaysInMonth(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE))) + SALES_LEDGER.PAYMENT_DUE_DAYS) = AUDIT_HEADER.DATE_TO) AND SALES_LEDGER.STATUS_NUMBER = 1

These filters work and the reports return the invoice numbers required, however I have been unable to recreate these filters in the invoice report or figure out a way of importing the results so that only required invoices are generated.

Can anyone help me with this?

Thanks

[brucedenney]

You would need to link the tables into the report as they are not part of the data for that report.

[Rufus1983]

Thanks Bruce, I have tried linking the tables, but I'm not sure which fields to join on - when I have tried I end up getting no data for the report, so I assume that I'm doing something wrong...

[Rufus1983]

Ok, I've managed to get this working after a fashion; however, I now have to go into report editor and amend the date in the filter manually, i.e.

AUDIT_SPLIT.AGED_BALANCE <> 0.00 AND STATUS_NUMBER = 0 AND (AUDIT_HEADER.DATE + SALES_LEDGER.PAYMENT_DUE_DAYS) = "2014-05-09"

I have tried using the NOW() function, but get the error message "The report could not be generated. The given key was not present in the dictionary" and I have tried setting AUDIT_HEADER_DATE as a criteria and using the CRITERIA.AUDIT_HEADER_DATE_TO field to filter, but this returns the error message "The report could not be generated. A criterion or filter references a table which is not used elsewhere on the report (CRITERIA). To solve this, add a field from CRITERIA to the report (and set Suppress Printing=True to hide it)." I have tried the 'solution' given on the error message, and this does not make any difference.

Any suggestions on how to get around this?

[brucedenney]

I am a bit of a loss as to why you are trying to do this, this way.

The way I would approach it is to just list the outstanding header transactions (PAID_FLAG) from the audit trail and use the AUDIT_HEADER.DATE_DUE as a criteria.

I can only imagine that you have transactions where the DATE_Due field is not being set correctly for some reason. I would fix that issue rather than trying such a complex route to producing what seems a simple report.

The Now() function returns a date and time, if you want just the date then you need to use the integer of it.

You can also use the report date e.g. REPORT.DATE - AUDIT_HEADER.DUE_DATE will give you the number of days overdue a transaction is.

[Rufus1983]

What I am trying to do is get a batch report set up to print the daily invoices on the following schedule:

Invoice (next day)
Invoice Reminder (due date)
First Overdue Reminder (7 days overdue)
Second Overdue Reminder (14 days overdue)
Final Overdue Reminder (21 days overdue)

So if I were to run the report tomorrow, it would generate the following invoices: Invoice (13/05), Invoice Reminder (14/04), First Overdue Reminder (07/04), Second Overdue Reminder (31/03), Final Overdue Reminder (24/03).

I have amended the Customer Invoices Overdue report to generate the required invoice numbers, however we then have to select each invoice and print on the correct layout manually, which can be quite time consuming.

My main problem in trying to get this working is to do with the getting the filter to calculate today's date or recognise entered criteria date (the second would be preferable as would be easier to generate weekend invoices); I have been able to make the report work (although I have not created a batch report for this yet) by manually entering a date into the filter, however due to the number of different reports and the fact that none of my colleagues have any experience working with report designer this is not really a suitable option going forwards.

With regards to the due date, we have a number of customers that have 30/60 days from end of month, rather than from invoice date. As Sage calculates due date as invoice date + payment due days, this will be incorrect for a lot of our customers and would lead to incorrect reminders being sent.

I hope that this clears up what it is I'm trying to do and why I am trying to use this method

[brucedenney]

I think the issue is that the filter is dependant on the customer and their trading terms, so you can't filter the record out until you have looked at it to work out if it is due or not.

[Rufus1983]

Hi Bruce, I'm not sure what you mean - can you give me a bit more detail please?

[brucedenney]

I am not sure I know what I meant.

I guess you could do it
calculate the "days past due" on each line for a customer
find the max value of the "days past due" in the customer footer
Then dependant on the max number of days past due conditionally print a footer block containing the text for that "day past due".

You might get blank pages where nothing was needed... try it and find out

[Rufus1983]

I now have this working almost perfectly, the only issue is when invoices fall due on a non-working day - but this is easily circumvented by adjusting the system date before running. If you're interested, I can send you the layout files?

[brucedenney]

add them to your post.

[Rufus1983]

There are two different layouts - one for email, one for printing on our headed paper - and I have stripped out the company information. If you have any recommendations for tweaks/amendments then I would be happy to hear them

For reference, we are using account status 0 for customers paying from invoice date and account status 1 for customers who pay from end of month.