crystal reports if then else formula with multiple records

Question

I have a table that has multiple records for each job #.

Job#|Status|Material

17001 O 1
17001 O 2
17001 S 3
17001 O 4
17001 C 5
17001 C 6
17002 S 1
17002 O 2
17002 O 3
17002 C 4
17002 C 5

What I am trying to achieve is to have one line in my report for each job that has a status listed. I need to evaluate all lines for each job and if there is one with an "O" list the job as "not ordered" if no "O" records for that job are found then look for "S" and if found list job as "ordered" if no "O" or "S" records found look for "C" and if found list job as "Received". I have the following formula set as a field in my report.

if isNull ({Material_Req.Status})
then "Not Ordered"
ELSE If {Material_Req.Status} = "O"
then "Not Ordered"
ELSE If {Material_Req.Status} = "S"
then "Ordered" ELSE If {Material_Req.Status} = "C"
then "Received"

I have this field on a group footer but it returns "Received" when there are "O" or "S" records present. It works perfect if there is only one record for each job, but not when there are multiple records for each job.

Any help would be appreciated.

Answer 1

That sounds like a minimum function to me. Maybe you can try to substitute your status with a number (O=0, S=1, C=3) and then select the group minimum of that number.