Select (or hide) based on records in a group 1

[VE]

I have a table that has an order number, a date when the status was changed and what the status was previously. One order can have several status changes.

I need to be able to only show orders where within the order group there is a record at any time showing it as unfilled and the most current record is closed.

is that possible? Or am I just going about this the wrong way?

So it would show this group

Order123 unfilled 03/20/2011
Order123 inquiry 03/21/2011
Order123 Closed 03/22/2011

but not this one

order456 Inquiry 03/20/2011
order456 Active 03/21/2011
Order456 Closed 03/22/2011

I've tried googling this but I'm not coming up with the right words.

Thank you
VE

 

[lbass]

Create a formula {@unfilled} like this:

if {table.status} = "unfilled" then 1

And another one {@closed}:

if {table.status} = "closed" then
{table.date} else
date(0,0,0)

Make sure the case matches that in the data. Then insert a group on order and go to report->selection formula group and enter:

sum({@unfilled},{table.order}) <> 0 and
maximum({@closed},{table.order}) = maximum({table.date},{table.order})

If you then need to do any calculations across orders, you should use running totals, since non-group selected records would still contribute to the more usual summaries.

-LB