3

I have expanded my Apex class to cover more 'trigger' scenarios in our Sandbox and cannot deploy to Production because I only have 74% code coverage. The code automatically creates 'Project Activities' based on answers to four questions defined on the parent 'Engineering Project' header object. I also expanded the trigger test criteria to cover all appropriate scenarios but get the following error when I try to deploy.

EngineeringProjectTriggerTest.myUnitTest() Class 118 Failure Message: "System.LimitException: Too many SOQL queries: 101", Failure Stack Trace: "Class.ProjectActivityHelper.buildActivitiesDependantOnProject: line 118, column 1 Class.ProjectActivityHelper.buildActivityList: line 88, column 1 Class.EngineeringProjectTriggerTest.myUnitTest: line 166, column 1"

Code below for your review..

@isTest(SeeAllData=true)

private class EngineeringProjectTriggerTest {


    static testMethod void myUnitTest() { 

        Account account = new Account(Name = 'TEST', Type = 'Distributor', Industry = 'General Industry');
        insert account;

        End_User_Site__c endUserSite  = new End_User_Site__c(
                SF_End_User_Operator_Account__c = account.id, 
                Market__c = 'Power',
                Country__c = 'BELGIUM');

        insert endUserSite;        

        Opportunity opportunity = new Opportunity(
                Enquiry_Received_Date__c = System.today(),
                Name = 'TEST OPP',
                Operating_Company__c = 'NETE',
                CurrencyIsoCode = 'GBP',
                AccountId = account.id,
                Internal_External_Customer__c = 'Third Party End User',
                New_or_Existing_Build__c = 'New Installation',
                LeadSource = 'Advertisement',
                Country_of_End_Destination__c = 'BELGIUM',
                End_User_Site_Plant__c = endUserSite.Id,
                Job_Type__c = 'Service',
                Product_Specialist__c = null,
                Agreed_Delivery_Date__c = System.today(),
                Expected_Ship_Date__c = System.today(),
                Go_Ahead__c = '10',
                StageName = 'Opportunity',
                CloseDate = System.today(),
                Get_Probability__c = '80',
                Lead_Time_Weeks__c = 23,
                Payment_Terms__c = '30 Days Nett',
                Quote_Prepared_Date__c = System.today(),
                Order_Received_Date__c = System.today());
    insert opportunity;




        Engineering_Projects__c engProject  = new Engineering_Projects__c(
                Opportunity__c = opportunity.id,
                Product_Line__c = 'Food Transfer',
                Model__c = 'WFT4',
                CurrencyIsoCode = 'GBP',
                Order_Received_Date__c = System.today(),
                Customer_Requested_Delivery_Date__c = System.today()+40,
                Lead_Time_Weeks__c = 40,
                Expected_Ship_Date__c = System.today()+40,
                Submittal_Approval_Required__c = 'YES',
                Final_Doc_Required__c = 'YES',
                Preliminary_O_M_Approval_Required__c = 'YES',
                Engineering_Required__c = 'YES');

          insert engProject;

        ProjectActivityHelper projActHelper = new ProjectActivityHelper();
        projActHelper.buildActivityList(engProject);

         List <Project_Activities__c> projectActivityList = [Select id, Due_Date__c, Project_Name__c 
                                                            from Project_Activities__c 
                                                            where Project_Name__c = :engProject.id];

        projectActivityList[0].Due_Date__c = ProjectActivityHelper.addBusinessDays(projectActivityList[0].Due_Date__c, 1);
        update projectActivityList[0];



        engProject  = new Engineering_Projects__c(
                Opportunity__c = opportunity.id,
                Product_Line__c = 'Food Transfer',
                Model__c = 'WFT4',
                CurrencyIsoCode = 'GBP',
                Order_Received_Date__c = System.today(),
                Customer_Requested_Delivery_Date__c = System.today()+40,
                Lead_Time_Weeks__c = 40,
                Expected_Ship_Date__c = System.today()+40,
                Submittal_Approval_Required__c = 'NO',
                Final_Doc_Required__c = 'YES',
                Preliminary_O_M_Approval_Required__c = 'YES',
                Engineering_Required__c = 'YES');

          insert engProject;

        projActHelper.buildActivityList(engProject);

         projectActivityList = [Select id, Due_Date__c, Project_Name__c 
                                from Project_Activities__c 
                                where Project_Name__c = :engProject.id];

        projectActivityList[0].Due_Date__c = ProjectActivityHelper.addBusinessDays(projectActivityList[0].Due_Date__c, 1);
        update projectActivityList[0];


        engProject  = new Engineering_Projects__c(
                Opportunity__c = opportunity.id,
                Product_Line__c = 'Food Transfer',
                Model__c = 'WFT4',
                CurrencyIsoCode = 'GBP',
                Order_Received_Date__c = System.today(),
                Customer_Requested_Delivery_Date__c = System.today()+40,
                Lead_Time_Weeks__c = 40,
                Expected_Ship_Date__c = System.today()+40,
                Submittal_Approval_Required__c = 'NO',
                Final_Doc_Required__c = 'NO',
                Preliminary_O_M_Approval_Required__c = 'YES',
                Engineering_Required__c = 'YES');

          insert engProject;

        projActHelper.buildActivityList(engProject);

         projectActivityList = [Select id, Due_Date__c, Project_Name__c 
                                from Project_Activities__c 
                                where Project_Name__c = :engProject.id];

        projectActivityList[0].Due_Date__c = ProjectActivityHelper.addBusinessDays(projectActivityList[0].Due_Date__c, 1);
        update projectActivityList[0];



        engProject  = new Engineering_Projects__c(
                Opportunity__c = opportunity.id,
                Product_Line__c = 'Food Transfer',
                Model__c = 'WFT4',
                CurrencyIsoCode = 'GBP',
                Order_Received_Date__c = System.today(),
                Customer_Requested_Delivery_Date__c = System.today()+40,
                Lead_Time_Weeks__c = 40,
                Expected_Ship_Date__c = System.today()+40,
                Submittal_Approval_Required__c = 'NO',
                Final_Doc_Required__c = 'NO',
                Preliminary_O_M_Approval_Required__c = 'NO',
                Engineering_Required__c = 'YES');

          insert engProject;

        projActHelper.buildActivityList(engProject);

         projectActivityList = [Select id, Due_Date__c, Project_Name__c 
                                from Project_Activities__c 
                                where Project_Name__c = :engProject.id];

        projectActivityList[0].Due_Date__c = ProjectActivityHelper.addBusinessDays(projectActivityList[0].Due_Date__c, 1);
        update projectActivityList[0];


        engProject  = new Engineering_Projects__c(
                Opportunity__c = opportunity.id,
                Product_Line__c = 'Food Transfer',
                Model__c = 'WFT4',
                CurrencyIsoCode = 'GBP',
                Order_Received_Date__c = System.today(),
                Customer_Requested_Delivery_Date__c = System.today()+40,
                Lead_Time_Weeks__c = 40,
                Expected_Ship_Date__c = System.today()+40,
                Submittal_Approval_Required__c = 'NO',
                Final_Doc_Required__c = 'NO',
                Preliminary_O_M_Approval_Required__c = 'NO',
                Engineering_Required__c = 'NO');

          insert engProject;

        projActHelper.buildActivityList(engProject);

         projectActivityList = [Select id, Due_Date__c, Project_Name__c 
                                from Project_Activities__c 
                                where Project_Name__c = :engProject.id];

        projectActivityList[0].Due_Date__c = ProjectActivityHelper.addBusinessDays(projectActivityList[0].Due_Date__c, 1);
        update projectActivityList[0]; 


        engProject  = new Engineering_Projects__c(
                Opportunity__c = opportunity.id,
                Product_Line__c = 'Food Transfer',
                Model__c = 'WFT4',
                CurrencyIsoCode = 'GBP',
                Order_Received_Date__c = System.today(),
                Customer_Requested_Delivery_Date__c = System.today()+40,
                Lead_Time_Weeks__c = 40,
                Expected_Ship_Date__c = System.today()+40,
                Submittal_Approval_Required__c = 'YES',
                Final_Doc_Required__c = 'YES',
                Preliminary_O_M_Approval_Required__c = 'YES',
                Engineering_Required__c = 'NO');

          insert engProject;

        projActHelper.buildActivityList(engProject);

         projectActivityList = [Select id, Due_Date__c, Project_Name__c 
                                from Project_Activities__c 
                                where Project_Name__c = :engProject.id];

        projectActivityList[0].Due_Date__c = ProjectActivityHelper.addBusinessDays(projectActivityList[0].Due_Date__c, 1);
        update projectActivityList[0];      


        engProject  = new Engineering_Projects__c(
                Opportunity__c = opportunity.id,
                Product_Line__c = 'Food Transfer',
                Model__c = 'WFT4',
                CurrencyIsoCode = 'GBP',
                Order_Received_Date__c = System.today(),
                Customer_Requested_Delivery_Date__c = System.today()+40,
                Lead_Time_Weeks__c = 40,
                Expected_Ship_Date__c = System.today()+40,
                Submittal_Approval_Required__c = 'YES',
                Final_Doc_Required__c = 'YES',
                Preliminary_O_M_Approval_Required__c = 'NO',
                Engineering_Required__c = 'NO');

          insert engProject;

        projActHelper.buildActivityList(engProject);

         projectActivityList = [Select id, Due_Date__c, Project_Name__c 
                                from Project_Activities__c 
                                where Project_Name__c = :engProject.id];

        projectActivityList[0].Due_Date__c = ProjectActivityHelper.addBusinessDays(projectActivityList[0].Due_Date__c, 1);
        update projectActivityList[0];


        engProject  = new Engineering_Projects__c(
                Opportunity__c = opportunity.id,
                Product_Line__c = 'Food Transfer',
                Model__c = 'WFT4',
                CurrencyIsoCode = 'GBP',
                Order_Received_Date__c = System.today(),
                Customer_Requested_Delivery_Date__c = System.today()+40,
                Lead_Time_Weeks__c = 40,
                Expected_Ship_Date__c = System.today()+40,
                Submittal_Approval_Required__c = 'YES',
                Final_Doc_Required__c = 'NO',
                Preliminary_O_M_Approval_Required__c = 'NO',
                Engineering_Required__c = 'NO');

          insert engProject;

        projActHelper.buildActivityList(engProject);

         projectActivityList = [Select id, Due_Date__c, Project_Name__c 
                                from Project_Activities__c 
                                where Project_Name__c = :engProject.id];

        projectActivityList[0].Due_Date__c = ProjectActivityHelper.addBusinessDays(projectActivityList[0].Due_Date__c, 1);
        update projectActivityList[0];


        engProject  = new Engineering_Projects__c(
                Opportunity__c = opportunity.id,
                Product_Line__c = 'Food Transfer',
                Model__c = 'WFT4',
                CurrencyIsoCode = 'GBP',
                Order_Received_Date__c = System.today(),
                Customer_Requested_Delivery_Date__c = System.today()+40,
                Lead_Time_Weeks__c = 40,
                Expected_Ship_Date__c = System.today()+40,
                Submittal_Approval_Required__c = 'NO',
                Final_Doc_Required__c = 'YES',
                Preliminary_O_M_Approval_Required__c = 'NO',
                Engineering_Required__c = 'NO');

          insert engProject;

        projActHelper.buildActivityList(engProject);

         projectActivityList = [Select id, Due_Date__c, Project_Name__c 
                                from Project_Activities__c 
                                where Project_Name__c = :engProject.id];

        projectActivityList[0].Due_Date__c = ProjectActivityHelper.addBusinessDays(projectActivityList[0].Due_Date__c, 1);
        update projectActivityList[0];


    }

closed as too broad by Jesse Altman, Doug B, Rao, Daniel Blackhall, pchittum Aug 20 '13 at 22:37

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Can you paste you main class, this is a test class and only when you paste the main class can someone help with finding where the too many soql is happening – Rao Aug 19 '13 at 16:29
  • 1
    Generally, that indicates you have a SOQL query inside a loop somewhere. Also, please read wiki.developerforce.com/page/How_to_Write_Good_Unit_Tests; your test is very large, hard to maintain, and doesn't appear to actually test anything (no asserts). – Mike Chale Aug 19 '13 at 16:30
  • Look at your updated code, you have multiple SOQL queries inside of a for loop as @MikeChale and myself suggested. At this point I would highly suggest going back and taking some time to learn Apex/SOQL a bit more. This code is gigantic and I'm not sure anyone here can help debug the entire thing for you. – Jesse Altman Aug 20 '13 at 15:50
5

The issue here is you are violating Salesforce's governor limits. Specifically, you can only do 100 SOQL queries inside a single transaction.

As @MikeChale pointed out, this is almost definitely related to having a SOQL query inside of a loop. Also, as Mike noted, you will have trouble maintaining this code in the future the way it is currently written. I highly suggest reading about How To Write Good Unit Tests. I would go as far as suggesting to read Robert Martin's Clean Code: A Handbook of Agile Software Craftsmanship. This book will explain all of the benefits of following proper conventions. These benefits will follow you through your career across any programming language you encounter.

Now, back to your issue, I can't distinctively tell you where the error was because this is only part of the code. The way to read your exception is from the bottom up. So, you code is failing when:

  • EngineeringProjectTriggerTest Class calls method myUnitTest. On line 166, this method calls:
  • ProjectActivityHelper Class' buildActivityList method. On line 88, this method calls:
  • ProjectActivityHelper Class' buildActivitiesDependantOnProject method. This class is erroring out on line 118 because of:
  • System.LimitException: Too many SOQL queries: 101

So, as you can see, the error begins from your test class, but where Salesforce is actually failing is part of the ProjectActivityHelper class. Salesforce provides good documentation on how exceptions work, and I would suggest reading it.

  • Jesse, as requested please find attached the main Apex class that is causing the error... – user3588 Aug 20 '13 at 14:20
1

This is where the SOQL error is coming from

you cannot have a select statement inside a for loop : Instead create maps/ lists and use a IN: clause to pick the depProjCS.Dependancy_Activity__c and depProjCS.Dependant_Level_2_Activity__c and form the select statement.

   for(Dependent_Project_Activities__c depProjCS: depProjectsCS.values()) {
            System.debug('*************** Current Setting: ' + depProjCS.Name);
            // now find the activity which has another activity depending on it
            List<Project_Activities__c> foundActivities = [Select id, Due_Date__c
                                                            from Project_Activities__c
                                                            where Project_Activities__c.Project_Name__c = :engProject.id and 
                                                                    Project_Activities__c.Activity_Level_2__c = :depProjCS.Dependancy_Activity__c limit 1];

            if (foundActivities.size() > 0) {
                // before creating the new activity, check we haven't inserted this activity already    
                List<Project_Activities__c> projActivities = [Select id
                                                                from Project_Activities__c
                                                                where Project_Activities__c.Project_Name__c = :engProject.id and 
                                                                        Project_Activities__c.Activity_Level_2__c = :depProjCS.Dependant_Level_2_Activity__c limit 1];

                if(projActivities.size() < 1){ // we don't already have the activity so we can create a new one
                    // Create the new activity
                    Date dueDate = addBusinessDays(foundActivities[0].Due_Date__c, Integer.valueOf(depProjCS.Lag__c));

                    Project_Activities__c newProjActivity = buildProjectActivity(engProject.ID, dueDate,
                                                                    depProjCS.Dependant_Level_1_Activity__c, 
                                                                    depProjCS.Dependant_Level_2_Activity__c, 
                                                                    engProject.OwnerID,
                                                                    Integer.valueOf(depProjCS.lag__c),
                                                                    foundActivities[0].id);

                    if(depProjCS.ACTIVITY_OWNER__C != 'Owner'){
                        String owner = getUserID(depProjCS.ACTIVITY_OWNER__C);
                        newProjActivity.Assigned_To__c = owner;
                    }

                    newProjActivity.Dependency_Lag__c = depProjCS.lag__c;

                    insertActivityList.add(newProjActivity);

                    // now set the lag for the dependancy activity
                    foundActivities[0].Dependency_Lag__c = depProjCS.Lag__c;
                    updateActivityMap.put(foundActivities[0].id, foundActivities[0]);
                }
            }
        }

Not the answer you're looking for? Browse other questions tagged or ask your own question.