count multiple tasks for an action

[atthevilla]

In my database, I have Prospects, Actions, Tasks, and Managers.

One fundraising action for a prospect may have many tasks assigned, each task to a different manager. I need to determine how many actions are 'joint' (e.g., one action is a visit to a prospect and two managers go on the visit). So it's a count of how many actions have more than one task assigned to it.

Because the report is grouped by manager, the same action may be repeated in each group so the Next function won't find the repeat. Ideally for each Manager group I need to create a YN flag to indicate 'joint.' Is this even possible?

 

[dgillz]

Please post some sample data, desired results and your version of crystal.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[atthevilla]

Prosp Action Task Manager Date Joint

Ron Contact Visit Alice 01/05/07 N
Joe Contact Visit Alice 02/15/07 Y
Joe Contact Visit Alice 03/01/07 N
Joe Contact Visit Joyce 05/01/07 N
Joe Contact Visit Joyce 05/31/07 N
Ron Contact Visit Sam 01/05/07 Y
Ron Contact Visit Sam 06/11/07 N

Crystal 10, Oracle. Report is grouped alpha by Manager. Alice and Sam both visited Ron on the same day 01/05/07 so that visit is joint. The last column is what I need to calculate.

 

[dgillz]

This doesn't make sense:

Alice and Sam both visited Ron on the same day 01/05/07 so that visit is joint

Prosp Action Task Manager Date Joint

Ron Contact Visit Alice 01/05/07 [red]N[/red]
Joe Contact Visit Alice 02/15/07 Y
Joe Contact Visit Alice 03/01/07 N
Joe Contact Visit Joyce 05/01/07 N
Joe Contact Visit Joyce 05/31/07 N
Ron Contact Visit Sam 01/05/07 Y
Ron Contact Visit Sam 06/11/07 N

So why is the red highlight above not a Y? And why is the next record a Y when there is only one date of 02/15/2007 in the entire recordset? Please explain.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[Madawc]

If you group by 'prospect' and then by 'action', you should be able to do a summary count. Or use minimum / maximum, to avoid counting cases where it is the same manager twice.

You could even group by 'prospect', then 'action' and then 'manager'. Something on those lines, though you are unclear about what you want.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I think you simplified your data in your sample. Do you have an ID field for an action? If so you could create a formula like this. Let's assume the ID is a number.

whileprintingrecords;
stringvar actionid;
stringvar joint := "";

if instr(actionid, totext({table.actionID},0,"")) = 0 then (
actionid := actionid + totext({table.actionID},0,"";
joint := "N"
);
if instr(actionid, totext({table.actionID},0,"")) <> 0 then
joint := Y;
joint

-LB

 

[atthevilla]

I did simplify my data and made a mistake by flagging the first 2 records incorrectly. The 2 records from 01/05 should be joint.

The grouping by manager, then prospect is mandatory.

Lbass, thank you for your response. I may have to make this a separate report with different grouping.

 

[lbass]

My suggestion was based on your only tagging the second record as "joint", so please disregard. You could handle this by inserting a subreport that is not grouped by or linked by manager, but is linked by actionID (Or action and date). In the subreport you would create a formula like this:

if count({table.manager}) > 1 then "Y" else "N"

Display only the report footer section of the sub, which contains this formula.

-LB

 

[atthevilla]

I found a solution to my problem. I aliased the tasks table and linked it also to actions then created a formula to find tasks (Visits) with different keys linked to the same action (Contact):

If tasks.taskkey = tasks2.taskkey then 'N'
else if tasks.taskkey <> tasks2.taskkey then 'Y'
else 'N'

Then I added this formula as an ascending sort within tasks so that Y values would show in the group footer.

Thanks for the assistance. It's appreciated.