BAQ Calc Field - Oldest Open Invoice Date?

[Adam (Adam Szymanski)]

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.

Thanks.

[Banderson (Brandon Anderson)]

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.

[Adam (Adam Szymanski)]

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.

[Adam (Adam Szymanski)]

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

…but that works

[Banderson (Brandon Anderson)]

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

@Adam

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”

Do that it your table criteria.