I'm struggling with a report I'm building for a pharmacy. What we need is a report of all prescriptions that haven't been picked up (or, at least, according to the computer.)

 

Each prescription has a "ticket" associated with it, and all actions on that prescription add another ticket status. The status is designated as a 2-digit code. So, for prescription A1234, it gets entered (NW status, for new), filled (FL), checked (CK), and picked up (PU.) Each of these statuses has sequential number assigned, as follows:

 

 

RX	Status	Tix Seq
A1234	PU	1105491
A1234	CK	1105452
A1234	FL	1104414
A1234	NW	1104411
A1235	CK	1105492
A1235	FL	1105453
A1235	NW	1105418

 

If I group them by RX and then by Ticket sequence, and use the following group selection formula

 

{TICKET.TICKETSEQ} = maximum ({TICKET.TICKETSEQ}, {TICKET.RX})

 

Then I get the following report, pulling only the most recent transaction for each prescription:

 

RX	Status	Tix Seq
A1234	PU	1105491
A1235	CK	1105492

 

My question is, however, how do I get the report to only pull the records where the most recent status is not PU? In the example report, I would like to see only A1235, since it hasn't been picked up, but I would not like to see A124.

 

I'm sure this is easier than I'm making it, but I can't figure out how to make it happen.

 

Can anyone offer some suggestions? And please let me know what other information I should provide...

Thanks so much!