0

I am using Trigger Handler for my Trigger. But still getting Too many SOQL on updateNoMaterialTool(); & Apex Class

public class ProjectTriggerHandler {
    public Map<Id, Projects__c> oldProjectMap {get;set;}
    public Map<Id, Projects__c> newProjectMap {get;set;}
    public Map<String, String> rejectedFieldToTargetPicklistMap {get;set;}
    public ProjectTriggerHandler(Map<Id, Projects__c> oldProjectMapParam, Map<Id, Projects__c> newProjectMapParam) {
        this.oldProjectMap = oldProjectMapParam;
        this.newProjectMap = newProjectMapParam;
    }
    public void operationBeforeUpdate() {
        updateRejectedPickListValues();
         //Exisitng trigger code added here
        updateNoMaterialTool();
    }
    public void operationBeforeInsert() {
        //Exisitng trigger code added here
        updateNoMaterialTool();
    }

    public void updateNoMaterialTool() {

        List<Project_Items__c> projectItemList=new  List<Project_Items__c>();
        List<Tools_Master__c> toolList=new  List<Tools_Master__c>();
        
        List<Projects__c> UpdateProject=new  List<Projects__c>();
        for( Projects__c pro: newProjectMap.values() ){
            
            projectItemList=[select id,Name,Execution__r.Name,Total_GRPO_Quantity__c,Execution__r.Project__c,Execution__r.Project__r.LL_Location__c,Item_Description__c,SAP_Item_Code__c,Item_Group_Name__c,
                                                    Approved_Make__c,UOM__c,Total_Initial_Stock__c,Total_Billed_Quantity__c,Total_Remaining_Stock__c from Project_Items__c 
                                                    where(Execution__r.Project__c =:pro.id )
                                                    AND Total_Remaining_Stock__c > 0];
            
            system.debug('projectItemList'+projectItemList);
            
            toolList=[select id,Tool_ID__c,Name,Tool_Description__c,Make__c,Region__c,Serial_Number__c,Procured_Date__c,
                                                    Tool_Status__c,Warehouse_Name__c,Calibration_Certificate_URL__c,Project_Names__c from Tools_Master__c 
                                                    where Project_Names__c =:pro.Opportunity_ID__c];
            
            system.debug('toolList'+ toolList);
            
                if( projectItemList.isEmpty() ){
                if(toolList.isEmpty()){
                        pro.No_Material_to_Return_Tools__c=true;
                    }
                }
            if( projectItemList.isEmpty() ){
                if(!toolList.isEmpty()){
                        pro.No_Material_to_Return_Tools__c=false;
                    }   
            }
            
            if(! projectItemList.isEmpty() ){
                if(toolList.isEmpty()){
                        pro.No_Material_to_Return_Tools__c=false;
                    }   
            }
            
        }
    }
    
    private void checkAndUpdateRevisionEHS(Projects__c oldRec, Projects__c newRec) {
        System.debug('@@@@@@ 1 @@@@@@ '+oldRec.Client_Approval_Status_EHS__c );
        System.debug('@@@@@@ 2 @@@@@@ '+newRec.Client_Approval_Status_EHS__c );
        System.debug('@@@@@@ 3 @@@@@@ '+oldRec.Revision_EHS__c );
        System.debug('@@@@@@ 4 @@@@@@ '+newRec.Revision_EHS__c );
        if(oldRec.Client_Approval_Status_EHS__c != newRec.Client_Approval_Status_EHS__c && 
                    newRec.Client_Approval_Status_EHS__c == 'Rejected') {
            if(oldRec.Revision_EHS__c == 'R10')
            {
                System.debug('@@@@@@ 5 @@@@@@ ');
                return;
            }
            String oldRevisionVal =oldRec.Revision_EHS__c;
            System.debug('@@@@@@ 6 @@@@@@ '+oldRevisionVal);
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            System.debug('@@@@@@ 7 @@@@@@ '+trailingIntegerString);
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            System.debug('@@@@@@ 8 @@@@@@ '+incrementedTrail);
            newRec.Revision_EHS__c = 'R'+incrementedTrail;    
            System.debug('@@@@@@ 9 @@@@@@ '+newRec.Revision_EHS__c);       
        }
    }
    
    private void checkAndUpdateRevisionQuality(Projects__c oldRec, Projects__c newRec) {
        if(oldRec.Clients_Approval_Status_quality__c != newRec.Clients_Approval_Status_quality__c && 
                    newRec.Clients_Approval_Status_quality__c == 'Rejected') {
            if(oldRec.Revision__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.Revision__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.Revision__c = 'R'+incrementedTrail;           
        }
        
    }
    private void checkAndUpdateRevisionTCC(Projects__c oldRec, Projects__c newRec) {
         if(oldRec.Clients_Approval_StatusT_C__c != newRec.Clients_Approval_StatusT_C__c && 
                    newRec.Clients_Approval_StatusT_C__c == 'Rejected') {
            if(oldRec.Revision_T_C__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.Revision_T_C__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.Revision_T_C__c = 'R'+incrementedTrail;           
        }

    }
    private void checkAndUpdateRevisionSLogis(Projects__c oldRec, Projects__c newRec) {
         if(oldRec.X9_Clients_Approval_Status_S_Logis__c!= newRec.X9_Clients_Approval_Status_S_Logis__c && 
                    newRec.X9_Clients_Approval_Status_S_Logis__c == 'Rejected') {
            if(oldRec.X2_Revision_S_Logis__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.X2_Revision_S_Logis__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.X2_Revision_S_Logis__c = 'R'+incrementedTrail;           
        }
        
    }
    
    private void checkAndUpdateRevisionProjectSchedule(Projects__c oldRec, Projects__c newRec) {
        if(oldRec.Client_Approval_Status_Project_Schedule__c != newRec.Client_Approval_Status_Project_Schedule__c && 
                    newRec.Client_Approval_Status_Project_Schedule__c == 'Rejected') {
            if(oldRec.Revision_Project_Schedule__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.Revision_Project_Schedule__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.Revision_Project_Schedule__c = 'R'+incrementedTrail;           
        }
        
    }
    private void checkAndUpdateRevisionToolsAndTackles(Projects__c oldRec, Projects__c newRec) {
        if(oldRec.Project_Lead_s_Approval_Status_TTP__c != newRec.Project_Lead_s_Approval_Status_TTP__c && 
                    newRec.Project_Lead_s_Approval_Status_TTP__c == 'Rejected') {
            if(oldRec.Revision_Tools_and_Tackles_Plan__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.Revision_Tools_and_Tackles_Plan__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.Revision_Tools_and_Tackles_Plan__c = 'R'+incrementedTrail;           
        }
        
        
    }
    private void updateRejectedPickListValues() {
        for(Projects__c oldRec : oldProjectMap.values()) {
            Projects__c newRec = newProjectMap.get(oldRec.Id);
            checkAndUpdateRevisionEHS(oldRec, newRec);
            checkAndUpdateRevisionQuality(oldRec, newRec);
            checkAndUpdateRevisionTCC(oldRec, newRec);
            checkAndUpdateRevisionSLogis(oldRec, newRec);
            checkAndUpdateRevisionProjectSchedule(oldRec, newRec);
            checkAndUpdateRevisionToolsAndTackles(oldRec, newRec);
        }
    }
}

NEW CODE

public class ProjectTriggerHandler {
    public Map<Id, Projects__c> oldProjectMap {get;set;}
    public Map<Id, Projects__c> newProjectMap {get;set;}
    public Map<String, String> rejectedFieldToTargetPicklistMap {get;set;}
    
    public ProjectTriggerHandler(Map<Id, Projects__c> oldProjectMapParam, Map<Id, Projects__c> newProjectMapParam) {
        this.oldProjectMap = oldProjectMapParam;
        this.newProjectMap = newProjectMapParam;
    }
    public void operationBeforeUpdate() {
        updateRejectedPickListValues();
         //Exisitng trigger code added here
        //updateNoMaterialTool();
    }
    public void operationBeforeInsert() {
        //Exisitng trigger code added here
        //updateNoMaterialTool();
    }

    public void updateNoMaterialTool() {
    
    List<Projects__c> UpdateProject=new  List<Projects__c>();
    
    Set<Id> projectsIds = newProjectMap.keySet();
    List<Project_Items__c> projectItemList=[select id,Name,Execution__r.Name,Total_GRPO_Quantity__c,Execution__r.Project__c,Execution__r.Project__r.LL_Location__c,Item_Description__c,SAP_Item_Code__c,Item_Group_Name__c,
                                                Approved_Make__c,UOM__c,Total_Initial_Stock__c,Total_Billed_Quantity__c,Total_Remaining_Stock__c from Project_Items__c 
                                                where(Execution__r.Project__c IN :projectsIds )
                                                AND Total_Remaining_Stock__c > 0];
                                                
    system.debug('projectItemList'+projectItemList);
    
    Set<String> opportunitiesIds = collectSobjectsFieldValues(newProjectMap.values(), 'Opportunity_ID__c');
    List<Tools_Master__c> toolList=[select id,Tool_ID__c,Name,Tool_Description__c,Make__c,Region__c,Serial_Number__c,Procured_Date__c,
                                                Tool_Status__c,Warehouse_Name__c,Calibration_Certificate_URL__c,Project_Names__c from Tools_Master__c 
                                                where Project_Names__c IN :opportunitiesIds];
                                                
    system.debug('toolList'+ toolList);
                                                
    for( Projects__c pro: newProjectMap.values() ){
            if( projectItemList.isEmpty() ){
            if(toolList.isEmpty()){
                    pro.No_Material_to_Return_Tools__c=true;
                }
            }
        if( projectItemList.isEmpty() ){
            if(!toolList.isEmpty()){
                    pro.No_Material_to_Return_Tools__c=false;
                }   
        }
        
        if(! projectItemList.isEmpty() ){
            if(toolList.isEmpty()){
                    pro.No_Material_to_Return_Tools__c=false;
                }   
        }
        
    }
}

private Set<String> collectSobjectsFieldValues(List<SObject> sobjectList, String fieldName) {
    Set<String> result = new Set<String>();
    for(SObject record: sobjectList) {
        result.add((String)record.get(fieldName));
    }
    
    return result;
}
 
    private void checkAndUpdateRevisionEHS(Projects__c oldRec, Projects__c newRec) {
        System.debug('@@@@@@ 1 @@@@@@ '+oldRec.Client_Approval_Status_EHS__c );
        System.debug('@@@@@@ 2 @@@@@@ '+newRec.Client_Approval_Status_EHS__c );
        System.debug('@@@@@@ 3 @@@@@@ '+oldRec.Revision_EHS__c );
        System.debug('@@@@@@ 4 @@@@@@ '+newRec.Revision_EHS__c );
        if(oldRec.Client_Approval_Status_EHS__c != newRec.Client_Approval_Status_EHS__c && 
                    newRec.Client_Approval_Status_EHS__c == 'Rejected') {
            if(oldRec.Revision_EHS__c == 'R10')
            {
                System.debug('@@@@@@ 5 @@@@@@ ');
                return;
            }
            String oldRevisionVal =oldRec.Revision_EHS__c;
            System.debug('@@@@@@ 6 @@@@@@ '+oldRevisionVal);
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            System.debug('@@@@@@ 7 @@@@@@ '+trailingIntegerString);
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            System.debug('@@@@@@ 8 @@@@@@ '+incrementedTrail);
            newRec.Revision_EHS__c = 'R'+incrementedTrail;    
            System.debug('@@@@@@ 9 @@@@@@ '+newRec.Revision_EHS__c);       
        }
    }
    
    private void checkAndUpdateRevisionQuality(Projects__c oldRec, Projects__c newRec) {
        if(oldRec.Clients_Approval_Status_quality__c != newRec.Clients_Approval_Status_quality__c && 
                    newRec.Clients_Approval_Status_quality__c == 'Rejected') {
            if(oldRec.Revision__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.Revision__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.Revision__c = 'R'+incrementedTrail;           
        }
        
    }
    private void checkAndUpdateRevisionTCC(Projects__c oldRec, Projects__c newRec) {
         if(oldRec.Clients_Approval_StatusT_C__c != newRec.Clients_Approval_StatusT_C__c && 
                    newRec.Clients_Approval_StatusT_C__c == 'Rejected') {
            if(oldRec.Revision_T_C__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.Revision_T_C__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.Revision_T_C__c = 'R'+incrementedTrail;           
        }

    }
    private void checkAndUpdateRevisionSLogis(Projects__c oldRec, Projects__c newRec) {
         if(oldRec.X9_Clients_Approval_Status_S_Logis__c!= newRec.X9_Clients_Approval_Status_S_Logis__c && 
                    newRec.X9_Clients_Approval_Status_S_Logis__c == 'Rejected') {
            if(oldRec.X2_Revision_S_Logis__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.X2_Revision_S_Logis__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.X2_Revision_S_Logis__c = 'R'+incrementedTrail;           
        }
        
    }
    
    private void checkAndUpdateRevisionProjectSchedule(Projects__c oldRec, Projects__c newRec) {
        if(oldRec.Client_Approval_Status_Project_Schedule__c != newRec.Client_Approval_Status_Project_Schedule__c && 
                    newRec.Client_Approval_Status_Project_Schedule__c == 'Rejected') {
            if(oldRec.Revision_Project_Schedule__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.Revision_Project_Schedule__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.Revision_Project_Schedule__c = 'R'+incrementedTrail;           
        }
        
    }
    private void checkAndUpdateRevisionToolsAndTackles(Projects__c oldRec, Projects__c newRec) {
        if(oldRec.Project_Lead_s_Approval_Status_TTP__c != newRec.Project_Lead_s_Approval_Status_TTP__c && 
                    newRec.Project_Lead_s_Approval_Status_TTP__c == 'Rejected') {
            if(oldRec.Revision_Tools_and_Tackles_Plan__c == 'R10')
            {
                return;
            }
            String oldRevisionVal =oldRec.Revision_Tools_and_Tackles_Plan__c;
            String trailingIntegerString =  oldRevisionVal.subString(oldRevisionVal.length()-1, oldRevisionVal.length()); 
            Integer incrementedTrail = Integer.valueOf(trailingIntegerString) + 1;
            newRec.Revision_Tools_and_Tackles_Plan__c = 'R'+incrementedTrail;           
        }
        
        
    }
    private void updateRejectedPickListValues() {
        for(Projects__c oldRec : oldProjectMap.values()) {
            Projects__c newRec = newProjectMap.get(oldRec.Id);
            checkAndUpdateRevisionEHS(oldRec, newRec);
            checkAndUpdateRevisionQuality(oldRec, newRec);
            checkAndUpdateRevisionTCC(oldRec, newRec);
            checkAndUpdateRevisionSLogis(oldRec, newRec);
            checkAndUpdateRevisionProjectSchedule(oldRec, newRec);
            checkAndUpdateRevisionToolsAndTackles(oldRec, newRec);
        }
    }
}

Trigger

trigger ProjectTrigger on Projects__c (before insert, before update) {
    ProjectTriggerHandler handler = new ProjectTriggerHandler(Trigger.oldMap, Trigger.newMap);
    if(Trigger.isInsert && Trigger.isBefore) {
        handler.operationBeforeInsert();
    }
    if(Trigger.isUpdate && Trigger.isBefore) {
        handler.operationBeforeUpdate();
    }
}
1
  • 1
    I can see SOQLs in for loops, get them out of the loops.
    – Girbot
    Commented Jan 12, 2023 at 10:49

2 Answers 2

1

The main reason of the "Too many SOQL" Exception is SOQLs inside for loop:

projectItemList=[select id,Name,Execution__r.Name,Total_GRPO_Quantity__c,Execution__r.Project__c,Execution__r.Project__r.LL_Location__c,Item_Description__c,SAP_Item_Code__c,Item_Group_Name__c,
                                                Approved_Make__c,UOM__c,Total_Initial_Stock__c,Total_Billed_Quantity__c,Total_Remaining_Stock__c from Project_Items__c 
                                                where(Execution__r.Project__c =:pro.id )
                                                AND Total_Remaining_Stock__c > 0];
        
        
toolList=[select id,Tool_ID__c,Name,Tool_Description__c,Make__c,Region__c,Serial_Number__c,Procured_Date__c,
                                                Tool_Status__c,Warehouse_Name__c,Calibration_Certificate_URL__c,Project_Names__c from Tools_Master__c 
                                                where Project_Names__c =:pro.Opportunity_ID__c];

You have to make SOQL outside for loop.

Set<Id> projectsIds = newProjectMap.keySet();

projectItemList=[select id,Name,Execution__r.Name,Total_GRPO_Quantity__c,Execution__r.Project__c,Execution__r.Project__r.LL_Location__c,Item_Description__c,SAP_Item_Code__c,Item_Group_Name__c,
                                                Approved_Make__c,UOM__c,Total_Initial_Stock__c,Total_Billed_Quantity__c,Total_Remaining_Stock__c from Project_Items__c 
                                                where(Execution__r.Project__c IN :proIds  )
                                                AND Total_Remaining_Stock__c > 0];

Make the same with another SOQL but you need to collect pro.Opportunity_ID__c to separate collection and use in WHERE statement

I would also advice you to check Apex Best Practices not to face that kind of issues again

3
  • Can you elaborate the solution on code. So, I can understand better & Can I call this method in future?????? if yes then how?
    – apz
    Commented Jan 12, 2023 at 11:31
  • @apz The main point to realize here is that you should be gathering data to feed into your queries first. This allows you to remove the queries from the loops. Instead of where Execution__r.Project__c = :pro.id (pro being the loop variable), you could do that outside of the loop by making the filter where Execution__r.Project__c IN :newProjectMap.keySet(). See How do I bulkify queries for directly or indirectly related records for a more thorough explanation.
    – Derek F
    Commented Jan 12, 2023 at 13:27
  • I tried but getting same error. Can you modify the code. So, I can understand were I Did wrong.
    – apz
    Commented Jan 14, 2023 at 3:31
0

Could you please try to replace updateNoMaterialTool method with this:

public void updateNoMaterialTool() {
    
    List<Projects__c> UpdateProject=new  List<Projects__c>();
    
    Set<Id> projectsIds = newProjectMap.keySet();
    List<Project_Items__c> projectItemList=[select id,Name,Execution__r.Name,Total_GRPO_Quantity__c,Execution__r.Project__c,Execution__r.Project__r.LL_Location__c,Item_Description__c,SAP_Item_Code__c,Item_Group_Name__c,
                                                Approved_Make__c,UOM__c,Total_Initial_Stock__c,Total_Billed_Quantity__c,Total_Remaining_Stock__c from Project_Items__c 
                                                where(Execution__r.Project__c IN :projectsIds )
                                                AND Total_Remaining_Stock__c > 0];
                                                
    system.debug('projectItemList'+projectItemList);
    
    Set<String> opportunitiesIds = collectSobjectsFieldValues(newProjectMap.values(), 'Opportunity_ID__c');
    List<Tools_Master__c> toolList=[select id,Tool_ID__c,Name,Tool_Description__c,Make__c,Region__c,Serial_Number__c,Procured_Date__c,
                                                Tool_Status__c,Warehouse_Name__c,Calibration_Certificate_URL__c,Project_Names__c from Tools_Master__c 
                                                where Project_Names__c IN :opportunitiesIds];
                                                
    system.debug('toolList'+ toolList);
                                                
    for( Projects__c pro: newProjectMap.values() ){
            if( projectItemList.isEmpty() ){
            if(toolList.isEmpty()){
                    pro.No_Material_to_Return_Tools__c=true;
                }
            }
        if( projectItemList.isEmpty() ){
            if(!toolList.isEmpty()){
                    pro.No_Material_to_Return_Tools__c=false;
                }   
        }
        
        if(! projectItemList.isEmpty() ){
            if(toolList.isEmpty()){
                    pro.No_Material_to_Return_Tools__c=false;
                }   
        }
        
    }
}

private Set<String> collectSobjectsFieldValues(List<SObject> sobjectList, String fieldName) {
    Set<String> result = new Set<String>();
    for(SObject record: sobjectList) {
        result.add((String)record.get(fieldName));
    }
    
    return result;
}

I made it in notepad. This peace of code can have errors

14
  • Set<String> opportunitiesIds = collectSobjectsFieldValues(newProjectMap.values(), 'Opportunity_ID__c'); having error Illegal assignment from Set<Object> to Set<String>
    – apz
    Commented Jan 14, 2023 at 4:40
  • Update the row, the new version is: Set<String> opportunitiesIds = (Set<String>)collectSobjectsFieldValues(newProjectMap.values(), 'Opportunity_ID__c');
    – Konstantin
    Commented Jan 14, 2023 at 19:08
  • Incompatible types since an instance of Set<Object> is never an instance of Set<String> this is the issue I am facing after updating the line.
    – apz
    Commented Jan 16, 2023 at 8:44
  • I updated code in the answer, could you please try to it?
    – Konstantin
    Commented Jan 16, 2023 at 22:11
  • Still showing Too many SOQL "Class.ProjectTriggerHandler.updateNoMaterialTool: line 24, column 1 Class.ProjectTriggerHandler.operationBeforeUpdate: line 12, column 1 Trigger.ProjectTrigger: line 7, column 1"
    – apz
    Commented Jan 17, 2023 at 3:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .