Formula Needed for newbie

[tkschief]

I have a crystal report that displays the charging attys and charging staff on a case for a received referral event if their assignment date is null grouped by Last_Name and then by Case_Number. In some instances, both a charging atty and charging staff are on a case. In this situation, we only want the case to appear under the charging staff group. Can you help me out? I'm not sure how to write a formula to exclude the case displaying for the charging attorney. If you require more detail, please let me know.

For Example:

Case #AD-2010-01109 has the following charging personnel:

Jane Fawn Assign Date: 08/10/09 Assign End: (null)
John Doe Assign Date: 08/15/09 Assign End: (null)


Current Report:

DOE:
AD-2010-01109 Assign Date: 08/15/09 Assign End: (null)

FAWN:
AD-2010-01109 Assign Date: 08/10/09 Assign End: (null)

 

[lbass]

What is the field that tells you whether the employee is an attorney or staff? I don't see it here.

-LB

 

[tkschief]

I've used a group selection formula to bring that information in: {L_ROLE.ROLE_NAME} in ["Charging Attorney", "Charging Staff"] - which being a newbie may not be the best route to go?

 

[lbass]

And what is the full field name for case #? How is the case table linked to the l_role table?

-LB

 

[tkschief]

The full name for the case number field is T.Case_Case_Num.

The L_Role table is linked to the T_Case_Employee table via Role_ID and the T_Case_Employee table is linked to the T_Case table via Case_ID.

 

[lbass]

Insert a SQL expression {%maxrole}:

(
select max(`ROLE_NAME`)
FROM L_Role A, T_Case_Employee B, T_Case C
where A.`Role_ID` = B.`Role_ID` and
B.`Case_ID` = C.`Case_ID` and
C.`Case_Num` = T_Case.`Case_Num`
)

Adjust the punctuation to match that of your datasource and then go to report->selection formula->record and enter:

{L_Role.Role_Name} = {%maxrole}

This should return only one role name per case number (and thus only one person should appear per case.

-LB