+ Start a Discussion
Norm CopelandNorm Copeland 

❓ How to bulkify trigger when soql query is searching in range?

Hi all, 

I have written a basic trigger that is meant to match it to a custom object that has two date fields; a start date and an end date. The closedate of the opportunity should be >= the start date and <= the end date. A custom field on the opp will then be updated with info from the custom object record. Because of the way that custom object is set up, there's only one record that will match each opportunity. My trigger works but with the SOQL query in the for loop, will not scale. With my SOQL query searching in a range, I'm having trouble bulkifying this (I am new to bulkifying and haven't written too many triggers). I'll paste my original un-bulkified code below. Thank you community for the help!
 
trigger emp_summary_at_time_of_donation on opportunity (before insert, before update) {

	for (Opportunity newOpp : Trigger.new) {

		// variables set
		Decimal account_num = decimal.valueOf(newOpp.Project_Account_Number__c);
		Date OppCloseDate = newOpp.CloseDate;

		system.debug (account_num);
		
		// SOQL Query finds matching member history record
		List<Member_History__c> MemberRecord = [SELECT Id,
										  			   Member__r.Sending_Country_NTMAA__c,
										  			   Start_Date__c,
										  			   End_Date__c,
													   Field__c,
													   Category_NTMAA__c,
													   Status_NTMAA__c
									 			  FROM Member_history__c
												 WHERE Member__r.Account_NTMAA__c = :account_num AND
										  			   Start_Date__c <= :OppCloseDate AND
										  			   End_Date__c >= :OppCloseDate
												 LIMIT 1];

		IF (MemberRecord.size() > 0){

			system.debug(MemberRecord.size());

			// Update opp with employee summary
			newOpp.employee_summary__c = 'SC:' +  MemberRecord.get(0).Member__r.Sending_Country_NTMAA__c +'|'+
										 'FLD:'+  MemberRecord.get(0).Field__c  +'|'+
										 'CAT:'+  MemberRecord.get(0).Category_NTMAA__c +'|'+
										 'STAT:'+ MemberRecord.get(0).Status_NTMAA__c;
		}
	}
}


 
Best Answer chosen by Norm Copeland
Suraj TripathiSuraj Tripathi

Hi Norm Copeland,

Please try this code hope it will help you.

trigger emp_summary_at_time_of_donation on opportunity (before insert, before update) {
	map<Decimal, Date> accNoWithDate = new map<Decimal, Date>();
	for (Opportunity newOpp : Trigger.new) {
		if(newOpp.Project_Account_Number__c != null && newOpp.CloseDate){
			Decimal account_num = decimal.valueOf(newOpp.Project_Account_Number__c);
			accNoWithDate.put(account_num, newOpp.CloseDate);
		}
	}
	if(accNoWithDate.size()>0){
		map<Decimal, Member_History__c> accNoWithMemHistory = new map<Decimal, Member_History__c>();
		for(Member_History__c memHistory : [SELECT Id, Member__r.Sending_Country_NTMAA__c, Member__r.Account_NTMAA__c, Start_Date__c, End_Date__c,Field__c,Category_NTMAA__c, Status_NTMAA__c
									 			  FROM Member_history__c WHERE Member__r.Account_NTMAA__c IN :accNoWithDate.keySet()]){
			
			Date OppCloseDate = accNoWithDate.get(memHistory.Member__r.Account_NTMAA__c);
			if(memHistory.Start_Date__c <= OppCloseDate && End_Date__c >= OppCloseDate){
				accNoWithMemHistory.put(memHistory.Member__r.Account_NTMAA__c, memHistory);
			}		
		}
		if(accNoWithMemHistory.size()>0){
			for (Opportunity newOpp : Trigger.new) {
				Decimal account_num = decimal.valueOf(newOpp.Project_Account_Number__c);
				if(accNoWithMemHistory.containsKey(account_num)){
					Member_History__c MemHistoryRecord = accNoWithMemHistory.get(account_num);
					newOpp.employee_summary__c = 'SC:' +  MemberRecord.get(0).Member__r.Sending_Country_NTMAA__c +'|'+
										 'FLD:'+  MemHistoryRecord.Field__c  +'|'+
										 'CAT:'+  MemHistoryRecord.Category_NTMAA__c +'|'+
										 'STAT:'+ MemHistoryRecord.Status_NTMAA__c;
				}			
			}		
		}		
	}
}


If it helps you mark as a best and close unsolved thread.

Regards,

Suraj

All Answers

Suraj TripathiSuraj Tripathi

Hi Norm Copeland,

Please try this code hope it will help you.

trigger emp_summary_at_time_of_donation on opportunity (before insert, before update) {
	map<Decimal, Date> accNoWithDate = new map<Decimal, Date>();
	for (Opportunity newOpp : Trigger.new) {
		if(newOpp.Project_Account_Number__c != null && newOpp.CloseDate){
			Decimal account_num = decimal.valueOf(newOpp.Project_Account_Number__c);
			accNoWithDate.put(account_num, newOpp.CloseDate);
		}
	}
	if(accNoWithDate.size()>0){
		map<Decimal, Member_History__c> accNoWithMemHistory = new map<Decimal, Member_History__c>();
		for(Member_History__c memHistory : [SELECT Id, Member__r.Sending_Country_NTMAA__c, Member__r.Account_NTMAA__c, Start_Date__c, End_Date__c,Field__c,Category_NTMAA__c, Status_NTMAA__c
									 			  FROM Member_history__c WHERE Member__r.Account_NTMAA__c IN :accNoWithDate.keySet()]){
			
			Date OppCloseDate = accNoWithDate.get(memHistory.Member__r.Account_NTMAA__c);
			if(memHistory.Start_Date__c <= OppCloseDate && End_Date__c >= OppCloseDate){
				accNoWithMemHistory.put(memHistory.Member__r.Account_NTMAA__c, memHistory);
			}		
		}
		if(accNoWithMemHistory.size()>0){
			for (Opportunity newOpp : Trigger.new) {
				Decimal account_num = decimal.valueOf(newOpp.Project_Account_Number__c);
				if(accNoWithMemHistory.containsKey(account_num)){
					Member_History__c MemHistoryRecord = accNoWithMemHistory.get(account_num);
					newOpp.employee_summary__c = 'SC:' +  MemberRecord.get(0).Member__r.Sending_Country_NTMAA__c +'|'+
										 'FLD:'+  MemHistoryRecord.Field__c  +'|'+
										 'CAT:'+  MemHistoryRecord.Category_NTMAA__c +'|'+
										 'STAT:'+ MemHistoryRecord.Status_NTMAA__c;
				}			
			}		
		}		
	}
}


If it helps you mark as a best and close unsolved thread.

Regards,

Suraj

This was selected as the best answer
Norm CopelandNorm Copeland
Thanks Suraj! I had to fix up some variables but other than that it seems to work! I'll test it at scale tomorrow, thank you for the help. I've learned a lot looking through this code. MUCH appreciated!
Suraj TripathiSuraj Tripathi
Thanks Norm for choosing my answer as a best.