BAQ Calc Field - Oldest Open Invoice Date?

I have a BAQ and I’m stuck on a calculated field. The BAQ has Customers table linked to a subquery called “Oldest Open Invoice Date”. (the link is company, custnum).

What I’m trying to do is display the oldest open invoice date for each customer. If none exists, just display a blank.

Any help would be appreciated.


in your join do an outer join between the tables, that will return every customer even if there is no invoice.

Then to get the oldest, (you might already have this, but this is how you would make it). Do a calculated field like shown below.


Set everything else to group by

Then you should get one line per customer. If there isn’t one, it will be blank.


Thank you. I have basically that now, but the tweak I need is to add some sort of where clause… “min inv date WHERE OpenInvoice = true”

… I’m only interested in returning dates for OpenInvoices.

Something like “min( InvcHead.InvoiceDate WHERE InvcHead.OpenInvoice = 1 )”

…but that works :slight_smile:

Or you can do a field like this. And skip the group by.

Do that it your table criteria.