Suppression of records by priority

[spcc07]

I am using Crystal XI, Oracle database.

I have a list of AT codes that should take priority when a person has more than one record (i.e. they have more than one AT code).

AT CODE PRIORITY
FTB takes priority over XBB, XTF, XCC.
MBB takes priority over MTF.
MTF takes priority over MCC and WRS.

DATA
ID AT Code
11111 FTB
22222 MBB
22222 MTF

Output should be:
ID AT Code
11111 FTB
22222 MBB



Thanks.

 

[lbass]

You could create a formula like this:

select {table.ATCode}
case "FTB" : 1
case "MBB" : 2
case "MTF" : 3

Use this formula as your sort field. Then insert a group on {table.ID} and drag the AT Code into the group header and suppress the detail section.

-LB

 

[spcc07]

If there is more than one AT code for an ID then I want to suppress one of the records.

If either the first or second record, for the ID, is MBB I want to suppress MTF.

Also, if one of the records is MTF and the other record is either MCC or WRS then MTF takes priority.

Thank you lbass for your post.

 

[lbass]

Please explain why you can't use my suggestion.

-LB

 

[spcc07]

FTB takes precedence over all men’s sports (usually that will only mean MBB, MTF, WRS, BSB and MSC).
MBB takes precedence over MTF.
MTF takes precedence over MCC and WRS.

WBB takes precedence over all women’s sports (usually that will only mean WTF and SFB)
WTF takes precedence over WCC.

This is the whole priority chart. There are three AT codes that overlap: I don't want MBB or MTF to show up if FTB is one of the codes. Then, if MBB is present I don't want MTF to show. Then, if MTF is one of the codes then I don't want MCC or WRS.

If I list the code that I want to take priority do I have to list all of the codes that take lesser priority?

 

[lbass]

I think my suggestion should work. I'm not sure how the women's sports fit in. Are they included in the same report? How do they relate to the men's sports?

-LB

 

[spcc07]

This is a report of athletes with their AT Codes. They may play more than one sport (AT Code).

 

[lbass]

You have not responded to my questions. I've understood that there are multiple AT codes from the very beginning.

-LB

 

[spcc07]

Thank you lbass. This works.