Our org has a custom object in place called "Programs" that is tied to Opportunities. When a user creates an Opportunity, they have the option of going through a picklist and selecting the relevant Programs to link to that Opp.
This element works great, but Big Boss would like a report of all the activities associated with each program. At the moment, there isn't a way to do that without making our reps record their activities in 3 different places, which is dumb.
We've come up with two possible solutions:
- Create a read-only custom activity field that auto-populates with the selected programs, but I can't figure out the formula to do that. I know how to just make another picklist field with the programs, but it's ridiculous to expect the reps check the boxes off every time they want to log an activity.
Right now I've been trying things in the custom Activity Formula field like
IF (Opportunity_Program__c >0, "Opportunity_Program__c.Name")
But it doesn't recognize Opportunity_Program__c as an object, even though I copied that directly from the Custom Object page. I also completely guessed on how to populate it with the program name.
- Create a Workflow so that Activities logged to the main Opportunity page get copied to the Program pages automatically, then pull reports from the program pages. We already have the reports set up for the program pages. I'm not sure how to set this up either.
As always, any help is very appreciated!
Best Answer
The basic problem you are facing is that your schema looks like this:
This is very hard to report against as you can't go from Program's to Tasks or vice-versa. Cross-object formula fields from Task to Program__c won't work either as you have discovered.
If you want to go with the solution of populating a picklist on Task of the Opportunity's Programs, you have a couple of choices:
Assumption - each Program__c has a Type or "Name' (but not the OOB Name field) that is what you want to report on.
Option 1 - Triggers
You will need two - one for Program__c and one for Task
The Task trigger (before insert and before update), fetches the parent Opportunity and its children Programs, setting the picklist field on Task to the list of Program__c.type__c's located
The Program trigger (after insert, after delete, after undelete), retrieves the parent Opportunity, and the Opportunity's children Tasks. The trigger then adds to the Task's picklist (if insert or undelete) or subtracts from the Task's picklist (if delete).
(A variation of this is to sync the list of Program__c's to a picklist on Opportunity)
Option 2 - Scheduled batch class
If the sync'ing isn't time crtitical, you could run a daily (hourly) batchable class that swept through all Opportunities, children Program__c, and children Tasks. Then simply smash the picklist in all of the Opportunity's Tasks to whatever the Opportunity's current set of Program__c are.
Caveats
Both solutions presume that a given Task is always associated with all Program__c for the common ancestor Opportunity.
Both solutions presume that the list of Program__c.type__c is finite and manageable via a picklist field on Task as you suggested in your OP.
@Girbot has an interesting comment and I have not tried that but populating that lookup field from Task to Program__c would still require the options above as the two lists are updated independently by user action.