function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
ankushankush 

Getting Too many SOQL queries: 101 when trying to update Contacts

Hi can some one help me in rectifying the code.

I am trying to update contacts and getting the above error. I have around 4000 contacts.


trigger Updateuserprofile on contact (after update) {
    string text1=Label.New_Reviewer;
      String recordTypeName = Label.Contact_Record_Type; 
  Map<String,Schema.RecordTypeInfo> rtMapByName = Schema.SObjectType.Contact.getRecordTypeInfosByName();
  Schema.RecordTypeInfo rtInfo =  rtMapByName.get(recordTypeName);
  id recordTypeId = rtInfo.getRecordTypeId();
    
    if(Trigger.isafter && trigger.isUpdate)
    {
          if(recursiveTrigger.runOnce()){
              
  for(contact cnt:trigger.new){
      if(cnt.recordtypeID==recordTypeId){
          if(cnt.Reviewer_Status__c==null){
              
          }
              
  else if( cnt.Reviewer_Status__c.equals(text1) ){
      try{
          list<User> u=[select Id,Email,Username from User where contactid=:cnt.Id];
          for(user usr:u){
           updateuserinformation.updateuser(usr.Id);
              }
}
      catch(exception e){
          
          
      }
    
}
   
      
}
    }  
  }
     
}
    }
 
Best Answer chosen by ankush
ankushankush

Hi Zuinglio,


Can you guide me for the best practice to avoid the soql query in the for loop. Code is working fine just want to follow best practice as well.

if(trigger.isbefore && trigger.isdelete)
    {
     
       list<COI_Expertise__c> coilist=new list<COI_Expertise__c>();
       list<panel_assignment__c> lp=[select Reviewer_Name__c,Panel__c,Active__c from panel_assignment__C where id=:Trigger.old];
        system.debug('The list is' +  lp);
   for(panel_assignment__c l:lp){
       Id ReviewerId=l.Reviewer_Name__c;
    Id PanelId=l.Panel__c;
   list<Research_Application__c> r=[select id from Research_Application__c where Panel__c=:panelId];
       for(Research_Application__c ra:r){
       Id rschappId=ra.Id;
           list<COI_Expertise__c> ci=[Select id,Reviewer_Name__c from COI_Expertise__c where Research_Application__c=:rschappId];
           for(COI_Expertise__c ce:ci){
               if(ReviewerId==ce.Reviewer_Name__c)
       ce.Active__c=false;
       coilist.add(ce);
   }
           }
       }
        update coilist;
    }

All Answers

Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello,

I am not sure if I got it right but try this instead:
 
trigger Updateuserprofile on Contact (after update) {
    
	String text1=Label.New_Reviewer;
	String recordTypeName = Label.Contact_Record_Type; 
	
	Map<String,Schema.RecordTypeInfo> rtMapByName = Schema.SObjectType.Contact.getRecordTypeInfosByName();
	Schema.RecordTypeInfo rtInfo = rtMapByName.get(recordTypeName);
	Id recordTypeId = rtInfo.getRecordTypeId();
    Set<Id> contacts = new Set<Id>();
	
    if(recursiveTrigger.runOnce())
    {
		for(Contact cnt: Trigger.new) {
			if (cnt.recordtypeId == recordTypeId && cnt.Reviewer_Status__c.equals(text1))
				contacts.add(cnt.Id);
		}
		
		for (List<Contact> contactsToUpdate : [SELECT Id, Email, Name FROM Contact WHERE Id =:contacts]) {
		
			for (Contact con : contactsToUpdate) {
				//update routine
				//e.g. con.Email = 'newEmail'
			}
			Database.update(contactsToUpdate);
		}
     
	}
}


You are getting this error because you are using DLM statments and SOQL queries inside a FOR loop, which is highly not recommended and it is not a best practice, although sometimes is needed when you want to mass update records. Find out more about here: 


https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm (https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm" target="_blank)

https://developer.salesforce.com/page/Apex_Code_Best_Practices (https://developer.salesforce.com/page/Apex_Code_Best_Practices" target="_blank)

Regards

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.

ankushankush
Hi Zuinglio,

As per your suggestion I modified the code as below. Can you check and let me know if it is fine. Any inputs on this will be a great help.

//This is a trigger that updates the profile of a Community User based on their Reviewer status
trigger Updateuserprofile on contact (after update) {
    string text1=Label.New_Reviewer;
      String recordTypeName = Label.Contact_Record_Type; 
  Map<String,Schema.RecordTypeInfo> rtMapByName = Schema.SObjectType.Contact.getRecordTypeInfosByName();
  Schema.RecordTypeInfo rtInfo =  rtMapByName.get(recordTypeName);
  id recordTypeId = rtInfo.getRecordTypeId();
    
    if(Trigger.isafter && trigger.isUpdate)
    {
          if(recursiveTrigger.runOnce()){
              
              list<contact> cntct= new list<contact>();
  for(contact cnt:trigger.new){
      if(cnt.recordtypeID==recordTypeId && cnt.Reviewer_Status__c.equals(text1)){
          cntct.add(cnt);
          try{
          list<User> u=[select Id,Email,Username from User where contactid=:cntct];
          for(user usr:u){
           updateuserinformation.updateuser(usr.Id);
              }
}
      catch(exception e){
          
          
      }
      }
       else if(cnt.Reviewer_Status__c==null){
              
          }
  }
          }
 
              
    }
}

 
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello,

It's almost there, you are doing great but I must insist that you should not have neither DLM statements and SOQL queries inside a FOR loop.
 
for(contact cnt:trigger.new){
      if(cnt.recordtypeID==recordTypeId && cnt.Reviewer_Status__c.equals(text1)){
          cntct.add(cnt);
          try{
          list<User> u=[select Id,Email,Username from User where contactid=:cntct]; //you have do it outside the FOR loop
          for(user usr:u){
           updateuserinformation.updateuser(usr.Id);
              }

Have you tried the code that I posted?

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
ankushankush
Hi Zuinglio,


                 I have looked at your code and now modified as below hope this is fine. I see all the functionalities working just let me know if this is fine so that I ensure I follow this as a best practice. Thanks a lot for your guidance.

//This is a trigger that updates the profile of a Community User based on their Reviewer status
trigger Updateuserprofile on contact (after update) {
    string text1=Label.New_Reviewer;
      String recordTypeName = Label.Contact_Record_Type; 
  Map<String,Schema.RecordTypeInfo> rtMapByName = Schema.SObjectType.Contact.getRecordTypeInfosByName();
  Schema.RecordTypeInfo rtInfo =  rtMapByName.get(recordTypeName);
  id recordTypeId = rtInfo.getRecordTypeId();
    
    if(Trigger.isafter && trigger.isUpdate)
    {
          if(recursiveTrigger.runOnce()){
              
              list<contact> cntct= new list<contact>();
  for(contact cnt:trigger.new){
      if(cnt.recordtypeID==recordTypeId && cnt.Reviewer_Status__c.equals(text1))
          cntct.add(cnt);
          }
          try{
          list<User> u=[select Id,Email,Username from User where contactid=:cntct];
          for(user usr:u){
           updateuserinformation.updateuser(usr.Id);
              }
}
      catch(exception e){
          
          
      }
          }
      
      // else if(cntct.Reviewer_Status__c==null){
              
         // }
  
          }
 
              
    }
ankushankush

Hi Zuinglio,


Can you guide me for the best practice to avoid the soql query in the for loop. Code is working fine just want to follow best practice as well.

if(trigger.isbefore && trigger.isdelete)
    {
     
       list<COI_Expertise__c> coilist=new list<COI_Expertise__c>();
       list<panel_assignment__c> lp=[select Reviewer_Name__c,Panel__c,Active__c from panel_assignment__C where id=:Trigger.old];
        system.debug('The list is' +  lp);
   for(panel_assignment__c l:lp){
       Id ReviewerId=l.Reviewer_Name__c;
    Id PanelId=l.Panel__c;
   list<Research_Application__c> r=[select id from Research_Application__c where Panel__c=:panelId];
       for(Research_Application__c ra:r){
       Id rschappId=ra.Id;
           list<COI_Expertise__c> ci=[Select id,Reviewer_Name__c from COI_Expertise__c where Research_Application__c=:rschappId];
           for(COI_Expertise__c ce:ci){
               if(ReviewerId==ce.Reviewer_Name__c)
       ce.Active__c=false;
       coilist.add(ce);
   }
           }
       }
        update coilist;
    }
This was selected as the best answer