+ Start a Discussion
Thayalan Guhathashan 11Thayalan Guhathashan 11 

System.LimitException

Hi there,
The code works for normal scenario with single lead, but the code does not support bulkifying. When Multiple leads try to convert same time, I am getting a "System.LimitException: Too many SOQL queries" error.

trigger LeadIntegrationTrigger on Lead (after update, after insert)
{
    
    List<String> LeadNames = new List<String>{};
    Set<ID> Oppid =new Set<ID>();
    List<Contact>lcon = New List<Contact>();
    List<OpportunityLineItem> ListLine = New List<OpportunityLineItem>();
    List<Opportunity> OppList = New List<Opportunity>();
    
 
    if (Trigger.isInsert) {
        for(Lead aLead: Trigger.new){
          
                if((aLead.isconverted==false) && (aLead.Status == 'Qualified - Enrolled')) {
                    Database.LeadConvert lc = new database.LeadConvert();
                    lc.setLeadId(aLead.Id);
                    lc.convertedStatus = 'Qualified - Enrolled';
                    lc.setDoNotCreateOpportunity(false);
                    Database.LeadConvertResult lcr = Database.convertLead(lc);
                    System.assert(lcr.isSuccess());
                    //aLead.IsConverted=false;
                }
                 
        }
    
    }
    
    if (Trigger.isUpdate ) {
        List<OpportunityStudent__c> aNewStudents = new List<OpportunityStudent__c>();
        Map<Id, Lead> aLeadToCheck = new Map<Id, Lead>();
        for(Integer x = 0; x < Trigger.new.size(); x++)
            {        
                    if((Trigger.new[x].isconverted==false) && (Trigger.new[x].Status == 'Qualified - Enrolled'&&Trigger.new[x].Status!=Trigger.old[x].Status)) {
                    Database.LeadConvert lc = new database.LeadConvert();
                    lc.setLeadId(Trigger.new[x].Id);
                    lc.convertedStatus = 'Qualified - Enrolled';
                    lc.setDoNotCreateOpportunity(false);
                    Database.LeadConvertResult lcr = Database.convertLead(lc);
                    System.assert(lcr.isSuccess());
                   }
        
        
            if (Trigger.new[x].IsConverted == true && Trigger.oldMap.get(Trigger.new[x].Id).IsConverted == false&& Trigger.new[x].ConvertedOpportunityId != null && Trigger.new[x].ConvertedContactId != null){  
                aLeadToCheck.put(Trigger.new[x].ConvertedOpportunityId, Trigger.new[x]);
            }
        }
         
        //Auto add student on Opportuntiy
        List<Opportunity> anOppList = [SELECT Id, AccountId, Account.Name,account.recordtype.name FROM Opportunity WHERE Id IN :aLeadToCheck.keySet()];
        
        for (Opportunity anOpp: anOppList){
        
           if (anOpp.AccountId != null){
                Lead aLead = aLeadToCheck.get(anOpp.Id);
                //update Opportunity Detail
               
                anOpp.Training_Program__c=aLead.Product__c;
                OppList.add(anOpp);
                //
                Contact con=[SELECT id,Avetmiss_At_School__c,Contact_Method__c,Avetmiss_Country_Of_Birth__c,
                Avetmiss_Disability__c,Avetmiss_Disability_List__c,Gender__c,Avetmiss_Highest_School_Level__c,
                Avetmiss_Indigenous_Status__c,Avetmiss_Labour_Force_Status__c,Avetmiss_Learner_Unique_Identifier__c,
                Avetmiss_Main_Language__c,Avetmiss_Nationality__c,Avetmiss_Prior_Education__c,Prior_Education__c,Avetmiss_Prior_Education_List__c,
                Avetmiss_School_Identifier__c,Avetmiss_Spoken_English_Proefficiency__c,Avetmiss_Year_Highest_School_Level__c,BirthDate
                FROM contact WHERE id=:aLead.ConvertedContactId];
                system.debug('Cloning');
                //Clone data to personAccount
                con.Avetmiss_At_School__c=aLead.Avetmiss_At_School__c;
               
                               
            
                con.Avetmiss_Year_Highest_School_Level__c = aLead.Avetmiss_Year_Highest_School_Level__c;
                lcon.add(con);
                // end clone data
                
                //Add New PricebookEntry
                if(aLead.Product__c!=null){
                    PricebookEntry Pricebook=[SELECT id FROM PricebookEntry WHERE Product2Id=:aLead.Product__c Limit 1];
                  
                }                    
                OpportunityStudent__c aNewStudent = new OpportunityStudent__c();
                aNewStudent.Contact__c = aLead.ConvertedContactId;
                aNewStudent.Opportunity__c = aLead.ConvertedOpportunityId;                                          
                Oppid.add(aNewStudent.Opportunity__c);
                aNewStudents.add(aNewStudent);
                system.debug('@@@@@ CCCC');
            }
            else{
                Oppid.add(anOpp.id);
            }
        }
        
        //Prevent Duplicate Manage student [Person Acccount]
        try{
            List <OpportunityContactRole> Role=[SELECT id,opportunityid FROM OpportunityContactRole WHERE Opportunityid in : Oppid];
            delete Role;
        }
        catch(System.QueryException e){}
        
        insert aNewStudents;
        //insert ListLine;
        update OppList;
        Update lcon;   
        List<OpportunityStudent__c> ListStudent=[SELECT id,Opportunity__c,Contact__c FROM OpportunityStudent__c WHERE Opportunity__c in:Oppid];
        IF(ListStudent.size()>1){
            delete ListStudent[1];
        }    
    }
}
Raj VakatiRaj Vakati
In line number 60 you have SOQL inside the for loop ..bulkifiy it by using it out from the forlopp 


 for (Opportunity anOpp: anOppList){
        
           if (anOpp.AccountId != null){
                Lead aLead = aLeadToCheck.get(anOpp.Id);
                //update Opportunity Detail
               
                anOpp.Training_Program__c=aLead.Product__c;
                OppList.add(anOpp);
                //
                Contact con=[SELECT id,Avetmiss_At_School__c,Contact_Method__c,Avetmiss_Country_Of_Birth__c,
                Avetmiss_Disability__c,Avetmiss_Disability_List__c,Gender__c,Avetmiss_Highest_School_Level__c,
                Avetmiss_Indigenous_Status__c,Avetmiss_Labour_Force_Status__c,Avetmiss_Learner_Unique_Identifier__c,
                Avetmiss_Main_Language__c,Avetmiss_Nationality__c,Avetmiss_Prior_Education__c,Prior_Education__c,Avetmiss_Prior_Education_List__c,
                Avetmiss_School_Identifier__c,Avetmiss_Spoken_English_Proefficiency__c,Avetmiss_Year_Highest_School_Level__c,BirthDate
                FROM contact WHERE id=:aLead.ConvertedContactId];
                system.debug('Cloning');
                //Clone data to personAccount
                con.Avetmiss_At_School__c=aLead.Avetmiss_At_School__c;
               
Thayalan Guhathashan 11Thayalan Guhathashan 11
Thanks Raj, do u have an idea on how to put that Contact SOQL outsiode the for loop?
Thayalan Guhathashan 11Thayalan Guhathashan 11
Hi Raj Vakati ,
I moved the query out from the loop, still I am getting the System.LimitException: Too many SOQL queries: 101.  through the process builder:

trigger LeadIntegrationTrigger on Lead (after update, after insert)
{
    
    List<String> LeadNames = new List<String>{};
    Set<ID> Oppid =new Set<ID>();
    List<Contact>lcon = New List<Contact>();
    List<OpportunityLineItem> ListLine = New List<OpportunityLineItem>();
    List<Opportunity> OppList = New List<Opportunity>();
    
    //After Save and status = Qualifield will auto convert
    if (Trigger.isInsert) {
        for(Lead aLead: Trigger.new){
            //try{
                if((aLead.isconverted==false) && (aLead.Status == 'Qualified - Enrolled')) {
                    Database.LeadConvert lc = new database.LeadConvert();
                    lc.setLeadId(aLead.Id);
                    lc.convertedStatus = 'Qualified - Enrolled';
                    lc.setDoNotCreateOpportunity(false);
                    Database.LeadConvertResult lcr = Database.convertLead(lc);
                    System.assert(lcr.isSuccess());
                    //aLead.IsConverted=false;
                }
            //}
            // Validate If Phone Lead wrong
            //catch(System.DmlException ee){
              //  aLead.addError('Phone number must contains 10 digit and start with 0 Or Gender is not input');
            //}        
        }
    
    }
    
    if (Trigger.isUpdate ) {
        List<OpportunityStudent__c> aNewStudents = new List<OpportunityStudent__c>();
        Map<Id, Lead> aLeadToCheck = new Map<Id, Lead>();
        Map<Id, Lead> aContactToCheck = new Map<Id, Lead>();
        
        for(Integer x = 0; x < Trigger.new.size(); x++)
            {        
                    if((Trigger.new[x].isconverted==false) && (Trigger.new[x].Status == 'Qualified - Enrolled'&&Trigger.new[x].Status!=Trigger.old[x].Status)) {
                    Database.LeadConvert lc = new database.LeadConvert();
                    lc.setLeadId(Trigger.new[x].Id);
                    lc.convertedStatus = 'Qualified - Enrolled';
                    lc.setDoNotCreateOpportunity(false);
                    Database.LeadConvertResult lcr = Database.convertLead(lc);
                    System.assert(lcr.isSuccess());
                   }
        
        
            if (Trigger.new[x].IsConverted == true && Trigger.oldMap.get(Trigger.new[x].Id).IsConverted == false&& Trigger.new[x].ConvertedOpportunityId != null && Trigger.new[x].ConvertedContactId != null){  
                aLeadToCheck.put(Trigger.new[x].ConvertedOpportunityId, Trigger.new[x]);
                aContactToCheck.put(Trigger.new[x].ConvertedContactId, Trigger.new[x]);
            }
        }
         
        //Auto add student on Opportuntiy
        List<Opportunity> anOppList = [SELECT Id, AccountId, Account.Name,account.recordtype.name FROM Opportunity WHERE Id IN :aLeadToCheck.keySet()];
        List<Contact> acontactList = [SELECT Id,Avetmiss_At_School__c,Contact_Method__c,Avetmiss_Country_Of_Birth__c,
                Avetmiss_Disability__c,Avetmiss_Disability_List__c,Gender__c,Avetmiss_Highest_School_Level__c,
                Avetmiss_Indigenous_Status__c,Avetmiss_Labour_Force_Status__c,Avetmiss_Learner_Unique_Identifier__c,
                Avetmiss_Main_Language__c,Avetmiss_Nationality__c,Avetmiss_Prior_Education__c,Prior_Education__c,Avetmiss_Prior_Education_List__c,
                Avetmiss_School_Identifier__c,Avetmiss_Spoken_English_Proefficiency__c,Avetmiss_Year_Highest_School_Level__c,BirthDate
                FROM Contact  WHERE Id IN :aContactToCheck.keySet()];
        
        
       for(Contact aCon:acontactList) {
             if (aCon.Id != NULL){
                   Lead myLead = aContactToCheck.get(aCon.Id);
             
                aCon.Avetmiss_At_School__c=myLead.Avetmiss_At_School__c;
                aCon.Birthdate=myLead.Birthdate__c;
                // Making Mandatory 'Email' as the Contact Method
                aCon.Contact_Method__c= 'Email';
                aCon.Avetmiss_Country_Of_Birth__c=myLead.Avetmiss_Country_Of_Birth__c;
                aCon.Avetmiss_Disability__c=myLead.Avetmiss_Disability__c;
                aCon.Avetmiss_Disability_List__c=myLead.Avetmiss_Disability_List__c;
                aCon.Gender__c=myLead.Gender__c;
                aCon.Avetmiss_Highest_School_Level__c=myLead.Avetmiss_Highest_School_Level__c;
                aCon.Avetmiss_Indigenous_Status__c = myLead.Avetmiss_Indigenous_Status__c;
                aCon.Avetmiss_Labour_Force_Status__c= myLead.Avetmiss_Labour_Force_Status__c;
                aCon.Avetmiss_Learner_Unique_Identifier__c = myLead.Avetmiss_Learner_Unique_Identifier__c;
                aCon.Avetmiss_Main_Language__c = myLead.Avetmiss_Main_Language__c;
                aCon.Avetmiss_Nationality__c = myLead.Avetmiss_Nationality__c;
                aCon.Avetmiss_Prior_Education__c = myLead.Avetmiss_Prior_Education__c;
                aCon.Town_of_Birth__c = myLead.Town_of_Birth__c;
                aCon.State_of_Birth__c = myLead.State_of_Birth__c;
                aCon.MobilePhone = myLead.MobilePhone;
                aCon.Email = myLead.Email;
                aCon.Login__c = myLead.Email;
                aCon.Prior_Education__c = myLead.Prior_Education__c;
                aCon.Disability__c= myLead.Disability__c;
                aCon.Avetmiss_Prior_Education_List__c = myLead.Avetmiss_Prior_Education_List__c;
                aCon.Avetmiss_School_Identifier__c = myLead.Avetmiss_School_Identifier__c;
                aCon.Avetmiss_Spoken_English_Proefficiency__c = myLead.Avetmiss_Spoken_English_Proefficiency__c;
                aCon.Avetmiss_Year_Highest_School_Level__c = myLead.Avetmiss_Year_Highest_School_Level__c;
                }
           lcon.add(aCon);
       }
        
        
        
        
        
        
        
        
        
        for (Opportunity anOpp: anOppList){
        
           if (anOpp.AccountId != null){
                Lead aLead = aLeadToCheck.get(anOpp.Id);
                //update Opportunity Detail
                anOpp.Course_Amount__c=aLead.Course_Amount__c;
                anOpp.Course_Start_Date__c=aLead.Course_Start_Date__c;
                anOpp.Course_End_Date__c=aLead.Course_End_Date__c;
                anOpp.Training_Program__c=aLead.Product__c;
                OppList.add(anOpp);
               
               /*
                //
                Contact con=[SELECT id,Avetmiss_At_School__c,Contact_Method__c,Avetmiss_Country_Of_Birth__c,
                Avetmiss_Disability__c,Avetmiss_Disability_List__c,Gender__c,Avetmiss_Highest_School_Level__c,
                Avetmiss_Indigenous_Status__c,Avetmiss_Labour_Force_Status__c,Avetmiss_Learner_Unique_Identifier__c,
                Avetmiss_Main_Language__c,Avetmiss_Nationality__c,Avetmiss_Prior_Education__c,Prior_Education__c,Avetmiss_Prior_Education_List__c,
                Avetmiss_School_Identifier__c,Avetmiss_Spoken_English_Proefficiency__c,Avetmiss_Year_Highest_School_Level__c,BirthDate
                FROM contact WHERE id=:aLead.ConvertedContactId];
                system.debug('Cloning');
                //Clone data to personAccount
                con.Avetmiss_At_School__c=aLead.Avetmiss_At_School__c;
                con.Birthdate=aLead.Birthdate__c;
                // Making Mandatory 'Email' as the Contact Method
                con.Contact_Method__c= 'Email';
                con.Avetmiss_Country_Of_Birth__c=aLead.Avetmiss_Country_Of_Birth__c;
                con.Avetmiss_Disability__c=aLead.Avetmiss_Disability__c;
                con.Avetmiss_Disability_List__c=aLead.Avetmiss_Disability_List__c;
                con.Gender__c=aLead.Gender__c;
                con.Avetmiss_Highest_School_Level__c=aLead.Avetmiss_Highest_School_Level__c;
                con.Avetmiss_Indigenous_Status__c = aLead.Avetmiss_Indigenous_Status__c;
                con.Avetmiss_Labour_Force_Status__c= aLead.Avetmiss_Labour_Force_Status__c;
                con.Avetmiss_Learner_Unique_Identifier__c = aLead.Avetmiss_Learner_Unique_Identifier__c;
                con.Avetmiss_Main_Language__c = aLead.Avetmiss_Main_Language__c;
                con.Avetmiss_Nationality__c = aLead.Avetmiss_Nationality__c;
                con.Avetmiss_Prior_Education__c = aLead.Avetmiss_Prior_Education__c;
                con.Town_of_Birth__c = aLead.Town_of_Birth__c;
                con.State_of_Birth__c = aLead.State_of_Birth__c;
                               
                con.MobilePhone = aLead.MobilePhone;
                con.Email = aLead.Email;
                con.Login__c = aLead.Email;
                con.Prior_Education__c = aLead.Prior_Education__c;
                con.Disability__c= aLead.Disability__c;
                con.Avetmiss_Prior_Education_List__c = aLead.Avetmiss_Prior_Education_List__c;
                con.Avetmiss_School_Identifier__c = aLead.Avetmiss_School_Identifier__c;
                con.Avetmiss_Spoken_English_Proefficiency__c = aLead.Avetmiss_Spoken_English_Proefficiency__c;
                con.Avetmiss_Year_Highest_School_Level__c = aLead.Avetmiss_Year_Highest_School_Level__c;
                lcon.add(con);
                // end clone data
                
                */
                
                //Add New PricebookEntry
                if(aLead.Product__c!=null){
                    PricebookEntry Pricebook=[SELECT id FROM PricebookEntry WHERE Product2Id=:aLead.Product__c Limit 1];
                    /*PricebookEntry Pricebook = new PricebookEntry();
                    Pricebook.Pricebook2Id=Pbook.id;
                    Pricebook.Product2Id=aLead.Product__c;
                    Pricebook.UseStandardPrice=true;
                    insert Pricebook;
                    */
                    //
                    //Add Product Line
                    /*
                    OpportunityLineItem OppLi = new OpportunityLineItem();
                    OppLi.OpportunityId=anOpp.id;
                   
                    OppLi.PricebookEntryId=Pricebook.id;
                    OppLi.Quantity=1;
                    OppLi.UnitPrice=1000;
                    
                    ListLine.add(OppLi);
                    */
                }                    
                OpportunityStudent__c aNewStudent = new OpportunityStudent__c();
                aNewStudent.Contact__c = aLead.ConvertedContactId;
                aNewStudent.Opportunity__c = aLead.ConvertedOpportunityId;                                          
                Oppid.add(aNewStudent.Opportunity__c);
                aNewStudents.add(aNewStudent);
                system.debug('@@@@@ CCCC');
            }
            else{
                Oppid.add(anOpp.id);
            }
        }
        
        //Prevent Duplicate Manage student [Person Acccount]
        try{
            List <OpportunityContactRole> Role=[SELECT id,opportunityid FROM OpportunityContactRole WHERE Opportunityid in : Oppid];
            delete Role;
        }
        catch(System.QueryException e){}
        
        insert aNewStudents;
        //insert ListLine;
        update OppList;
        update lcon;   
        List<OpportunityStudent__c> ListStudent=[SELECT id,Opportunity__c,Contact__c FROM OpportunityStudent__c WHERE Opportunity__c in:Oppid];
        IF(ListStudent.size()>1){
            delete ListStudent[1];
        }    
    }
}