Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ManoharSFManoharSF 

Too many SOQL queries: 101

Hi there,

 

what can be changed in this to avoid 'Too many SOQL queries: 101' issue?

 

thanks

Manohar

 

 

trigger UpdateApprovalPercentForRest on Opportunity (after delete, after undelete, after update) {

    List<Opportunity> buildsToUpdate = new List<Opportunity>{};

    for( Opportunity parent: Trigger.new)
    {                                                    
        List<Opportunity> restBuildswiththePartner =[SELECT Partner_Cap_Reached__c , Build_Partner__c,Construction_Approval_Payment__c,
                                                                StageName,Substantial_Completion_Payment__c 
                                                                FROM Opportunity WHERE Build_Partner_ID__c = :parent.Build_Partner_ID__c AND 
                                                                StageName NOT IN ('Construction Approved', 'Substantial Completion Pending Approval',
                                                                    'Substantial Completion', 'Final Completion Pending Approval', 
                                                                    'Final Completion', 'Cancelled - Customer', 'Cancelled - SCF') ];
 
        for(Opportunity o : restBuildswiththePartner){
            if (o.Partner_Cap_Reached__c == true && (o.Construction_Approval_Payment__c != 0.00 && o.Substantial_Completion_Payment__c != 0.80 ) && (o.StageName != 'Construction Approved' && o.StageName != 'Substantial Completion Pending Approval' && o.StageName != 'Substantial Completion' && o.StageName != 'Final Completion Pending Approval' && o.StageName != 'Final Completion' && o.StageName != 'Cancelled - Customer' && o.StageName != 'Cancelled - SCF')) {
                  o.Construction_Approval_Payment__c = 0.00;
                  o.Substantial_Completion_Payment__c = 0.80;
                  o.Final_Completion_Payment__c = 0.20;
                  buildsToUpdate.add(o);
            }
        
            if (o.Partner_Cap_Reached__c == false && (o.Construction_Approval_Payment__c != 0.40 && o.Substantial_Completion_Payment__c != 0.40 ) && (o.StageName != 'Construction Approved' && o.StageName != 'Substantial Completion Pending Approval' && o.StageName != 'Substantial Completion' && o.StageName != 'Final Completion Pending Approval' && o.StageName != 'Final Completion' && o.StageName != 'Cancelled - Customer' && o.StageName != 'Cancelled - SCF')) {
                  o.Construction_Approval_Payment__c = 0.40;
                  o.Substantial_Completion_Payment__c = 0.40;
                  o.Final_Completion_Payment__c = 0.20;
                  buildsToUpdate.add(o);
            }
        }
    
        if (!buildsToUpdate.isEmpty()){
            update buildsToUpdate;
        }                                                                                                                    
   }
}

 

 

 

ryanjuptonryanjupton

ManoharSF. Try pulling the SOQL query out of the for loop. Is this happeing with a bulk update?
abinayaabinaya

Plz refer to this link http://wiki.developerforce.com/page/Apex_Code_Best_Practices
CoolSurenCoolSuren

Hi manohar,

 

I think the first for loop is unnecessary one. Try the following may be it will helpful to you,

 

trigger UpdateApprovalPercentForRest on Opportunity (after delete, after undelete, after update) {

 

  List<Opportunity> buildsToUpdate = new List<Opportunity>{};

  List<Opportunity> restBuildswiththePartner =[SELECT Partner_Cap_Reached__c , Build_Partner__c,Construction_Approval_Payment__c,
                                 StageName,Substantial_Completion_Payment__c
                                 FROM Opportunity WHERE Build_Partner_ID__c = :parent.Build_Partner_ID__c AND
                                 StageName NOT IN ('Construction Approved', 'Substantial Completion Pending Approval',
                                 'Substantial Completion', 'Final Completion Pending Approval',
                                 'Final Completion', 'Cancelled - Customer', 'Cancelled - SCF') ];

for(Opportunity o : restBuildswiththePartner){
        if (o.Partner_Cap_Reached__c == true && (o.Construction_Approval_Payment__c != 0.00 && o.Substantial_Completion_Payment__c != 0.80 )                        && (o.StageName != 'Construction Approved' && o.StageName != 'Substantial Completion Pending Approval' && o.StageName != 'Substantial Completion' && o.StageName != 'Final Completion Pending Approval' && o.StageName != 'Final Completion' && o.StageName != 'Cancelled - Customer' && o.StageName != 'Cancelled - SCF')) {
o.Construction_Approval_Payment__c = 0.00;
o.Substantial_Completion_Payment__c = 0.80;
o.Final_Completion_Payment__c = 0.20;
buildsToUpdate.add(o);
}

if (o.Partner_Cap_Reached__c == false && (o.Construction_Approval_Payment__c != 0.40 && o.Substantial_Completion_Payment__c != 0.40 ) &&                 (o.StageName != 'Construction Approved' && o.StageName != 'Substantial Completion Pending Approval' && o.StageName != 'Substantial                        Completion' && o.StageName != 'Final Completion Pending Approval' && o.StageName != 'Final Completion' && o.StageName != 'Cancelled - Customer' && o.StageName != 'Cancelled - SCF')) {
o.Construction_Approval_Payment__c = 0.40;
o.Substantial_Completion_Payment__c = 0.40;
o.Final_Completion_Payment__c = 0.20;
buildsToUpdate.add(o);
}
}
if (!buildsToUpdate.isEmpty()){
   update buildsToUpdate;
}
}
ManoharSFManoharSF

Thanks everyone for the replies

 

I tried as below but still having same issue at the indicated line below (-->>) .  is there any other way I can mdify this code to avoid 'Too many SOQL queries: 101'?

 

 

 

trigger UpdateApprovalPercentForRest on Opportunity (after delete, after undelete, after update) {

    list<Opportunity> buildsToUpdate = new list<Opportunity>{};
    
   -->> list<Opportunity> restBuildswiththePartner =[SELECT Partner_Cap_Reached__c , Build_Partner__c,Construction_Approval_Payment__c,
                                                                StageName,Substantial_Completion_Payment__c 
                                                                FROM Opportunity WHERE  
                                                                StageName IN ('Pre-Qualified','Initial Lease Docs Pending Approval',
                                                                    'Initial Lease Docs Approved','Design Pending Approval','Design Approved') and 
                                                                    Build_Partner_ID__c = :Trigger.newMap.keySet()];

    

    for(Opportunity o : restBuildswiththePartner){
            if (o.Partner_Cap_Reached__c == true && o.Construction_Approval_Payment__c != 0.00 && o.Substantial_Completion_Payment__c != 0.80  ) {
                  o.Construction_Approval_Payment__c = 0.00;
                  o.Substantial_Completion_Payment__c = 0.80;
                  o.Final_Completion_Payment__c = 0.20;
                  buildsToUpdate.add(o);
            }
        
            if (o.Partner_Cap_Reached__c == false && o.Construction_Approval_Payment__c != 0.40 && o.Substantial_Completion_Payment__c != 0.40 ) {
                  o.Construction_Approval_Payment__c = 0.40;
                  o.Substantial_Completion_Payment__c = 0.40;
                  o.Final_Completion_Payment__c = 0.20;
                  buildsToUpdate.add(o);
            }
        }
    
        if (!buildsToUpdate.isEmpty()){
            update buildsToUpdate;
        }                                                                                                                    
   
   
}