0

This code is been hitting a governor limit and getting Too many SOQL queries: 101, based on research I found that I need to remove SOQL query from my for loop and the For Loops within For Loop.

Requirement :

  1. We have a custom text field on Contact and Lead Object "NCPC_Pardot_Avail_Sub_Interest_Ids__c" which is also created on Pardot and used in forms or landing pages having 2 subscription/Interest IDs in that field.
  2. When the form is submitted from pardot prospect/lead is created in pardot, which is synced in CRM and passes the subscription/interest ID in the custom text field "NCPC_Pardot_Avail_Sub_Interest_Ids__c" which needs to be the comma-separated value(18 digit ID should be separated by (,))
  3. Based on the IDs Subscription/Interest record should be created which should be associated with the particular Lead ID for which it's created.
  4. Duplicate subscription/Interest should not be created for Lead if it already exists with subscription/interest i.e. If someone has unsubscribed it before and come back and fill out the form same subscription/interest record needs to Optin or Selected again instead of creating records.

public static void afterInsert(List newLeads,Map<ID,Lead> oldLeadMap){ //insert subscription records on field update - NCPC_Pardot_Avail_Sub_Interest_Ids__c subscriptionInterestRecords(newLeads,oldLeadMap);

}


//Process to create subscription from the indicated field update
public static void subscriptionInterestRecords(List<Lead> newList,Map<ID,Lead> oldMap){
    try{            
        Map<Id,String> leadIdToAvailSubMap = new Map<Id,String>();
        if(schema.SObjectType.Lead.isAccessible()
           && schema.SObjectType.Lead.fields.NCPC_Pardot_Avail_Sub_Interest_Ids__c.isAccessible()){ 
            for(Lead leadRec : newList){
                   if( !String.isBlank(leadRec.NCPC_Pardot_Avail_Sub_Interest_Ids__c)){ 
                       //available subscription and interest are changing
                       leadIdToAvailSubMap.put(leadRec.Id,leadRec.NCPC_Pardot_Avail_Sub_Interest_Ids__c);
                   }
               }

           if(!leadIdToAvailSubMap.isEmpty()){
                          if(schema.SObjectType.ncpc__PC_Subscription__c.isAccessible()
                  && schema.SObjectType.ncpc__PC_Interest__c.isAccessible()
                  && schema.SObjectType.ncpc__PC_Available_Subscription_Interest__c.isAccessible() 
                  && schema.SObjectType.ncpc__PC_Available_Subscription_Interest__c.fields.ncpc__Type__c.isAccessible()
                  && schema.SObjectType.ncpc__PC_Subscription__c.fields.ncpc__Contact__c.isAccessible()
                  && schema.SObjectType.ncpc__PC_Interest__c.fields.ncpc__Contact__c.isAccessible()
                 ){

                         for(Id leadId : leadIdToAvailSubMap.keySet()){
                             List<String>  availSubscriptionInterestList = leadIdToAvailSubMap.get(leadId).split(',');
                             List<ncpc__PC_Subscription__c> addSubscriptionList = new List<ncpc__PC_Subscription__c>();
                             List<ncpc__PC_Interest__c> addInterestList = new List<ncpc__PC_Interest__c>();
                             Set<Id> subsIdSet = new Set<Id>();
                             Set<Id> interestIdSet = new Set<Id>();
                             
                             if(availSubscriptionInterestList.size()>0){
                                 for(ncpc__PC_Available_Subscription_Interest__c avail : [SELECT Id, ncpc__Type__c  FROM ncpc__PC_Available_Subscription_Interest__c 
                                                                                          WHERE Id  IN : availSubscriptionInterestList ]){
                                                                                              if(avail.ncpc__Type__c == 'Subscription'){
                                                                                                  subsIdSet.add(avail.Id);
                                                                                              }
                                                                                              if(avail.ncpc__Type__c == 'Interest'){
                                                                                                  interestIdSet.add(avail.Id);
                                                                                              }
                                                                                          }
                                 //subscriptionList
                                 for( ncpc__PC_Subscription__c sub : [SELECT Id, ncpc__Lead__c,ncpc__Opt_In__c, ncpc__Related_Subscription_Interest__c FROM ncpc__PC_Subscription__c WHERE ncpc__Lead__c =: leadId]){
                                                                          if(subsIdSet.contains(sub.ncpc__Related_Subscription_Interest__c)){
                                                                              if(sub.ncpc__Opt_In__c == false){
                                                                                  ncpc__PC_Subscription__c s = new ncpc__PC_Subscription__c (Id=sub.Id);
                                                                                  s.ncpc__Opt_In__c = true;
                                                                                  s.ncpc__Opt_In_Date__c = Date.today();
                                                                                  addSubscriptionList.add(s);
                                                                              }
                                                                              //do nothing -- don't process this record and remove from set
                                                                              subsIdSet.remove(sub.ncpc__Related_Subscription_Interest__c);
                                                                          }
                                                                      }
                                 if(subsIdSet.size()>0){
                                     //for remaining subscriptions
                                     for(Id sub : subsIdSet){
                                         ncpc__PC_Subscription__c recordSub = new ncpc__PC_Subscription__c();
                                         recordSub.ncpc__Opt_In__c = true;
                                         recordSub.ncpc__Lead__c = leadId;
                                         recordSub.ncpc__Opt_In_Source__c = 'Pardot Form';
                                         recordSub.ncpc__Related_Subscription_Interest__c = sub;
                                         addSubscriptionList.add(recordSub);
                                     }
                                 }
                                 
                                 
                                 //interestList
                                 for( ncpc__PC_Interest__c sub : [SELECT Id, ncpc__Lead__c, ncpc__Selected__c, ncpc__Interest_Selected__c  FROM ncpc__PC_Interest__c WHERE ncpc__Lead__c =: leadId]){
                                                                      if(interestIdSet.contains(sub.ncpc__Interest_Selected__c)){
                                                                          if(sub.ncpc__Selected__c == false){
                                                                              ncpc__PC_Interest__c a = new ncpc__PC_Interest__c(Id=sub.Id);
                                                                              a.ncpc__Selected__c = true;
                                                                              a.ncpc__Captured_Date__c = Date.today();
                                                                              addInterestList.add(a);
                                                                          }
                                                                          //do nothing -- don't process this record and remove from set
                                                                          interestIdSet.remove(sub.ncpc__Interest_Selected__c);
                                                                      }
                                                                  }
                                 if(interestIdSet.size()>0){
                                     //for remaining interest
                                     for(Id ins : interestIdSet){
                                         ncpc__PC_Interest__c recordSub = new ncpc__PC_Interest__c();
                                         recordSub.ncpc__Selected__c = true;
                                         recordSub.ncpc__Lead__c = leadId;
                                         recordSub.ncpc__Captured_Date__c = Date.today();
                                         recordSub.ncpc__Interest_Selected__c = ins;
                                         addInterestList.add(recordSub);
                                     }
                                 }
                                 
                                 if(addSubscriptionList.size()>0){
                                     upsert addSubscriptionList;
                                 }
                                 
                                 if(addInterestList.size()>0){
                                     upsert addInterestList;
                                 }
                             }
                         }
                     }
                 }
           }
    }catch(Exception e){
        //catch error in some object
        System.debug(+e.getMessage() + e.getStackTraceString());
    }finally{
        //blank out NCPC_Pardot_Avail_Sub_Interest_Ids__c to handle optouts and field not getting changed
        List<Lead> updateLeadList = new List<Lead>();
        for(Lead l : newList){
            //update the field to blank once the subscription/interest records got created
            if(String.isNotBlank(l.NCPC_Pardot_Avail_Sub_Interest_Ids__c)){
                Lead lnew = new Lead(Id=l.Id,NCPC_Pardot_Avail_Sub_Interest_Ids__c='');
                updateLeadList.add(lnew);
            }
        }
        try{
            if(updateLeadList.size()>0)
                update updateLeadList;
        }catch(Exception e){ System.debug(e.getStackTraceString() + ' ' + e.getMessage() );}
    }
}

}

1
  • can you explain you requirement in details? also clarify which objects are parents and child Sep 30, 2020 at 15:46

1 Answer 1

0

If you can explain your requirement in detail then maybe I can help you to modify code well.

Since you want to remove for within for and wants to avoid SOQL query within for loop, I have modified your code below. Please note that I haven't tested the code. This is just for your reference of how can you write a better code with the help of single map instead of defining 2 different Set, avoid for within for and SOQL query under for loop.

public static void afterInsert(List<Lead> newLeads,Map<ID,Lead> oldLeadMap){
    //insert subscription records on field update -  NCPC_Pardot_Avail_Sub_Interest_Ids__c
    subscriptionInterestRecords(newLeads,oldLeadMap);
    
}


//Process to create subscription from the indicated field update
public static void subscriptionInterestRecords(List<Lead> newList,Map<ID,Lead> oldMap){
    try{     
        boolean isSuccess = false;  //adding boolean variable. If operation is successful, update lead records
        Map<Id,String> leadIdToAvailSubMap = new Map<Id,String>();
        //Set<Id> interestIdSet = new Set<Id>();
        //Set<Id> subsIdSet = new Set<Id>();
        Map<Id,String> mapOfSubIntMap = new Map<Id,String>(); // create single map instead of 2 Sets
        List<ncpc__PC_Subscription__c> addSubscriptionList = new List<ncpc__PC_Subscription__c>();
        List<ncpc__PC_Interest__c> addInterestList = new List<ncpc__PC_Interest__c>();
        if(schema.SObjectType.Lead.isAccessible()
           && schema.SObjectType.Lead.fields.NCPC_Pardot_Avail_Sub_Interest_Ids__c.isAccessible()){ 
               for(Lead leadRec : newList){
                   if( !String.isBlank(leadRec.NCPC_Pardot_Avail_Sub_Interest_Ids__c)){ 
                       //available subscription and interest are changing
                       leadIdToAvailSubMap.put(leadRec.Id,leadRec.NCPC_Pardot_Avail_Sub_Interest_Ids__c);
                   }
               }
               
               if(!leadIdToAvailSubMap.isEmpty()){
                   if(schema.SObjectType.ncpc__PC_Subscription__c.isAccessible()
                      && schema.SObjectType.ncpc__PC_Interest__c.isAccessible()
                      && schema.SObjectType.ncpc__PC_Available_Subscription_Interest__c.isAccessible() 
                      && schema.SObjectType.ncpc__PC_Available_Subscription_Interest__c.fields.ncpc__Type__c.isAccessible()
                      && schema.SObjectType.ncpc__PC_Subscription__c.fields.ncpc__Contact__c.isAccessible()
                      && schema.SObjectType.ncpc__PC_Interest__c.fields.ncpc__Contact__c.isAccessible()
                     ){
                         
                         for(ncpc__PC_Available_Subscription_Interest__c avail : [SELECT Id, ncpc__Type__c  FROM ncpc__PC_Available_Subscription_Interest__c 
                                                                                  WHERE Id  IN : leadIdToAvailSubMap.values()]){
                                                                                      if(avail.ncpc__Type__c == 'Subscription'){
                                                                                         // subsIdSet.add(avail.Id); No need to create 2 different Sets
                                                                                         mapOfSubIntMap.put(avail.Id,avail.ncpc__Type__c);
                                                                                      }
                                                                                      else if(avail.ncpc__Type__c == 'Interest'){
                                                                                          //interestIdSet.add(avail.Id); No need to create 2 different Sets
                                                                                          mapOfSubIntMap.put(avail.Id,avail.ncpc__Type__c);
                                                                                      }
                                                                                  } //Ending for loop
                         
                         //subscriptionList
                         for( ncpc__PC_Subscription__c sub : [SELECT Id, ncpc__Lead__c,ncpc__Opt_In__c, ncpc__Related_Subscription_Interest__c FROM ncpc__PC_Subscription__c WHERE ncpc__Lead__c =: leadIdToAvailSubMap.keySet()]){
                             //If Block Updates the record
                             if(mapOfSubIntMap.containsKey(sub.ncpc__Related_Subscription_Interest__c) && mapOfSubIntMap.get(sub.ncpc__Related_Subscription_Interest__c) == 'Subscription'){
                                 
                                 if(sub.ncpc__Opt_In__c == false){
                                     ncpc__PC_Subscription__c s = new ncpc__PC_Subscription__c(
                                     Id=sub.Id,
                                     ncpc__Opt_In__c = true,
                                     ncpc__Opt_In_Date__c = Date.today()
                                     );
                                     
                                     addSubscriptionList.add(s); //Looks like you're inserting records here. Please confirm
                                 }
                             }
                              /* Else block creates the records. If you want IF condition here instead of else then mention which criteria to be include in IF block. In above IF condition, we check if Map contains ID and has subscription and also check ncpc__Opt_In__c to false then only it updates record. For Create a new Record specify what is the criteria 
                              */
                                 else{
                                     system.debug('Interest Lead is:'+sub.ncpc__Lead__c);
                                     ncpc__PC_Subscription__c recordSub = new ncpc__PC_Subscription__c(
                                     ncpc__Opt_In__c = true,
                                     ncpc__Lead__c = sub.ncpc__Lead__c,
                                     ncpc__Opt_In_Source__c = 'Pardot Form',
                                     ncpc__Related_Subscription_Interest__c = sub.Id
                                     ); 
                                     addSubscriptionList.add(recordSub); //Looks like you're updating records here. Please confirm
                                 }
                         }//Ending for loop
                         /*
                         //Shifting Logic above. Unnecessary code. 
                         if(subsIdSet.size()>0){
                             //for remaining subscriptions
                             for(Id sub : subsIdSet){
                                 ncpc__PC_Subscription__c recordSub = new ncpc__PC_Subscription__c();
                                 recordSub.ncpc__Opt_In__c = true;
                                 recordSub.ncpc__Lead__c = leadId;
                                 recordSub.ncpc__Opt_In_Source__c = 'Pardot Form';
                                 recordSub.ncpc__Related_Subscription_Interest__c = sub;
                                 addSubscriptionList.add(recordSub);
                             }
                         }*/
                         
                         //interestList
                         for( ncpc__PC_Interest__c sub : [SELECT Id, ncpc__Lead__c, ncpc__Selected__c, ncpc__Interest_Selected__c  FROM ncpc__PC_Interest__c WHERE ncpc__Lead__c =: leadIdToAvailSubMap.keySet()]){
                                  if(mapOfSubIntMap.containsKey(sub.ncpc__Interest_Selected__c) && mapOfSubIntMap.get(sub.ncpc__Interest_Selected__c) == 'Interest'){
                                 if(sub.ncpc__Selected__c == false){
                                     ncpc__PC_Interest__c a = new ncpc__PC_Interest__c(
                                     Id=sub.Id,
                                     ncpc__Selected__c = true,
                                     ncpc__Captured_Date__c = Date.today()
                                     );
                                     addInterestList.add(a);//Looks like you're inserting records here. Please confirm
                                 }
                                 
                             }
                             /* Else block creates the records. If you want IF condition here instead of else then mention which criteria to be include in IF block. In above IF condition, we check if Map contains ID and has interest and also check ncpc__Opt_In__c to false then only it updates record. For Create a new Record specify what is the criteria?
                              */
                              
                                 else{
                                     system.debug('Interest Lead is:'+sub.ncpc__Lead__c);
                                 ncpc__PC_Interest__c recordSub = new ncpc__PC_Interest__c(ncpc__Selected__c = true,
                                 ncpc__Lead__c = sub.ncpc__Lead__c,
                                 ncpc__Captured_Date__c = Date.today(),
                                 ncpc__Interest_Selected__c = ins
                                 );
                                 addInterestList.add(recordSub); //Looks like you're updating records here. Please confirm
                                 }
                              
                         }//Ending for loop
                         /*
                         //Shifting Logic above. Unnecessary code & for loop
                         if(interestIdSet.size()>0){
                             //for remaining interest
                             for(Id ins : interestIdSet){
                                 ncpc__PC_Interest__c recordSub = new ncpc__PC_Interest__c();
                                 recordSub.ncpc__Selected__c = true;
                                 recordSub.ncpc__Lead__c = leadId;
                                 recordSub.ncpc__Captured_Date__c = Date.today();
                                 recordSub.ncpc__Interest_Selected__c = ins;
                                 addInterestList.add(recordSub);
                             }
                         }*/
                         // no need to check size. Use isNotEmpty()
                         if(!addSubscriptionList.isEmpty()){
                             isSuccess = true;
                             upsert addSubscriptionList;
                         }
                         
                         if(!addInterestList.isEmpty()){
                             isSuccess = true;
                             upsert addInterestList;
                         }
                         
                         
                     }
               }
           }
    }catch(Exception e){
        //catch error in some object
        isSuccess = false;
        System.debug(+e.getMessage() + e.getStackTraceString());
    }finally{
        //blank out NCPC_Pardot_Avail_Sub_Interest_Ids__c to handle optouts and field not getting changed
        List<Lead> updateLeadList = new List<Lead>();
        //if operation is successful, update Lead Records
        if(isSuccess){
        for(Lead l : newList){
            //update the field to blank once the subscription/interest records got created
            if(String.isNotBlank(l.NCPC_Pardot_Avail_Sub_Interest_Ids__c)){
                Lead lnew = new Lead(Id=l.Id,NCPC_Pardot_Avail_Sub_Interest_Ids__c='');
                updateLeadList.add(lnew);
            }
        }
        }
        try{
            if(!updateLeadList.isEmpty())
                update updateLeadList;
        }catch(Exception e){ System.debug(e.getStackTraceString() + ' ' + e.getMessage() );}
    }
}
}
13
  • Hey Dhananjay, thank you so much for the solution. I'm just little confused for the line no 32 in my code, where the values from map is getting separated by comma. That's the thing we need to achieve where the 18 digit IDs (subscriptionID or InterestId or Both) will be coming from Pardot through field "NCPC_Pardot_Avail_Sub_Interest_Ids__c" and it will be separated by comma based on that subscriptions record will be created. could you please help me know how it will be separated?
    – darkCloud
    Oct 1, 2020 at 7:03
  • Also at line no 58 and 96 in your code, I'm trying to update the susbscription and interest record if that is unsubscribed and user again comes back fill out form and 18 digit id is passed again then Optin check box equal to true for subscription and Selected to true for interest should be updated instead of creating new records. At line no 83 and 120 I was trying to create fresh records based on the 18 digit Ids(both subs and interest) coming. I've removed them because if the Optin and selected is true then do nothing.
    – darkCloud
    Oct 1, 2020 at 7:03
  • on what basis do you figure out whether it is subscriptionID or InterestId or Both? In Line 36 of my code, it only returns Interest Records since from leadIdToAvailSubMap.values(), it only consider InterestId.Same is applicable for subscription. I could not understand why you want to separate those Ids and if you want to separate those ids, there should be some difference between those ids that you can use in your condition. Oct 1, 2020 at 8:22
  • to create records, you don't need separate for loop here. Else condition that I added helps you to create those records. If you want to update the subscription and interest and create records at the same time, you can remove else part and add If condition there. to add condition in if block, you can add Boolean check in 58/96 line then add it to your condition. once you add those inserted records, you can uncheck Boolean variable or instead of Boolean variable you can use some valid condition in your code of on what basis you insert records. Oct 1, 2020 at 8:29
  • I think the first part of the requirement you did was absolutely correct even in my code it was trying to achieve the same thing. I'm not able to achieve the 2nd point which you have told, could you please help me in that? I think rest your code really helped me alot
    – darkCloud
    Oct 1, 2020 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.