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
Thibaud NavarreThibaud Navarre 

Bulkify Trigger by moving SOQL queries

Hi,

I'm new to Salesforce and I'm struggling since a few hours on how to bulkify this trigger.

I have an object called kognoz1__Invoice2__c linked with an opportunity through a lookup field. This object has 3 email custom fields :"Relance_Email__c","Relance_CC_Email__c" and "Relance__CC2_Email__c".

The trigger must copy the emails from the Contact Role of the Opportunity linked with my custom object in those 3 previous email fields. But only when the roles of the contacts are :"Decisionnaire et Comptabilité" or "Comptabilité" and moreover when a checkbox named  "Relance_Automatique" ( which is a custom field on the contact object ) is checked. 

The fact is that an opportunity can have one, two or three contact role corresponding to the conditions that's why I'm using three email custom fields in the kognoz1__Invoice2__c object.

Here is the trigger i made, it is working fine when I'm updating the kognoz records one by one but I would like to use a Batch in order to update a lot of records and I'm getting a "too many SOQL queries Error". 

 

trigger UpdateContactEmailRelance on kognoz1__Invoice2__c (before insert, before update) {
        for(kognoz1__Invoice2__c k : trigger.new){
        List<OpportunityContactRole> Contactsearch = [Select ContactId from OpportunityContactRole where OpportunityId=:k.kognoz1__Opportunity__c and (Role='Décisionnaire et Comptabilité' or Role='Comptabilité')];
        if(Contactsearch.size()==1){
            k.Relance_CC_Email__c='';
            k.Relance_CC2_Email__c='';
            Contact email1=[Select Email, Relance_Automatique__c from Contact where Id=:Contactsearch[0].ContactId];
            if(email1.Relance_Automatique__c=true){
                k.Relance_Email__c=email1.Email;
            }
            else k.Relance_Email__c='';
        }
        if(Contactsearch.size()==2){
            k.Relance_CC2_Email__c='';
            List<Contact> emails=[Select Email, Relance_Automatique__c from Contact where (Id=:Contactsearch[0].ContactId or Id=:Contactsearch[1].ContactId) and Relance_Automatique__c=true];
            if(emails.size()==1){
                k.Relance_Email__c=emails[0].Email;
                k.Relance_CC_Email__c='';
            }
            else if(emails.size()==2){
                k.Relance_Email__c=emails[0].Email;
                k.Relance_CC_Email__c=emails[1].Email;
            }
            else{
                k.Relance_Email__c='';
                k.Relance_CC_Email__c='';
            }
        }
        if(Contactsearch.size()==3){
            List<Contact> emails=[Select Email, Relance_Automatique__c from Contact where (Id=:Contactsearch[0].ContactId or Id=:Contactsearch[1].ContactId or Id=:Contactsearch[2].ContactId) and Relance_Automatique__c=true];
            if(emails.size()==1){
                k.Relance_Email__c=emails[0].Email;
                k.Relance_CC_Email__c='';
                k.Relance_CC2_Email__c='';
            }
            else if(emails.size()==2){
                k.Relance_Email__c=emails[0].Email;
                k.Relance_CC_Email__c=emails[1].Email;
                k.Relance_CC2_Email__c='';
            }
            else if(emails.size()==3){
                k.Relance_Email__c=emails[0].Email;
                k.Relance_CC_Email__c=emails[1].Email;
                k.Relance_CC2_Email__c=emails[2].Email;
            }
            else{
                k.Relance_Email__c='';
                k.Relance_CC_Email__c='';
                k.Relance_CC2_Email__c='';
            }
        }
    }
}


I know that I must bulkify it but I can't figure out how to do it. 

Thank you in advance !

Best Answer chosen by Thibaud Navarre
George AdamsGeorge Adams
You can't use SOQL queries inside a for loop and expect the trigger to handle bulk records. You need to come up with a way to move all of that SOQL outside of the for loop.

I would use a map like <Id, Integer> that is the field kognoz1__Opportunity__c paired with the number of results for that Id from the SOQL query.

Then, in place of all your statements like "if(Contactsearch.size()==3) {....}", you'd do "if(yourMapName.get(k.kognoz1__Opportunity__c)==3)  {....}".

The key is building a map (or multiple maps) with these counts that you can then reference in your for loop for each of the new records you're working with. This way you only make a few SOQL queries, format the results the way you need to, then run your for loop checks against the info in the maps. Some info that will help you build the maps here:

http://salesforce.stackexchange.com/questions/36690/soql-count-number-of-child-records
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm

All Answers

George AdamsGeorge Adams
You can't use SOQL queries inside a for loop and expect the trigger to handle bulk records. You need to come up with a way to move all of that SOQL outside of the for loop.

I would use a map like <Id, Integer> that is the field kognoz1__Opportunity__c paired with the number of results for that Id from the SOQL query.

Then, in place of all your statements like "if(Contactsearch.size()==3) {....}", you'd do "if(yourMapName.get(k.kognoz1__Opportunity__c)==3)  {....}".

The key is building a map (or multiple maps) with these counts that you can then reference in your for loop for each of the new records you're working with. This way you only make a few SOQL queries, format the results the way you need to, then run your for loop checks against the info in the maps. Some info that will help you build the maps here:

http://salesforce.stackexchange.com/questions/36690/soql-count-number-of-child-records
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm
This was selected as the best answer
Thibaud NavarreThibaud Navarre

Thank you for your advices George ! 

I managed to do what I wanted with maps and it's working fine even with a great number of records !