function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Max PowerMax Power 

How do I bulkify a trigger with soql count()?

I've got an Apex trigger that I'm attempting to bulkify to handle multiple records.  Essentially what I want to do is to update a custom Number field on my Campaign records, which is a count of a number of Lead records that have a particular status (Note: Lead Status, not Campaign Member status), any time a Lead record is updated.

 

I did a proof of concept for a single record, using the following trigger:

 

trigger leadTrigger1 on Lead(after insert, after update, after delete) { CampaignMember cm = [select CampaignId from CampaignMember where LeadId = :Trigger.new[0].Id limit 1]; Campaign c = [select Id, name, Wastage__c from Campaign where Id = :cm.CampaignId limit 1]; Integer count = [select count() from CampaignMember where CampaignId = :cm.CampaignId and Lead.Status = 'Closed - Not Converted']; System.Debug( 'count = ' + count ); c.Wastage__c = (Double)count; update(c); }

 

 

This works great for one record, but it doesn't take into consideration updates of multiple Lead records that are possibly associated with different campaigns.  I'm attempting to write something to the effect of:

 

trigger leadTrigger1 on Lead(after insert, after update, after delete) { System.Debug( 'leadTrigger1 fired!' ); Set<Id> leadIds = new Set<Id>(); for( Lead l : Trigger.old ) leadIds.add(l.Id); for( Lead l : Trigger.new ) leadIds.add(l.Id); CampaignMember [] cms = [select CampaignId from CampaignMember where LeadId in :leadIds]; Set<Id> cIds = new Set<Id>(); for( CampaignMember cm : cms ) cIds.add( cm.CampaignId ); Campaign [] cList = [select Id, name, Wastage__c from Campaign where Id in :cIds]; Map<Id, Integer> counts = new Map<Id, Integer> = Some SOQL query that will give me a map of Ids and the counts associated with those Ids. for( Campaign c : cList ) { c.Wastage__c = (Double)counts.get(c.Id); update(c); } }

 

 

But I'm not having any luck coming up with a SOQL query to give me a map of the Ids with the count.  Can this be done?  Any help would be greatly appreciated.

 

Thanks in advance,

Max

jeffdonthemic2jeffdonthemic2

See if this blog post with code will help you out. I use count() in the SOQL and have a detailed unit test.

 

Post: Roll-Up Summary Fields With Lookup Relationships – Part 2

 

Good luck!

 

Jeff Douglas

http://blog.jeffdouglas.com

Appirio 

Max PowerMax Power

Jeff,

 

Thanks for the reply.  It was definitely an interesting read; however, I don't believe that a Roll-up Summary Field will work for me in this case.  The problem is that I need to update Campaign when a Lead is updated.  If I put an RSF on Campaign, it will only get updated if a CampaignMember is updated, since Lead and Campaign are not in a Master-Detail relationship. 

 

John Kucera gave me a great response in this post.  If it were possible to schedule an Apex job, I suppose I could get away with a periodic update of this field, but it doesn't look like we have that capability at this time.

 

Thanks,

Max

Caleb_SidelCaleb_Sidel

You'll likely hit limits before too long, but assuming your data volume is relatively low you can use your "bulkified" code but you'll simply have to count yourself...something like...

 

SOQL Loop List<CampaignMember> cms = Select CampaignId from CampaignMember where CampaignId = IN: cList and Lead.Status = 'Closed - Not Converted' 

Map<ID,Integer> campaignIdToCount = new Map<Id,Integer>(); 

for(cm in CMS) {

 if(campaignIdToCount.campaignIdToCount(cm.Id)) {

    campaignIdToCount.put(cm.Id,campaignIdToCount.get(cm.Id)+1);

} else {

     campaignIdToCount.put(cm.Id,1); 

 

}

 

then another loop to update the campaign wastage__c with the count... 

 

 

Something like that might work...

 

 

GavinWillGavinWill

Max Power - Did you ever succed in getting this resolved?

 

I am looking for a similar thing. Essentially I am counting the number of times a custom field appears and to then apply a workflow email depending on its value.

 

I am looking at triggers also that simply updates a field with the result from a SOQL Count() query.

 

I will feedback any progress I get but would appreciate if you could also let me know any progress you have had on this.

 

many thanks

 

Gavin