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
Mikhail Nitko 5Mikhail Nitko 5 

CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: ProcessWebFields: System.LimitException: Too many SOQL queries: 101

Hi folks and thank you for taking a look at this question,

I am getting this error in a trigger in an org I inherited, but the error is not always presenting itself and I cant figure out exactly how to fix it.

All my research shows that it is a governor limit error, but I'm not sure how to fix it in this inherited code. And the code is as follows:
  
trigger ProcessWebFields on Lead (after insert, after update) {

  for (Lead updatedLead : Trigger.new) {  
        
        // If these criteria are met, we've just create a Lead and 
        // there are values in the Web_UMOL field.
        if(updatedLead.Web_Program_of_Interest__c != NULL && updatedLead.Primary_Program_of_Interest__c == NULL){
          
          // Break up the Web_ field
          String[] umolProgramsArr = updatedLead.Web_Program_of_Interest__c.split(';');
          
          // Create Point of Interest records
          for(String program : umolProgramsArr) {
            Utilities.registerInterest(updatedLead.Id, program);
          }

          // Set the main Program of Interest
            List<Program__c> primaryProgramList = [SELECT Id FROM Program__c WHERE Name = :Utilities.getRealProgramName(umolProgramsArr[0])];
            if(primaryProgramList.size() == 1){
                Utilities.setPrimaryProgramOfInterest(updatedLead.Id, umolProgramsArr[0]); 
                Utilities.checkPrimaryPOI(updatedLead.Id, primaryProgramList[0].Id);
            }
            
         
         // Otherwise, we are evaluating a change made that will effect the Primary 
         // Program of Interest lookup field.
        } else {

          // If no Trigger.old, it's the first time running through and we don't want this code.
          if(Trigger.old != null){
            Lead oldLead = Trigger.oldMap.get(updatedLead.Id);
            // Check to see if there is a change to the Primary_Program_of_Interest__c lookup
          if(updatedLead.Primary_Program_of_Interest__c != oldLead.Primary_Program_of_Interest__c){
            Utilities.syncPrimaryPOI(updatedLead.Id, updatedLead.Primary_Program_of_Interest__c);
          }
          }
          
        }

        

    }

Thank you for helping folks.
Alain CabonAlain Cabon
Hi,

Most of the time, when you see a SOQL request inside a loop, you could have a problem of  "Too many SOQL queries: 101" if you update in bulk mode your data.

for (Lead updatedLead : Trigger.new) {
   ...
    List<Program__c> primaryProgramList = [SELECT Id FROM Program__c
                                                                       WHERE Name = :Utilities.getRealProgramName(umolProgramsArr[0])];
    ....
}

The common technique is to create a set of values of values used in the where clause (here mySet of names) inside the loop and you just use one SOQL query outside the loop with "WHERE Name in :mySet" 

Set<String> myNames = new Set<String>();
for (Lead updatedLead : Trigger.new) {
   String[] umolProgramsArr = updatedLead.Web_Program_of_Interest__c.split(';');
    myNames.add( Utilities.getRealProgramName(umolProgramsArr[0]) );
}
List<Program__c> primaryProgramList = [SELECT Id FROM Program__c  WHERE Name in :myNames]; 

Avoid SOQL queries that are inside FOR loops.
https://help.salesforce.com/articleView?id=000181404&type (https://help.salesforce.com/articleView?id=000181404&type=1)

Regards
James (CloudAnswers)James (CloudAnswers)
The query error is from a lack of bulkification, where you exceed the number of queries in a transaction.  This happens since each lead inserted in a batch will be executing a new query, potentially 100+ queries just for this trigger, not counting whatever other code you have in that environment.

Here's a version with bulkification:
  
trigger ProcessWebFields on Lead (after insert, after update) {

    // bulkify querying of programs
    Map<String, Program__c> programs = new Map<String, Program__c>();
    for (Lead l : Trigger.new) {
        if (String.isNotBlank(l.Web_Program_of_Interest__c)) {
            for (String programName : l.Web_Program_of_Interest__c.split(';')) {
                programs.put(programName, null);
            }
        }
    }
    for (Program__c p : [select Id, Name from Program__c where Name in :programs.keySet()]) {
        programs.put(p.Name, p);
    }

    for (Lead updatedLead : Trigger.new) {  

        // If these criteria are met, we've just create a Lead and 
        // there are values in the Web_UMOL field.
        if(updatedLead.Web_Program_of_Interest__c != NULL && updatedLead.Primary_Program_of_Interest__c == NULL){

            // Break up the Web_ field
            String[] umolProgramsArr = updatedLead.Web_Program_of_Interest__c.split(';');

            // Create Point of Interest records
            for(String program : umolProgramsArr) {
                Utilities.registerInterest(updatedLead.Id, program);
            }

            Program__c primaryProgram = umolProgramsArr.isNotEmpty() ? programs.get(umolProgramsArr[0]) : null;
            if (primaryProgram) {
                Utilities.setPrimaryProgramOfInterest(updatedLead.Id, primaryProgram.Name); 
                Utilities.checkPrimaryPOI(updatedLead.Id, primaryProgram.Id);
            }

        // Otherwise, we are evaluating a change made that will effect the Primary 
        // Program of Interest lookup field.
        } else {

            // If no Trigger.old, it's the first time running through and we don't want this code.
            if(Trigger.old != null){
                Lead oldLead = Trigger.oldMap.get(updatedLead.Id);
                // Check to see if there is a change to the Primary_Program_of_Interest__c lookup
                if(updatedLead.Primary_Program_of_Interest__c != oldLead.Primary_Program_of_Interest__c){
                    Utilities.syncPrimaryPOI(updatedLead.Id, updatedLead.Primary_Program_of_Interest__c);
                }
            }

        }
    }
}

I just added bulkification at the top of the code basically... let me know what you think.
Mikhail Nitko 5Mikhail Nitko 5
Hi everyone.

I have not had the chance to try the code just yet.

I will be right back with you.