Apex Code Development You need to sign in to do that
Don't have an account?
❓ 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!
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;
}
}
}
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
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