Formula/Group Selection Help Requested

[tkschief]

Hi - I'm hoping someone can provide some help with this as I am a beginner CR user. Here's my dilemma:

I’m using this formula (AssignAttyTrialDate) to identify which attorneys were assigned when the eventdate occurred.

If date({T_EVENT.EVENT_DATE_TIME}) in minimum ({T_CASE_EMPLOYEE.ASSIGNMENT_BEGIN_DATE}) to maximum ({T_CASE_EMPLOYEE.ASSIGNMENT_END_DATE}) then 1

1. I’m not sure how to adjust my formula to take into account if the T_CASE_EMPLOYEE.ASSIGNMENT_END_DATE is null (which it frequently is).
2. My report is also duplicating the data if there is more than 1 assigned attorney.
3. If there are more than 2 attys during the Event Date, how do I get both of their initials into the report without duplicating the data: TKN/JAK

I tried a Report Group of sum({@AssignAttyTrialDate},{T_CASE.CASE_NUM})=1 to get rid of the duplicate records but I get the message that @AssignAttyTrialDate can’t be summarized. When I click on @AssignAttyTrialDate in the report it does say it is a number.

Basically, what I’m trying to do is have my report return the Assigned Attorney’s initials for whose assignments were active during the EventDate. EventDate is in T_Event and Assigned Atty Begin and End Assignment dates are in T_CASE_Employee. If there are 2 attorneys assigned when the eventdate occurred, I would like their initials returned as TKN\JAK. Please see further details below:

Example 1 Details:
Case ID Assign Atty AssignBegin AssignEnd EventDate
12345 TKNormal 04/17/09 02/02/10 04/23/10
12345 JAKason 02/02/10 06/30/10 04/23/10

Expected return:
Case ID Assign Atty EventDate
12345 JAK 04/23/10

Example 2:
Case ID Assign Atty AssignBegin AssignEnd EventDate
12345 TKNormal 04/17/09 05/23/10 04/23/10
12345 JAKason 02/02/10 04/23/10

Expected return TKN/JAK.
Case ID Assign Atty EventDate
12345 TKN/JAK 04/23/10

Example 3:
Case ID Assign Atty AssignBegin AssignEnd EventDate
12345 TKNormal 04/17/09 04/23/10
12345 JAKason 02/02/10 04/23/10
12345 KSFind 03/24/10 04/22/10

I maybe didn't explain this clearly so please ask questions if you need more information or details. Thanks very much!

 

[Madawc]

I'm not sure how to adjust my formula to take into account if the T_CASE_EMPLOYEE.ASSIGNMENT_END_DATE is null

Create a formula field,

if isnull({T_CASE_EMPLOYEE.ASSIGNMENT_END_DATE}) then Currentdate
else {T_CASE_EMPLOYEE.ASSIGNMENT_END_DATE}

My report is also duplicating the data if there is more than 1 assigned attorney

Group by Case ID. Display data in the group header or footer. Suppress detail lines.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP