Hello all

I been searching and read many articles on dates and calculations.

My database is designed as follows invoices <- invoice payments -> payments.

When a client pays insert information in invoice payments via portal on invoices layout. Invoice Payments is a portal on invoices. Actual information is inserted into payments table.

Now heres my problem!!!

I want to know when invoices are due after terms either 14 or 30 days. I think my calculation should look at Payment table payment date. If this is blank then refer back to invoices due date if this is more than terms then invoice overdue!

Does the above make sense?

Or is there an easier way??

Look forward to any replies of if you need any further info!

BFGK

Have a look at this. It assumes that if there are no payments, then the invoice is overdue. Therefore, if there is one payment (no matter how small) the invoice will not be overdue. This is what you describe as your business rule in your post. However, you could change the calc so that if there is a balance due past the payment due date, then the invoice is overdue.

In the list view, I show two techniques for drawing attention to overdue invoices. One displays the flag field as a checkbox. The other puts a graphic hilite behind the list row.

Disclaimer: This is quick and dirty, so ignore the design, please! Also, I have "allowed creation of records on" in the portal relationships, something I never do. I prefer scripting the creation of child records.

InvoiceExample.zip

Thanks bcooney

I will take a look and get back to you shortly!

BFGK

Thanks bcooney

It worked!

I adapted flag_paymentoverdue calculation and inserted this in my invoice table.

Created a script to search on flag = 1 to display overdue invoices.

Thanks.

BFGK

Hello all

I've been working on creating a count field thats displays the number of days invoice due.

I have tried using the flag_paymentoverdue and searched forum on date function with no luck.

Any ideas i will keep working on an answer!

BFGK

The number of days until an invoice is due is your "terms" field, correct? That is not a calc field, but a plain number field.

Given that, you are looking for a calculation that subtracts the date due from today's date, right?

DaysUntilDueDt = DateDue - get (currentdate), result is number

DateDue is a calc field that equals InvoiceDT + Terms, result is date.

Thanks again you've solved it!!!