An automated notification script for tracking overdue/late invoices/payments

[buckbuck]

Hello

 

I have an Invoice database for which I am trying to create an automated notification function much as one might have a calendar function reminding one of meetings in an email program. I am hoping this notification function will scan my Invoices on a daily basis and report all Invoices that are termed "over due".

 

Overdue equals Date Shipped (Date) + Terms (number) is greater than 21, then 42 then 63 (days). Meaning the script will function, unless the field Payment Status ≠ "Paid", and/or when the Not IsEmpty ReInvoiced Date (field). 

 

I say and/or since if Not IsEmpty Reinvoice Date and field Payment Status ≠ "Paid" the alert needs to run again at 42 days and 63 days. Not IsEmpty indicates I have responded to the prior Notification by emailing a ReInvoice. 

 

The corresponding would be true for ReInvoice Date 2 at 42 days and ReInvoice Date 3 at 63 days.

 

Probably the best way to trigger this script would be when the "Orders" layout is loaded - this is the root layout to all other Order layouts (Invoice, ReInvoice, ReInvoice 2, ReInvoice 3, Paid, etc.).

 

It seems to me this script is quite involved and might require too much of your time to write. If you have ideas how to structure the script this input would be welcomed.

 

Thanks

Matthew

[bcooney]

There are several ways to go about this. One way is to create a couple of more fields (or a child table of notifications) that timestamp your notifications (FirstNoticeSent, SecondNoticeSent, etc). This will allow you to construct a "flag" field which is a calculation that results in "1" if this Invoice requires action.

 

For example, flag_NeedsNotice =1 if Overdue (you define overdue) and its been X days since last notice.

 

As for sending the notices, that could be "manually" done by user clicking a button that starts a script to find invoices that require action or you could have a server side script that runs daily, finding and sending emails without user involvement.

 

hth,

Barbara