1

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:

  1. 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.

  1. 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!

  • This might be an alternative, there is a open beta for creating custom lookups on Activities. You just need to contact support to get it activated: success.salesforce.com/ideaview?id=08730000000BrPdAAK – Girbot Nov 19 '14 at 19:38
  • Thanks for the tip. We got ourselves added to the Beta, and I can get it to find the right objects for selection, just not autopopulate them. The other issue I'm running into is that lookup is 1:1, and we need it to be 1: many. I feel like we're moving in the right direction though! – Elizabeth Nov 20 '14 at 18:36
2

The basic problem you are facing is that your schema looks like this:

enter image description here

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.

| improve this answer | |
  • Your assumptions are actually spot-on. Essentially the problem is that any given opportunity is going to be associated with one or more of our programs (I think we have around 12-15 right now so yes, it is definitely a finite number). But Opportunity A might be associated with Programs 1, 5, and 7, and the Big Boss wants a report for just the activity on Program 7. – Elizabeth Nov 20 '14 at 19:37
  • Right now our only options are to run an activity report for all opportunities (which we do, but he wants to drill down more), or to run activity reports on each given program (which we're also doing, but the opportunity activities don't show up there unless the reps manually copy them). The Lookup Beta is all right, but it only allows for one value, which is a problem. And of course it doesn't auto-populate on its own. I'm going to look into junction objects, roll up summaries, and your trigger suggestion to see what will work for us. – Elizabeth Nov 20 '14 at 19:40
  • Elizabeth -- (1) I don't think Junction will work because Task can't be a parent of anything; OOB RSF won't work as you can't rollup text fields. (2) Just to confirm - the activity on Oppo A will show up three times, once on a report for Program 1, once on a report for Program 5, and once on a report for Program 7. (3) You could consider building a custom object called 'Program_Activity__c' and that could participate in a junction with Program__c. Then one Program_activity__c on an Oppo could be associated to some, all, or none of the Oppo's programs. – cropredy Nov 20 '14 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.