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
Jim MontgomeryJim Montgomery 

error too many soql queries

I am getting "Too mant SOQL Queries" error in this chunk of code. it is in an Apex class, and I know they are running a SOQL query in a for loop (no no), but not sure how to correctly create and reference the map that they should be using.


if(opptyWonIds.size()<0){
            for(Opportunity wonOpp : [SELECT Id, Opportunity_Closed_Lost__c, Opportunity_Partial_Won__c, Opportunity_Closed_Won__c 
                                           FROM Opportunity WHERE Id IN: opptyWonIds]){
                if(wonOpp.Opportunity_Closed_Won__c != true){
                    wonOpp.Opportunity_Closed_Won__c = true;
                    if(wonOpp.Opportunity_Partial_Won__c == true || wonOpp.Opportunity_Closed_Lost__c == true){
                        wonOpp.Opportunity_Partial_Won__c = false;
                        wonOpp.Opportunity_Closed_Lost__c = false;
                   }
                    System.debug('*********************wonOpp: '+wonOpp);
                   opportunitiesToUpdate.add(wonOpp);
Paul S.Paul S.
Jim - would it be possible to post the rest of the class?  What you've posted wouldn't itself be causing the error.
Jim MontgomeryJim Montgomery
public with sharing class APTS_ProposalLineItem_Helper {

    public APTS_ProposalLineItem_Helper() {
        
    }

    public void triggerOpportunityWorkflows(Apttus_Proposal__Proposal_Line_Item__c[] objects){
        System.debug('****************objects entering method: '+objects);
        //Proposal IDs
        Set<Id> proposalIds = new Set<Id>();
        //Opportunity IDs
        Set<Id> opptyWonIds = new Set<Id>();
        Set<Id> opptyPartialWonIds = new Set<Id>();
        Set<Id> opptyLostIds = new Set<Id>();

        for(Apttus_Proposal__Proposal_Line_Item__c pli : objects){
            //Add to Proposal ID set
            proposalIds.add(pli.Apttus_Proposal__Proposal__c);
        }

        System.debug('*******************proposalIds: '+proposalIds);

        //List of Proposals
        List<Apttus_Proposal__Proposal__c> listProposals = [SELECT Id, Apttus_Proposal__Opportunity__c 
                                                            FROM Apttus_Proposal__Proposal__c 
                                                            WHERE Id IN : proposalIds];
        System.debug('******************listProposals: '+listProposals);

        //List of Proposal Line Items
        List<Apttus_Proposal__Proposal_Line_Item__c> listPLineItems = [SELECT Id, Apttus_QPConfig__LineStatus__c, Apttus_Proposal__Proposal__c
                                                                        FROM Apttus_Proposal__Proposal_Line_Item__c
                                                                        WHERE Apttus_Proposal__Proposal__c IN : proposalIds];

        System.debug('*********************listPLineItems: '+listPLineItems);
        for(Apttus_Proposal__Proposal__c proposal : listProposals){

            //Count of Line Statuses per Proposal
            Integer counterNew = 0;
            Integer counterCancelled = 0;
            Integer counterRenewed = 0;
            //Count of Line Items per Proposal
            Integer counterLineItems = 0;

            for(Apttus_Proposal__Proposal_Line_Item__c pli : listPLineItems){
                System.debug('************************pli: '+pli);
                if(proposal.Id == pli.Apttus_Proposal__Proposal__c){

                    counterLineItems += 1;

                    if(pli.Apttus_QPConfig__LineStatus__c == 'Cancelled'){
                        counterCancelled += 1;
                    }
                }
            }

            System.debug('****************************counterCancelled: '+counterCancelled);
            System.debug('****************************counterLineItems: '+counterLineItems);

            if(counterCancelled == 0){
                opptyWonIds.add(proposal.Apttus_Proposal__Opportunity__c);
            }

            if(counterCancelled>0 && counterCancelled<counterLineItems){
                opptyPartialWonIds.add(proposal.Apttus_Proposal__Opportunity__c);
            }
            if(counterCancelled>0 && counterCancelled == counterLineItems){
                opptyLostIds.add(proposal.Apttus_Proposal__Opportunity__c);
            }
        }

        System.debug('***************opptyWonIds: '+opptyWonIds);
        System.debug('***************opptyPartialWonIds: '+opptyPartialWonIds);
        System.debug('***************opptyLostIds: '+opptyLostIds);

        List<Opportunity> opportunitiesToUpdate = new List<Opportunity>();

        if(opptyPartialWonIds.size()>0){
            for(Opportunity partialWonOpp : [SELECT Id, Opportunity_Partial_Won__c, Opportunity_Closed_Won__c, Opportunity_Closed_Lost__c FROM
                                                Opportunity WHERE Id IN: opptyPartialWonIds]){
                if(partialWonOpp.Opportunity_Partial_Won__c != true){
                    partialWonOpp.Opportunity_Partial_Won__c = true;
                    if(partialWonOpp.Opportunity_Closed_Lost__c == true || partialWonOpp.Opportunity_Closed_Won__c == true){
                        partialWonOpp.Opportunity_Closed_Lost__c = false;
                        partialWonOpp.Opportunity_Closed_Won__c = false;
                    }
                    System.debug('***********************partialWonOpp: '+partialWonOpp);
                    opportunitiesToUpdate.add(partialWonOpp);
                }
            }
        }

        if(opptyLostIds.size()>0){
            for(Opportunity lostOpp : [SELECT Id, Opportunity_Closed_Lost__c, Opportunity_Partial_Won__c, Opportunity_Closed_Won__c FROM
                                            Opportunity WHERE Id IN: opptyLostIds]){
                if(lostOpp.Opportunity_Closed_Lost__c != true){
                    lostOpp.Opportunity_Closed_Lost__c = true;
                    if(lostOpp.Opportunity_Partial_Won__c == true || lostOpp.Opportunity_Closed_Won__c == true){
                        lostOpp.Opportunity_Partial_Won__c = false;
                        lostOpp.Opportunity_Closed_Won__c = false;
                    }
                    System.debug('***********************lostOpp: '+lostOpp);
                    opportunitiesToUpdate.add(lostOpp);
                }
            }
        }

        if(opptyWonIds.size()<0){
            for(Opportunity wonOpp : [SELECT Id, Opportunity_Closed_Lost__c, Opportunity_Partial_Won__c, Opportunity_Closed_Won__c 
                                           FROM Opportunity WHERE Id IN: opptyWonIds]){
                if(wonOpp.Opportunity_Closed_Won__c != true){
                    wonOpp.Opportunity_Closed_Won__c = true;
                    if(wonOpp.Opportunity_Partial_Won__c == true || wonOpp.Opportunity_Closed_Lost__c == true){
                        wonOpp.Opportunity_Partial_Won__c = false;
                        wonOpp.Opportunity_Closed_Lost__c = false;
                   }
                    System.debug('*********************wonOpp: '+wonOpp);
                   opportunitiesToUpdate.add(wonOpp);
                }
            }
        }

        System.debug('**************************************opportunitiesToUpdate: '+opportunitiesToUpdate);
        if(opportunitiesToUpdate.size()>0){
            try{
                update opportunitiesToUpdate;
            } catch(DmlException e){
                System.debug('The following error has occurred: '+e.getMessage());
            }
        }
    }
    
}
Paul S.Paul S.
Jim - those queries aren't actually in the for loop from the perspective of the best practice that says no SOQL in a for loop.  The queries you have are just setting the list over which the for loop will iterate.  I'm inclined to think that the issue is actually being caused by whatever (a trigger?) is calling this class.
Jim MontgomeryJim Montgomery
This is the error I get attempting to deploy a simple trigger and test class. TestSoftwareAnnualDates insertProposalLineItem System.LimitException: Too many SOQL queries: 101 Stack Trace: Class.APTS_ProposalLineItem_Helper.triggerOpportunityWorkflows: line 115, column 1 Class.APTS_ProposalLineItem_Handler.OnAfterUpdate: line 38, column 1 Trigger.APTS_ProposalLineItem: line 24, column 1 Jim Montgomery Manager, Sales Intelligence Operations North America Tax & Accounting 20101 Hamilton Ave. Torrance, CA 90502 877-346-7148 jim.montgomery@wolterskluwer.com [wklogo] Confidentiality Notice: This email and any attachments may contain confidential or privileged information that is intended for the addressee only. If you are not an intended recipient of the original sender (or responsible for delivering the message to such person), you are hereby notified that any review, disclosure, copying, distribution or the taking of any action in reliance of the contents of and attachments to this email is strictly prohibited. If you have received this email in error, please immediately notify the sender at the address shown herein and permanently delete any copies of this email (digital or paper) in your possession. Wolters Kluwer shall not be liable for the incorrect or incomplete transmission of this email or any attachments, nor for unauthorized use by its employees.
Paul S.Paul S.

Thanks.  Your trigger, APTS_ProposalLineItem, is calling the APTS_ProposalLineItem_Handler class, which is then calling the helper class you originally posted...all of this from a test class. 

The issue could be anywhere, but given the error has identified the OnAfterUpdate method of your handler class, I'd start by seeing if you're not in some recursive situation.  I.e., the insertion of the proposal line item causes an update to the associated opportunity, which in turn causes some update to the same proposal line item, and around and around you go until you hit some DML limit. 

If you want to paste all of that code (using the "< >" icon above), we can look at it further.