+ Start a Discussion
Madhuri KanthekarMadhuri Kanthekar 

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

Throwing error at line 12, when inserting (data loader) more than 100 records and Trigger works fine for less than 100 records
Please help, below is my trigger - 

trigger AccountTrigger on Account (before insert,before update){
    
    Set<String> groupIdSet = new Set<String>();
    for(Account acc : Trigger.New){
        groupIdSet.add(acc.Pro_Alpha_ID__c);
        system.debug('--Trigger size in for loop1-->'+Trigger.new.size());
        system.debug(groupIdSet);
    }
    
    Map<String,Account> parentAccMap = new Map<String,Account>();   
    List<Account> accList = new List<Account>();
    accList = [SELECT id,Pro_Alpha_ID__c FROM Account WHERE Pro_Alpha_ID__c IN: groupIdSet AND RecordtypeId =: Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()];
    for(Account acc : accList){
        parentAccMap.put(acc.Pro_Alpha_ID__c,acc);
        system.debug(parentAccMap);
    } 
    
    Map<String,List<Account>> accMap = new Map<String,List<Account>>(); 
    if(Trigger.isBefore){
        system.debug('--Trigger size in for loop3-->'+Trigger.new.size());
        
        for(Account acc : Trigger.New){
            if(acc.Pro_Alpha_ID__c != null && acc.ParentId == null && acc.RecordtypeId != Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()){
                if(parentAccMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                    acc.ParentId = parentAccMap.get(acc.Pro_Alpha_ID__c).Id;
                } else {
                    if(accMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                        accMap.get(acc.Pro_Alpha_ID__c).add(acc);
                    system.debug(accMap);

                    } else {
                        List<Account> accLst = new List<Account>();
                        accLst.add(acc);
                        system.debug(accLst);
                        accMap.put(acc.Pro_Alpha_ID__c,accLst); 
                        system.debug(accMap);
                    }
                 }
            }
         }
        List<Account> accountsToCreate = new List<Account>();
        
        for(String s : accMap.keySet()){
                                  
            Account accnt = new Account(RecordtypeId = Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId(),
                                        Pro_Alpha_ID__c = s,
                                        Name = accMap.get(s).get(0).Name,
                                        BillingStreet = accMap.get(s).get(0).BillingStreet,
                                        BillingCity = accMap.get(s).get(0).BillingCity,
                                        BillingState = accMap.get(s).get(0).BillingState,
                                        BillingCountry = accMap.get(s).get(0).BillingCountry,
                                        BillingPostalCode = accMap.get(s).get(0).BillingPostalCode
                                        );
                
                insert accnt;
                system.debug(accnt);
            
            for(Account acc : accMap.get(s)){
               system.debug(accMap.get(s)); 
               acc.ParentId = accnt.Id;
             }
            }
        }
}
Best Answer chosen by Madhuri Kanthekar
Balayesu ChilakalapudiBalayesu Chilakalapudi
Like Keyset() & values() are the methods of Map.

Try like this,
 
trigger AccountTrigger on Account (before insert,before update){
    
    Set<String> groupIdSet = new Set<String>();
    for(Account acc : Trigger.New){
        groupIdSet.add(acc.Pro_Alpha_ID__c);
        system.debug('--Trigger size in for loop1-->'+Trigger.new.size());
        system.debug(groupIdSet);
    }
    
    Map<String,Account> parentAccMap = new Map<String,Account>();   
    List<Account> accList = new List<Account>();
    accList = [SELECT id,Pro_Alpha_ID__c FROM Account WHERE Pro_Alpha_ID__c IN: groupIdSet AND RecordtypeId =: Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()];
    for(Account acc : accList){
        parentAccMap.put(acc.Pro_Alpha_ID__c,acc);
        system.debug(parentAccMap);
    } 
    
    Map<String,List<Account>> accMap = new Map<String,List<Account>>(); 
    if(Trigger.isBefore){
        system.debug('--Trigger size in for loop3-->'+Trigger.new.size());
        
        for(Account acc : Trigger.New){
            if(acc.Pro_Alpha_ID__c != null && acc.ParentId == null && acc.RecordtypeId != Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()){
                if(parentAccMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                    acc.ParentId = parentAccMap.get(acc.Pro_Alpha_ID__c).Id;
                } else {
                    if(accMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                        accMap.get(acc.Pro_Alpha_ID__c).add(acc);
                    system.debug(accMap);

                    } else {
                        List<Account> accLst = new List<Account>();
                        accLst.add(acc);
                        system.debug(accLst);
                        accMap.put(acc.Pro_Alpha_ID__c,accLst); 
                        system.debug(accMap);
                    }
                 }
            }
         }
        List<Account> accountsToCreate = new List<Account>();
        
        for(String s : accMap.keySet()){
                                  
            Account accnt = new Account(RecordtypeId = Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId(),
                                        Pro_Alpha_ID__c = s,
                                        Name = accMap.get(s).get(0).Name,
                                        BillingStreet = accMap.get(s).get(0).BillingStreet,
                                        BillingCity = accMap.get(s).get(0).BillingCity,
                                        BillingState = accMap.get(s).get(0).BillingState,
                                        BillingCountry = accMap.get(s).get(0).BillingCountry,
                                        BillingPostalCode = accMap.get(s).get(0).BillingPostalCode
                                        );
                
               // insert accnt;
              //  system.debug(accnt);
             accountsToCreate.add(accnt);            
          
            }
           insert accountsToCreate;
           //List<Account> mapaccs=new List<Account>():
           //mapaccs=accMap.values();
           for(Account a:accountsToCreate){
                   for(List<Account> mapacclist : accMap.values()){
                     for(Account acc:mapacclist){
                      if(a.Name==acc.Name){
               system.debug(accMap.get(s)); 
               acc.ParentId = a.Id;
                      }
                   }
               }
           }
        }
}

Let us know if it helps.

All Answers

Balayesu ChilakalapudiBalayesu Chilakalapudi
Use list to insert all the accounts at once, like this,
 
trigger AccountTrigger on Account (before insert,before update){
    
    Set<String> groupIdSet = new Set<String>();
    for(Account acc : Trigger.New){
        groupIdSet.add(acc.Pro_Alpha_ID__c);
        system.debug('--Trigger size in for loop1-->'+Trigger.new.size());
        system.debug(groupIdSet);
    }
    
    Map<String,Account> parentAccMap = new Map<String,Account>();   
    List<Account> accList = new List<Account>();
    accList = [SELECT id,Pro_Alpha_ID__c FROM Account WHERE Pro_Alpha_ID__c IN: groupIdSet AND RecordtypeId =: Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()];
    for(Account acc : accList){
        parentAccMap.put(acc.Pro_Alpha_ID__c,acc);
        system.debug(parentAccMap);
    } 
    
    Map<String,List<Account>> accMap = new Map<String,List<Account>>(); 
    if(Trigger.isBefore){
        system.debug('--Trigger size in for loop3-->'+Trigger.new.size());
        
        for(Account acc : Trigger.New){
            if(acc.Pro_Alpha_ID__c != null && acc.ParentId == null && acc.RecordtypeId != Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()){
                if(parentAccMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                    acc.ParentId = parentAccMap.get(acc.Pro_Alpha_ID__c).Id;
                } else {
                    if(accMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                        accMap.get(acc.Pro_Alpha_ID__c).add(acc);
                    system.debug(accMap);

                    } else {
                        List<Account> accLst = new List<Account>();
                        accLst.add(acc);
                        system.debug(accLst);
                        accMap.put(acc.Pro_Alpha_ID__c,accLst); 
                        system.debug(accMap);
                    }
                 }
            }
         }
        List<Account> accountsToCreate = new List<Account>();
        
        for(String s : accMap.keySet()){
                                  
            Account accnt = new Account(RecordtypeId = Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId(),
                                        Pro_Alpha_ID__c = s,
                                        Name = accMap.get(s).get(0).Name,
                                        BillingStreet = accMap.get(s).get(0).BillingStreet,
                                        BillingCity = accMap.get(s).get(0).BillingCity,
                                        BillingState = accMap.get(s).get(0).BillingState,
                                        BillingCountry = accMap.get(s).get(0).BillingCountry,
                                        BillingPostalCode = accMap.get(s).get(0).BillingPostalCode
                                        );
                
               // insert accnt;
              //  system.debug(accnt);
             accountsToCreate.add(accnt);            
          
            }
           insert accountsToCreate;
           for(Account a:accountsToCreate){
                   for(Account acc : accMap.get(s)){
               system.debug(accMap.get(s)); 
               acc.ParentId = a.Id;
               }
           }
        }
}

Let us know if it helps.
Madhuri KanthekarMadhuri Kanthekar
error at line 63; variable does not exist:s
Balayesu ChilakalapudiBalayesu Chilakalapudi
This will work
trigger AccountTrigger on Account (before insert,before update){
    
    Set<String> groupIdSet = new Set<String>();
    for(Account acc : Trigger.New){
        groupIdSet.add(acc.Pro_Alpha_ID__c);
        system.debug('--Trigger size in for loop1-->'+Trigger.new.size());
        system.debug(groupIdSet);
    }
    
    Map<String,Account> parentAccMap = new Map<String,Account>();   
    List<Account> accList = new List<Account>();
    accList = [SELECT id,Pro_Alpha_ID__c FROM Account WHERE Pro_Alpha_ID__c IN: groupIdSet AND RecordtypeId =: Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()];
    for(Account acc : accList){
        parentAccMap.put(acc.Pro_Alpha_ID__c,acc);
        system.debug(parentAccMap);
    } 
    
    Map<String,List<Account>> accMap = new Map<String,List<Account>>(); 
    if(Trigger.isBefore){
        system.debug('--Trigger size in for loop3-->'+Trigger.new.size());
        
        for(Account acc : Trigger.New){
            if(acc.Pro_Alpha_ID__c != null && acc.ParentId == null && acc.RecordtypeId != Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()){
                if(parentAccMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                    acc.ParentId = parentAccMap.get(acc.Pro_Alpha_ID__c).Id;
                } else {
                    if(accMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                        accMap.get(acc.Pro_Alpha_ID__c).add(acc);
                    system.debug(accMap);

                    } else {
                        List<Account> accLst = new List<Account>();
                        accLst.add(acc);
                        system.debug(accLst);
                        accMap.put(acc.Pro_Alpha_ID__c,accLst); 
                        system.debug(accMap);
                    }
                 }
            }
         }
        List<Account> accountsToCreate = new List<Account>();
        
        for(String s : accMap.keySet()){
                                  
            Account accnt = new Account(RecordtypeId = Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId(),
                                        Pro_Alpha_ID__c = s,
                                        Name = accMap.get(s).get(0).Name,
                                        BillingStreet = accMap.get(s).get(0).BillingStreet,
                                        BillingCity = accMap.get(s).get(0).BillingCity,
                                        BillingState = accMap.get(s).get(0).BillingState,
                                        BillingCountry = accMap.get(s).get(0).BillingCountry,
                                        BillingPostalCode = accMap.get(s).get(0).BillingPostalCode
                                        );
                
               // insert accnt;
              //  system.debug(accnt);
             accountsToCreate.add(accnt);            
          
            }
           insert accountsToCreate;
           for(Account a:accountsToCreate){
                   for(Account acc : accMap.values()){
                      if(a.Name==acc.Name){
               system.debug(accMap.get(s)); 
               acc.ParentId = a.Id;
                      }
               }
           }
        }
}

 
Madhuri KanthekarMadhuri Kanthekar
Line 62: Loop variable must be of type List<Account>
Balayesu ChilakalapudiBalayesu Chilakalapudi
Please Use below code
 
trigger AccountTrigger on Account (before insert,before update){
    
    Set<String> groupIdSet = new Set<String>();
    for(Account acc : Trigger.New){
        groupIdSet.add(acc.Pro_Alpha_ID__c);
        system.debug('--Trigger size in for loop1-->'+Trigger.new.size());
        system.debug(groupIdSet);
    }
    
    Map<String,Account> parentAccMap = new Map<String,Account>();   
    List<Account> accList = new List<Account>();
    accList = [SELECT id,Pro_Alpha_ID__c FROM Account WHERE Pro_Alpha_ID__c IN: groupIdSet AND RecordtypeId =: Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()];
    for(Account acc : accList){
        parentAccMap.put(acc.Pro_Alpha_ID__c,acc);
        system.debug(parentAccMap);
    } 
    
    Map<String,List<Account>> accMap = new Map<String,List<Account>>(); 
    if(Trigger.isBefore){
        system.debug('--Trigger size in for loop3-->'+Trigger.new.size());
        
        for(Account acc : Trigger.New){
            if(acc.Pro_Alpha_ID__c != null && acc.ParentId == null && acc.RecordtypeId != Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()){
                if(parentAccMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                    acc.ParentId = parentAccMap.get(acc.Pro_Alpha_ID__c).Id;
                } else {
                    if(accMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                        accMap.get(acc.Pro_Alpha_ID__c).add(acc);
                    system.debug(accMap);

                    } else {
                        List<Account> accLst = new List<Account>();
                        accLst.add(acc);
                        system.debug(accLst);
                        accMap.put(acc.Pro_Alpha_ID__c,accLst); 
                        system.debug(accMap);
                    }
                 }
            }
         }
        List<Account> accountsToCreate = new List<Account>();
        
        for(String s : accMap.keySet()){
                                  
            Account accnt = new Account(RecordtypeId = Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId(),
                                        Pro_Alpha_ID__c = s,
                                        Name = accMap.get(s).get(0).Name,
                                        BillingStreet = accMap.get(s).get(0).BillingStreet,
                                        BillingCity = accMap.get(s).get(0).BillingCity,
                                        BillingState = accMap.get(s).get(0).BillingState,
                                        BillingCountry = accMap.get(s).get(0).BillingCountry,
                                        BillingPostalCode = accMap.get(s).get(0).BillingPostalCode
                                        );
                
               // insert accnt;
              //  system.debug(accnt);
             accountsToCreate.add(accnt);            
          
            }
           insert accountsToCreate;
           List<Account> mapaccs=new List<Account>():
           mapaccs=accMap.values();
           for(Account a:accountsToCreate){
                   for(Account acc : mapaccs){
                      if(a.Name==acc.Name){
               system.debug(accMap.get(s)); 
               acc.ParentId = a.Id;
                      }
               }
           }
        }
}

 
Madhuri KanthekarMadhuri Kanthekar

No its not working, error at same line. Am not getting how to caste this keyset to a list

Illegal assignment from List<List<Account>> to List<Account>

Balayesu ChilakalapudiBalayesu Chilakalapudi
Like Keyset() & values() are the methods of Map.

Try like this,
 
trigger AccountTrigger on Account (before insert,before update){
    
    Set<String> groupIdSet = new Set<String>();
    for(Account acc : Trigger.New){
        groupIdSet.add(acc.Pro_Alpha_ID__c);
        system.debug('--Trigger size in for loop1-->'+Trigger.new.size());
        system.debug(groupIdSet);
    }
    
    Map<String,Account> parentAccMap = new Map<String,Account>();   
    List<Account> accList = new List<Account>();
    accList = [SELECT id,Pro_Alpha_ID__c FROM Account WHERE Pro_Alpha_ID__c IN: groupIdSet AND RecordtypeId =: Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()];
    for(Account acc : accList){
        parentAccMap.put(acc.Pro_Alpha_ID__c,acc);
        system.debug(parentAccMap);
    } 
    
    Map<String,List<Account>> accMap = new Map<String,List<Account>>(); 
    if(Trigger.isBefore){
        system.debug('--Trigger size in for loop3-->'+Trigger.new.size());
        
        for(Account acc : Trigger.New){
            if(acc.Pro_Alpha_ID__c != null && acc.ParentId == null && acc.RecordtypeId != Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()){
                if(parentAccMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                    acc.ParentId = parentAccMap.get(acc.Pro_Alpha_ID__c).Id;
                } else {
                    if(accMap.keySet().contains(acc.Pro_Alpha_ID__c)){
                        accMap.get(acc.Pro_Alpha_ID__c).add(acc);
                    system.debug(accMap);

                    } else {
                        List<Account> accLst = new List<Account>();
                        accLst.add(acc);
                        system.debug(accLst);
                        accMap.put(acc.Pro_Alpha_ID__c,accLst); 
                        system.debug(accMap);
                    }
                 }
            }
         }
        List<Account> accountsToCreate = new List<Account>();
        
        for(String s : accMap.keySet()){
                                  
            Account accnt = new Account(RecordtypeId = Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId(),
                                        Pro_Alpha_ID__c = s,
                                        Name = accMap.get(s).get(0).Name,
                                        BillingStreet = accMap.get(s).get(0).BillingStreet,
                                        BillingCity = accMap.get(s).get(0).BillingCity,
                                        BillingState = accMap.get(s).get(0).BillingState,
                                        BillingCountry = accMap.get(s).get(0).BillingCountry,
                                        BillingPostalCode = accMap.get(s).get(0).BillingPostalCode
                                        );
                
               // insert accnt;
              //  system.debug(accnt);
             accountsToCreate.add(accnt);            
          
            }
           insert accountsToCreate;
           //List<Account> mapaccs=new List<Account>():
           //mapaccs=accMap.values();
           for(Account a:accountsToCreate){
                   for(List<Account> mapacclist : accMap.values()){
                     for(Account acc:mapacclist){
                      if(a.Name==acc.Name){
               system.debug(accMap.get(s)); 
               acc.ParentId = a.Id;
                      }
                   }
               }
           }
        }
}

Let us know if it helps.
This was selected as the best answer
Balayesu ChilakalapudiBalayesu Chilakalapudi
also comment line 67, system.debug(accMap.get(s));
Balayesu ChilakalapudiBalayesu Chilakalapudi
Mark any answer that helped you as the best answer to help some one
Madhuri KanthekarMadhuri Kanthekar
ya sure...I was testing with bulk....It worked...Thanks a lot !
rajat Maheshwari 6rajat Maheshwari 6
trigger AccountTrigger on Account (before insert,before update){
    
    Set<String> groupIdSet = new Set<String>();
	Map<String,Account> parentAccMap = new Map<String,Account>();
	List<Account> accList = new List<Account>();
	List<Account> accountsToCreate = new List<Account>();
	Map<String,List<Account>> accMap = new Map<String,List<Account>>(); 
	
    for(Account acc : Trigger.New){
        groupIdSet.add(acc.Pro_Alpha_ID__c);
        system.debug('--Trigger size in for loop1-->'+Trigger.new.size());
        system.debug(groupIdSet);
    }
    
      
    
    accList = [SELECT id,Pro_Alpha_ID__c FROM Account WHERE Pro_Alpha_ID__c IN: groupIdSet AND RecordtypeId =: Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()];
  
  for(Account acc : accList)
  {
        parentAccMap.put(acc.Pro_Alpha_ID__c,acc);
        system.debug(parentAccMap);
  } 
    
   
    if(Trigger.isBefore){
        system.debug('--Trigger size in for loop3-->'+Trigger.new.size());
        
        for(Account acc : Trigger.New){
            if(acc.Pro_Alpha_ID__c != null && acc.ParentId == null && acc.RecordtypeId != Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId()){
                if(parentAccMap!=null && parentAccMap.contains(acc.Pro_Alpha_ID__c)){
                    acc.ParentId = parentAccMap.get(acc.Pro_Alpha_ID__c).Id;
                } 
				
				if(accMap!=null && accMap.contains(acc.Pro_Alpha_ID__c))
				  {
				     
				     List<Account> lst_Account =  accMap.get(acc.Pro_Alpha_ID__c);
					 lst_Account.add(acc);
                     accMap.put(acc.Pro_Alpha_ID__c,lst_Account);
                  } else {
                        
                        accMap.put(acc.Pro_Alpha_ID__c,new List<Account>{acc}); 
                        system.debug(accMap);
                    }
                 }
            }
         }
       
        
        for(String s : accMap.keySet()){
                                  
            Account accnt = new Account(RecordtypeId = Schema.SObjectType.Account.getRecordTypeInfosByName().get('EHG Group').getRecordTypeId(),
                                        Pro_Alpha_ID__c = s,
                                        Name = accMap.get(s).get(0).Name,
                                        BillingStreet = accMap.get(s).get(0).BillingStreet,
                                        BillingCity = accMap.get(s).get(0).BillingCity,
                                        BillingState = accMap.get(s).get(0).BillingState,
                                        BillingCountry = accMap.get(s).get(0).BillingCountry,
                                        BillingPostalCode = accMap.get(s).get(0).BillingPostalCode
                                        );
                
               // insert accnt;
              //  system.debug(accnt);
             accountsToCreate.add(accnt);            
          
            }
			
			if(accountsToCreate!=null && !accountsToCreate.isEmpty())
			  {
                 insert accountsToCreate;
        
   for(Account a:accountsToCreate)
     {
       if(accMap!=null && accMap.containsKey(a.Name))
            {
                 for(Account acnt_Recd : accMap.get(a.Name))
                     {

                         acnt_Recd.ParentId = a.Id;
                      }
               }
     }
        }
}

Use this code snippet and let me know the results :)

Thanks
Rajat Maheshwari
rajatzmaheshwari@gmail.com