+ Start a Discussion
Max PowerMax Power 

Need help with custom field

I have a fairly stright forward problem, but I'm not sure how I should solve it.  Basically, I need a new custom field on the Campaign table that is a count of the number of leads that have a particular status.  Unfortunately, I can't simply create a Roll-Up Summary field on Campaigns, because it's not the master in a master-detail relationship.  In other words, I need a field on Campaigns that is essentially a count of the number of leads associated with that campaign that have a particular status.  How should I approach this?

 

The one possibility that I can see is that I could add a trigger that fires on insert/update/delete of leads that performs the appropriate SOQL query and updates the associated Campaign record(s); however, this seems a bit round-about, and my have to be done for all Campaign records every time that a Lead record is updated.  Is there a better way to do this?

 

Thanks in advance,

Max

 

angusgrantangusgrant

Hi Max,

 

I'm fairly new to Salesforce so might be wrong but,  I had a similar problem and was solved using a couple of apex triggers as described by yourself. I don't think there is another way. sorry. 

 

 

Angus  

jkucerajkucera

For this, the best route is probably campaign member triggers instead of lead triggers as the lead triggers could easily hit governor limits due to the 1:many relationship.

 

To do it "right" you'll need both an after insert/update trigger to increment the count & a delete trigger to decrement the count when campaign members are deleted.

 

Note in the next release (safe harbor) ~ October 10th  there will be campaign rollup summary fields that will do most of this work for you with the caveat that the RSF won't update on campaign if a lead is updated, only if the campaign member is updated. 

Max PowerMax Power

Thank you both for the timely replies.  I will look into implementing this and let you know how it goes.  Your advice was much appreciated!

 

Thanks,

Max

Max PowerMax Power

John,

 

I attempted to implement a CampaignMember trigger as you suggested, but I'm not getting the behavior that I want.  After putting some debugs into my trigger, I don't see my trigger fire unless I add a lead to a Campaign, or modify the Campaign Member status.  I guessing I wasn't quite clear on what I meant by status in my original post.  I'm looking to update a field on a Campaign record if the status field of a lead record is modified.  Wouldn't that mean that the trigger has to be on Lead instead of Campaign Member? 

 

I do see your point about the 1 to many relationship running into governor issues though.  Can you think of anything that I can do to get around this limitation?  The field that I want to update in Campaign is simply a count of the number of Lead records associated with that Campaign that have a particular Lead Status.  Is there a way that I can execute a single SOQL query to get the count of matching records to avoid the governor issue?

 

Thanks in advance,

 Max

jkucerajkucera

There isn't an easy answer for this given the many to many relationship between Leads & Campaigns.  You don't want to cascade update all campaign members when something on Lead changes and then cascade update the Campaigns as governor limits will quickly stop this, potentially even using asynchronous apex which has higher limits.

 

Either batch apex or a button on campaign that finds the count is probably the most efficient way to get this.

 

The button may make the most sense given batch apex can't be scheduled yet.  It should be pretty quick to make the code for it as well given the SOQL is straight forward to query leads in a campaign.  


 If that doesn't make sense, perhaps there's a process change that you can make that would get you the information you need without the many to many cascade update.

 

cadkinsocadkinso

I have a similar problem - wanted for any given campaign, to see a summary of the number of leads at each lead status - basically to see a report on which leads have been follow-up on and which had not.

 

We are using a clunky workaround right now, so if there is a more elegant solution, I'd love to hear it:

 

1.  We created a custom field called Lead Source Description on a Lead Record that is set to a picklist.

2.  When we create a new campaign, we add that same campaign name to the end of the picklist.

3.  When we use Web-to-lead forms or import leads for that campaign, we also populate the Lead Source Description field.

4.  Now, I can create a Leads Report, and summarize by Lead Source Description (which happens to equal Campaign).

 

Of course, this really only works for the initial campaign that brings the lead in.