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
SarikaPSarikaP 

Need help to bulkify the code: Too many SOQL queries exception is coming up

Hello Everyone,

I need help in bulkifying the code below. The requirement is we have multicurrency org, so first check the currency and if currency is greater than 10k USD, a special T&C should appear in addition to regular T&C (if reg T&C not present already)
For amount greater than 10K, only regular T&C should appear.
Here's the code:

    public static void CheckTNC(List<Quote> Qlist){   
      
        Set<Id> setGrtAmtQTC= new Set<Id>();
        Set<Id> setLessAmtQTC = new Set<Id>();
        Set<Id> setdelCCQId = new Set<Id>();
        Set<Id> setremccqid = new Set<Id>();
        Set<Id> setqtcid = new Set<Id>();
        Set<Terms_and_Conditions_Quote__c> settoins = new Set<Terms_and_Conditions_Quote__c>();
        Set<Terms_and_Conditions_Quote__c> settodel = new Set<Terms_and_Conditions_Quote__c>();
        List<Terms_and_Conditions_Quote__c> lsttodel1 = new List<Terms_and_Conditions_Quote__c>();
        List<Terms_and_Conditions_Quote__c> lsttodel2 = new List<Terms_and_Conditions_Quote__c>();       
        List<Terms_and_Conditions_Quote__c> lsttoins1 = new List<Terms_and_Conditions_Quote__c>();
        List<Terms_and_Conditions_Quote__c> lsttoins2 = new List<Terms_and_Conditions_Quote__c>();
        List<Terms_and_Conditions_Quote__c> lstdelallTC = new List<Terms_and_Conditions_Quote__c>();
        List<Terms_and_Conditions_Quote__c> lstdelQTC = new List<Terms_and_Conditions_Quote__c>();
        List<Terms_and_Conditions_Quote__c> lstinsQTC = new List<Terms_and_Conditions_Quote__c>();
        Terms_and_Conditions__c tcspecial = [Select t.Id  From Terms_and_Conditions__c t where t.Id =: 'a0o60000000ZyW']; //less than 10K T&C
        Terms_and_Conditions__c tcregular = [Select t.Id  From Terms_and_Conditions__c t where t.Id =: 'a0o60000000Zy']; //regular T&C
       
        map<string,decimal> queriedcurrencies = new map<string,decimal>();
        List<Opportunity> lstAutoRenOpp = new List<Opportunity>();        
        Map<Id, Quote> mapq = new Map<id, Quote>();
        for(CurrencyType c : [SELECT ISOCode, ConversionRate FROM CurrencyType WHERE IsActive=TRUE]){
            queriedcurrencies.put(c.ISOCode,c.ConversionRate);
            System.debug('Curreny ' + c.ISOCode + 'with conversion rate' + c.ConversionRate);
        }
        mapq.putall(Qlist);
        for(Quote q: mapq.values())
        {
             
            if( q.TotalPrice > 0 )
            {         
                decimal convertedvalue = q.TotalPrice/queriedcurrencies.get(q.CurrencyISOCode);            
                if(q.TotalPrice > 10000)         
                    setGrtAmtQTC.add(q.id);           
                else          
                    setLessAmtQTC.add(q.Id);           
            }          
            
        }
        if(setGrtAmtQTC != null && setGrtAmtQTC.size() > 0)
        {
            Map<Id,Terms_and_Conditions_Quote__c> mapdelCCTCQuoteId = new Map<Id,Terms_and_Conditions_Quote__c>([select Id,Quote__c,Terms_and_Conditions__c from Terms_and_Conditions_Quote__c where quote__c in : setGrtAmtQTC and Terms_and_Conditions__c =: tcspecial.id]);
        
            System.debug('List size to delete credit card quote:' + mapdelCCTCQuoteId.size());
            if(mapdelCCTCQuoteId != null && mapdelCCTCQuoteId.size() > 0)
            { 
                lsttodel1.addall(mapdelCCTCQuoteId.values());                  
                for(Terms_and_Conditions_Quote__c obj: mapdelCCTCQuoteId.values())
                    setdelCCQId.add(mapdelCCTCQuoteId.get(obj.Id).Quote__c);
                    
                //check if quotes have regular T&C, as some products dont, make sure to add it if its not added
                Set<Terms_and_Conditions_Quote__c> setremnetpqid = new Set<Terms_and_Conditions_Quote__c>([select Quote__c from Terms_and_Conditions_Quote__c where quote__c in : setdelCCQId and Terms_and_Conditions__c =: tcregular.Id]); 
                system.debug('setremnetpqid'+setremnetpqid.size());
                if(setremnetpqid!= null && setremnetpqid.size() > 0)
                { 
                    for(Terms_and_Conditions_Quote__c objq: setremnetpqid)               
                    setdelCCQId.remove(objq.Quote__c);
               
                }
                                
                if(setdelCCQId != null && setdelCCQId.size() > 0)
                {
                    for(Id tcquoteid : setdelCCQId)
                    {
                        Terms_and_Conditions_Quote__c objTCNet = new Terms_and_Conditions_Quote__c(); 
                        objTCNet.Quote__c = tcquoteid; 
                        objTCNet.Terms_and_Conditions__c = tcregular.Id; 
                        lsttoins1.add(objTCNet);
                    }
                    
                }
                
            }
            
        }
        if(setLessAmtQTC != null && setLessAmtQTC.size() > 0)
        {
            Set <Terms_and_Conditions_Quote__c> settcqid = new Set<Terms_and_Conditions_Quote__c>( [select quote__c from Terms_and_Conditions_Quote__c where quote__c in : setLessAmtQTC and Terms_and_Conditions__c  =: tcspecial.id]);
            if( settcqid != null &&  settcqid.size() >0)
            {
                for(Terms_and_Conditions_Quote__c t: settcqid)                                     
                setremccqid.add(t.Quote__c); 
                setLessAmtQTC.removeall(setremccqid);   
            }
            if(setLessAmtQTC != null && setLessAmtQTC.size() > 0)
            {
                
                for(Id tcquoteid : setLessAmtQTC)
                {
                    Terms_and_Conditions_Quote__c objTCCC = new Terms_and_Conditions_Quote__c(); 
                    objTCCC.Quote__c = tcquoteid;
                    objTCCC.Terms_and_Conditions__c = tcspecial.id;
                    lsttoins2.add(objTCCC);                                  
                }
                
            }
        }  

        if(lsttodel1!= null &&  lsttodel1.size() > 0)
            settodel.addall(lsttodel1);
      
        if(settodel != null && settodel.size() > 0) 
        {
            lstdelQTC.addall(settodel);
            delete  lstdelQTC;
        }
        if( lsttoins1!= null &&  lsttoins1.size() > 0 )
            settoins.addall(lsttoins1);      
        if(lsttoins2!= null &&  lsttoins2.size() > 0)
            settoins.addall(lsttoins2); 
       
        if(settoins != null && settoins.size() > 0)
        {
            lstinsQTC.addall(settoins);
            insert  lstinsQTC;
        }
    }

 
Deepali KulshresthaDeepali Kulshrestha
Hi SarikaP,

The following error appears when you exceed the Execution Governors Limit (you can run up to a total 100 SOQL queries in a single call or context). 
System.LimitException: Too many SOQL queries: 101 error
All the SOQL queries in triggers fired from one call or context will be counted against the limit of 100.
Salesforce cannot disable or raise the Governors Limit.

Resolve the "Too many SOQL queries: 101" error
To fix the issue, change your code so that the number of SOQL fired is less than 100.

If you need to change the context, you can use @future annotation which will run the code asynchronously.
 
Best practices to avoid exceeding the Governors Limit
Since Apex runs on a multi-tenant platform, the Apex runtime engine strictly enforces limits to ensure code doesn't monopolize shared resources. 

Avoid SOQL queries that are inside FOR loops.
Follow the key coding principals for Apex Code in our Developer's Guide.

I suggest you visit this link,it may be helpful:

https://help.salesforce.com/articleView?id=000181404&type=1

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

 Thanks and Regards,
 Deepali Kulshrestha
 
SarikaPSarikaP
Hi Deepika,

Thanks for your response. But looking at the code above is there a way to bulkify it?