function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
orikkerorikker 

Apex Trigger SOQL Limit problem

I am trying to bulk update Contacts (FAs) with number of leads related to them.

 

I get stuck because of the SOQL limit... Please help.

 

thank you.

 

trigger CountLeads on Contact (before insert, before update) {

//production version
	
	Map <Id, Contact > FAList = new Map<Id, Contact> (
	
	[SELECT Id, Leads_2010_YTD__c FROM Contact WHERE Id in :Trigger.new]
	);
	
	
	
	Map<Id, Integer> LeadCount = new Map<Id, Integer> ();
	
	for (Contact con: Trigger.New ){
		Integer i = [SELECT count () FROM Lead 
		WHERE FA__c = :con.Id
		 AND (CreatedDate >= 2010-01-01T00:00:00Z AND CreatedDate <= 2010-12-31T00:00:00Z)];
		 
		LeadCount.put (con.Id, i);
		
		
	} //end for con
	
	
	try {
		
		for (Contact FAupdate : Trigger.new){
			
			FAupdate.Leads_2010_YTD__c = LeadCount.get(FAupdate.Id);
			
		} // end for FAupdate
		
		
		
	}// end try
    
    catch (System.Queryexception e){
    	
    	System.debug('FA Lead Count ERROR:' + e);
    	
    } // end catch
    
 
}

 

Best Answer chosen by Admin (Salesforce Developers) 
SuperfellSuperfell

Rather than doing hte count query in a loop, you should be able get all the counts in one go with an aggregate query, by grouping on the FA__c field.

All Answers

SuperfellSuperfell

Rather than doing hte count query in a loop, you should be able get all the counts in one go with an aggregate query, by grouping on the FA__c field.

This was selected as the best answer
orikkerorikker

Thank you . :)

 


Here is the end code in case someone has the same trouble.

 

trigger CountLeads on Contact (before insert, before update) {

//production version
	 	
	Map <String, Integer> LeadCount = new Map <String, Integer> ();
	
	String FAid = null;
	Integer l = null;
	
	for (AggregateResult agr: [SELECT  FA__c, count(Id) numOfLeads FROM Lead 
		WHERE FA__c in :Trigger.new
		 AND (CreatedDate >= 2010-01-01T00:00:00Z
		  AND CreatedDate <= 2010-12-31T00:00:00Z)
		   GROUP BY FA__c]){
			
		FAid = string.valueOf(agr.get('FA__c'));
		l = integer.valueOf(agr.get('numOfLeads'));
		LeadCount.put (FAid, l);	
		}	
	
	try {
		
		for (Contact FAupdate : Trigger.new){
			
			if (Integer.valueOf(LeadCount.get(FAupdate.Id)) != null){
				
				FAupdate.Leads_2010_YTD__c = Integer.valueOf(LeadCount.get(FAupdate.Id));
			}
			
			else FAupdate.Leads_2010_YTD__c = 0;
			
		} // end for FAupdate
		
	}// end try
    
    catch (System.Queryexception e){
    	
    	System.debug('FA Lead Count ERROR:' + e);
    	
    } // end catch
    
}