function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Anirudh Sharma 5Anirudh Sharma 5 

too many SOQL queries 101 error

Hello when I click a button which checks the review records in my org I am getting this error too many SOQL queries 101. Ii am pasting the trigger and it says in line 52 and line 54 it is throwing the error. Can you please help me out in this. I have highlighted the SOQL below

public static void checkTier( Advisory_Panel_Application__c advisoryp,Advisory_Panel_Application_Review__c advisorypanels){
        Id recordTypeId = Schema.SObjectType.Advisory_Panel_Application_Review__c.getRecordTypeInfosByName().get(Label.Tier_2_Review_Advsiory_Panel_on_Addressing_Disparities).getRecordTypeId();
        Id recordTypeId1 = Schema.SObjectType.Advisory_Panel_Application_Review__c.getRecordTypeInfosByName().get(Label.Tier_2_Review_Advisory_Panel_on_Clinical_Trials).getRecordTypeId(); 
        Id recordTypeId2 = Schema.SObjectType.Advisory_Panel_Application_Review__c.getRecordTypeInfosByName().get(Label.Tier_2_Review_Advisory_Panel_on_APDTO).getRecordTypeId(); 
        Id recordTypeId3 = Schema.SObjectType.Advisory_Panel_Application_Review__c.getRecordTypeInfosByName().get(Label.Tier_2_Review_Advisory_Panel_on_Improving_Healthcare_System).getRecordTypeId(); 
        Id recordTypeId4 = Schema.SObjectType.Advisory_Panel_Application_Review__c.getRecordTypeInfosByName().get(Label.Tier_2_Review_Advisory_Panel_on_Rare_Disease).getRecordTypeId(); 
        Id recordTypeId5 = Schema.SObjectType.Advisory_Panel_Application_Review__c.getRecordTypeInfosByName().get(Label.Tier_2_Review_Advsiory_Panel_on_Communication_and_Dissemination_Research).getRecordTypeId(); 
        Id recordTypeId6 = Schema.SObjectType.Advisory_Panel_Application_Review__c.getRecordTypeInfosByName().get(Label.Tier_2_Review_Advsiory_Panel_on_Patient_Engagement).getRecordTypeId(); 
        String addressing =Label.Addressing_Disparities;
        String apdto=Label.APDTO; 
        String clinical=Label.Clinical_Trials;
        String improvinghs=Label.Improving_Healthcare_Systems;
        String rared=Label.Rare_Disease;
        String commdere=Label.Communication_and_Dissemination_Research;
        String patiente=Label.Patient_Engagement;
        String pass =Label.Pass;
        String fail=Label.Fail;
        String yes=Label.Yes;
        String maybe=Label.Maybe;
        String noo=Label.No;
        String alternate =Label.Alternate;
         String adminname=Label.AdminUser;
 String tier1name=Label.Tier_1_name;
        String tier1name1=Label.Tier_1_name2;
        String tier2name=Label.Tier_2_name;
        String tier2name1=Label.Tier_2_name1;
        String tier1Status=Label.Tier_1;
        String tier1SubStatus=Label.Tier_1_Under_Review;
        String tier1SubStatusHold=Label.Tier_1_Hold;
        String tier2Status=Label.Tier_2;
        String tier2SubStatus=Label.Tier_2_Under_Review;
        String tier2SubStatusHold=Label.Tier_2_Hold;
        String tier3Status=Label.Tier_3;
        String tier3SubStatus=Label.Tier_3_Under_Review;
        
            User usr=[Select id, name from User where name=:adminname limit 1];  
        // U- returns the user id of the User with name 'PCORI SF Administrator'
        Advisory_Panel_Application_Review__c advisoryr1 = [select id, Review_Name__c,Review_Complete__c,Tier_1_Tag__c,Advisory_panel_s_applied_to__c,OwnerId,Advisory_Panel_Application__c,Advisory_Panel__c,Owner.Name from Advisory_Panel_Application_Review__c where Advisory_Panel_Application__c=:advisorypanels.Advisory_Panel_Application__c and Review_Name__c =:tier1name and Advisory_Panel__c=:advisorypanels.Advisory_Panel__c limit 1];
        // ap - Returns the Advisory Panel Application Review record for Tier 1 Review 1 with the details queried in the SOQL
        Advisory_Panel_Application_Review__c advisoryr2 = [select id, Review_Name__c,Review_Complete__c,Tier_1_Tag__c,Advisory_panel_s_applied_to__c,OwnerId,Advisory_Panel_Application__c,Advisory_Panel__c,Owner.Name from Advisory_Panel_Application_Review__c where Advisory_Panel_Application__c=:advisorypanels.Advisory_Panel_Application__c and Review_Name__c=:tier1name1 and Advisory_Panel__c=:advisorypanels.Advisory_Panel__c limit 1]; 
        // ap1 - Returns the Advisory Panel Application Review record for Tier 1 Review 2 with the details queried in the SOQL
        if((advisorypanels.Tier_2_Tag__c != yes && advisorypanels.Tier_2_Tag__c != noo && advisorypanels.Tier_2_Tag__c != alternate ) && advisoryr1.Review_Complete__c == true && advisoryr2.Review_Complete__c==true){
            if((advisoryr1.Tier_1_Tag__c == yes|| advisoryr1.Tier_1_Tag__c== Maybe) && (advisoryr2.Tier_1_Tag__c == yes ||advisoryr2.Tier_1_Tag__c == Maybe)){
                if(advisoryp.Addressing_Disparities_Result__c!=pass && advisoryr1.Advisory_Panel__c.equals(addressing) && advisoryr2.Advisory_Panel__c.equals(addressing)){
                    AP_CreateReviewersPanelHandler.createTier2(advisoryr1,advisoryr2,recordTypeId,usr.id,addressing);
                    advisoryp.Addressing_Disparities_Result__c =pass;
                }
                if(advisoryp.Clinical_Trials_Result__c!=pass && advisoryr1.Advisory_Panel__c.equals(clinical) && advisoryr2.Advisory_Panel__c.equals(clinical)){
                    AP_CreateReviewersPanelHandler.createTier2(advisoryr1,advisoryr2,recordTypeId1,usr.id,clinical);
                    advisoryp.Clinical_Trials_Result__c=pass;
                }
                
                if(advisoryp.APDTO_Result__c!=pass && advisoryr1.Advisory_Panel__c.equals(apdto) && advisoryr2.Advisory_Panel__c.equals(apdto)){
                    AP_CreateReviewersPanelHandler.createTier2(advisoryr1,advisoryr2,recordTypeId2,usr.id,apdto);
                    advisoryp.APDTO_Result__c=pass;
                }
                
                if(advisoryp.IHS_Result__c!=pass && advisoryr1.Advisory_Panel__c.equals(improvinghs) && advisoryr2.Advisory_Panel__c.equals(improvinghs)){
                    AP_CreateReviewersPanelHandler.createTier2(advisoryr1,advisoryr2,recordTypeId3,usr.id,improvinghs);
                    advisoryp.IHS_Result__c=pass;
                }
                
                if(advisoryp.Rare_Disease_Result__c!=pass && advisoryr1.Advisory_Panel__c.equals(rared) && advisoryr2.Advisory_Panel__c.equals(rared)){
                    AP_CreateReviewersPanelHandler.createTier2(advisoryr1,advisoryr2,recordTypeId4,usr.id,rared);
                    advisoryp.Rare_Disease_Result__c=pass;
                }
                
                if(advisoryp.CDR_Result__c!=pass && advisoryr1.Advisory_Panel__c.equals(commdere) && advisoryr2.Advisory_Panel__c.equals(commdere)){
                    AP_CreateReviewersPanelHandler.createTier2(advisoryr1,advisoryr2,recordTypeId5,usr.id,commdere);
                    advisoryp.CDR_Result__c=pass;
                }
                
                if(advisoryp.Patient_Engagement_Result__c!=pass && advisoryr1.Advisory_Panel__c.equals(patiente) && advisoryr2.Advisory_Panel__c.equals(patiente)){
                    AP_CreateReviewersPanelHandler.createTier2(advisoryr1,advisoryr2,recordTypeId6,usr.id,patiente);
                    advisoryp.Patient_Engagement_Result__c=pass;
                }
                if( advisoryp.Application_Status__c!=tier3Status){
                    advisoryp.Application_Status__c=tier2Status;
                    advisoryp.Application_Sub_Status__c=tier2SubStatus;
                }
            }
            if((advisoryr1.Tier_1_Tag__c ==Maybe && advisoryr2.Tier_1_Tag__c==noo) || (advisoryr2.Tier_1_Tag__c==Maybe && advisoryr1.Tier_1_Tag__c==noo) ){
                if(advisoryp.Application_Status__c!=tier2Status && advisoryp.Application_Status__c!=tier3Status){ 
                    advisoryp.Application_Status__c=tier1Status;
                    advisoryp.Application_Sub_Status__c=tier1SubStatusHold;
                }
                if(advisoryr1.Advisory_Panel__c.equals(addressing) || advisoryr2.Advisory_Panel__c.equals(addressing)){
                    advisoryp.Addressing_Disparities_Result__c=fail;
                }
                if(advisoryr1.Advisory_Panel__c.equals(clinical) || advisoryr2.Advisory_Panel__c.equals(clinical)){
                    advisoryp.Clinical_Trials_Result__c=fail;
                }
                if(advisoryr1.Advisory_Panel__c.equals(apdto) || advisoryr2.Advisory_Panel__c.equals(apdto)){
                    advisoryp.APDTO_Result__c=fail;
                }
                if(advisoryr1.Advisory_Panel__c.equals(improvinghs) || advisoryr2.Advisory_Panel__c.equals(improvinghs)){
                    advisoryp.IHS_Result__c=fail;
                }
                if(advisoryr1.Advisory_Panel__c.equals(rared) || advisoryr2.Advisory_Panel__c.equals(rared)){
                    advisoryp.Rare_Disease_Result__c=fail;
                }
                
                if(advisoryr1.Advisory_Panel__c.equals(commdere) || advisoryr2.Advisory_Panel__c.equals(commdere)){
                    advisoryp.CDR_Result__c=fail;
                }
                
                if(advisoryr1.Advisory_Panel__c.equals(patiente) || advisoryr2.Advisory_Panel__c.equals(patiente)){
                    advisoryp.Patient_Engagement_Result__c=fail;
                }
            }
            
            if((advisoryr1.Tier_1_Tag__c ==noo && advisoryr2.Tier_1_Tag__c==noo) || (advisoryr2.Tier_1_Tag__c==noo && advisoryr1.Tier_1_Tag__c==noo) ){
                if(advisoryp.Application_Status__c!=tier2Status && advisoryp.Application_Status__c!=tier3Status){ 
                    advisoryp.Application_Status__c=tier1Status;
                    advisoryp.Application_Sub_Status__c=tier1SubStatusHold;
                }
                if(advisoryr1.Advisory_Panel__c.equals(addressing) || advisoryr2.Advisory_Panel__c.equals(addressing)){
                    advisoryp.Addressing_Disparities_Result__c=fail;
                }
                if(advisoryr1.Advisory_Panel__c.equals(clinical) || advisoryr2.Advisory_Panel__c.equals(clinical)){
                    advisoryp.Clinical_Trials_Result__c=fail;
                }
                if(advisoryr1.Advisory_Panel__c.equals(apdto) || advisoryr2.Advisory_Panel__c.equals(apdto)){
                    advisoryp.APDTO_Result__c=fail;
                }
                if(advisoryr1.Advisory_Panel__c.equals(improvinghs) || advisoryr2.Advisory_Panel__c.equals(improvinghs)){
                    advisoryp.IHS_Result__c=fail;
                }
                if(advisoryr1.Advisory_Panel__c.equals(rared) || advisoryr2.Advisory_Panel__c.equals(rared)){
                    advisoryp.Rare_Disease_Result__c=fail;
                }
                
                if(advisoryr1.Advisory_Panel__c.equals(commdere) || advisoryr2.Advisory_Panel__c.equals(commdere)){
                    advisoryp.CDR_Result__c=fail;
                }
                
                if(advisoryr1.Advisory_Panel__c.equals(patiente) || advisoryr2.Advisory_Panel__c.equals(patiente)){
                    advisoryp.Patient_Engagement_Result__c=fail;
                }
            }
            
            if((advisoryr1.Tier_1_Tag__c == yes|| advisoryr1.Tier_1_Tag__c == noo) && (advisoryr2.Tier_1_Tag__c == yes|| advisoryr2.Tier_1_Tag__c == noo) ){
                if ((advisoryr1.Tier_1_Tag__c==yes && advisoryr2.Tier_1_Tag__c==noo) || (advisoryr2.Tier_1_Tag__c==yes && advisoryr1.Tier_1_Tag__c==noo) ){ 
                    if(advisoryp.Application_Status__c!=tier2Status && advisoryp.Application_Status__c!=tier3Status){ 
                        
                        advisoryp.Application_Status__c=tier1Status;
                        advisoryp.Application_Sub_Status__c=tier1SubStatusHold; 
                    }
                    if(advisoryr1.Advisory_Panel__c.equals(addressing) || advisoryr2.Advisory_Panel__c.equals(addressing)){
                        advisoryp.Addressing_Disparities_Result__c=fail;
                    }
                    if(advisoryr1.Advisory_Panel__c.equals(clinical) || advisoryr2.Advisory_Panel__c.equals(clinical)){
                        advisoryp.Clinical_Trials_Result__c=fail;
                    }
                    if(advisoryr1.Advisory_Panel__c.equals(apdto) || advisoryr2.Advisory_Panel__c.equals(apdto)){
                        advisoryp.APDTO_Result__c=fail;
                    }
                    if(advisoryr1.Advisory_Panel__c.equals(improvinghs) || advisoryr2.Advisory_Panel__c.equals(improvinghs)){
                        advisoryp.IHS_Result__c=fail;
                    }
                    if(advisoryr1.Advisory_Panel__c.equals(rared) || advisoryr2.Advisory_Panel__c.equals(rared)){
                        advisoryp.Rare_Disease_Result__c=fail;
                    }
                    
                    if(advisoryr1.Advisory_Panel__c.equals(commdere) || advisoryr2.Advisory_Panel__c.equals(commdere)){
                        advisoryp.CDR_Result__c=fail;
                    }
                    
                    if(advisoryr1.Advisory_Panel__c.equals(patiente) || advisoryr2.Advisory_Panel__c.equals(patiente)){
                        advisoryp.Patient_Engagement_Result__c=fail;
                    }
                }
            }
            
        }
        if(advisorypanels.Tier_2_Tag__c == yes||advisorypanels.Tier_2_Tag__c== noo ||advisorypanels.Tier_2_Tag__c == alternate){
            Advisory_Panel_Application_Review__c advisoryre1 = [select id, Review_Name__c,Review_Complete__c,Tier_2_Tag__c,Advisory_panel_s_applied_to__c,OwnerId,Advisory_Panel_Application__c,Advisory_Panel__c,Owner.Name from Advisory_Panel_Application_Review__c where Advisory_Panel_Application__c=:advisorypanels.Advisory_Panel_Application__c and Review_Name__c=:tier2name and Advisory_Panel__c=:advisorypanels.Advisory_Panel__c limit 1];
            Advisory_Panel_Application_Review__c advisoryre2 = [select id, Review_Name__c,Review_Complete__c,Tier_2_Tag__c,Advisory_panel_s_applied_to__c,OwnerId,Advisory_Panel_Application__c,Advisory_Panel__c,Owner.Name from Advisory_Panel_Application_Review__c where Advisory_Panel_Application__c=:advisorypanels.Advisory_Panel_Application__c and Review_Name__c=:tier2name1 and Advisory_Panel__c=:advisorypanels.Advisory_Panel__c limit 1];   
            
            if(advisoryre1.Review_Complete__c == true && advisoryre2.Review_Complete__c==true){
                if((advisoryre1.Tier_2_Tag__c == yes|| advisoryre1.Tier_2_Tag__c == alternate || advisoryre1.Tier_2_Tag__c == noo ) && (advisoryre2.Tier_2_Tag__c == yes|| advisoryre2.Tier_2_Tag__c==alternate || advisoryre2.Tier_2_Tag__c==noo) ){
                    if(advisoryre1.Tier_2_Tag__c==yes && advisoryre2.Tier_2_Tag__c==yes){
                        if(advisoryre1.Advisory_Panel__c.equals(addressing) || advisoryre2.Advisory_Panel__c.equals(addressing)){
                            advisoryp.Addressing_Disparities_Result1__c=pass;
                        }
                        if(advisoryre1.Advisory_Panel__c.equals(clinical) || advisoryre2.Advisory_Panel__c.equals(clinical)){
                            advisoryp.Clinical_Trials_Result1__c=pass;
                        }