Sign Up ›

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

How to move SOQL out of loop to avoid SOQL 101 error.

Hello All

The code below executes as expected, however when attempting to deploy I receive  a SOQL 101 error.
I believe it is due to the bolded line of code below inside of the Opportunity for loop.  How can I move this outside the loop
and still match the candidate to the candidate on the opportunity?

Thanks in Advance



trigger OpportunityChange on Opportunity (before insert, before update) {

List<Quintile__c> Quintiles = new List<Quintile__c> ();  
Quintiles = [Select Quintile__c, T12_Min__c,T12_Max__c, LOS_Min__c, LOS_Max__c
       FROM 
              Quintile__c
      WHERE
        start_date__c <= today AND
       End_date__c > today];
    
  for (Opportunity o : Trigger.new) {
     List<Contact> Contacts = new List<Contact> (); 
     Contacts = [Select ID, Total_years_as_a_rep__c from Contact where ID =: o.Contact__c limit 1];
     for (Contact c : Contacts){
         for (Quintile__c q : Quintiles) {
              if(q.T12_Min__c <= o.Post_Haircut_T12__c &&
              q.T12_Max__c > o.Post_Haircut_T12__c &&
              q.LOS_Min__c <= c.Total_years_as_a_rep__c &&
             q.LOS_Max__c > c.Total_years_as_a_rep__c) {
          o.Quintile__c = q.Quintile__c;
        }
          }
   }
     }
}
SFDC_DevloperSFDC_Devloper
Hi,

try below code...

trigger OpportunityChange on Opportunity (before insert, before update) {

List<Quintile__c> Quintiles = new List<Quintile__c> ();  
List<Contact> Contacts = new List<Contact> (); 
Opportunity opp=new Opportunity();

Quintiles = [Select Quintile__c, T12_Min__c,T12_Max__c, LOS_Min__c, LOS_Max__c
       FROM Quintile__c WHERE start_date__c <= today AND End_date__c > today];

Contacts = [Select ID, Total_years_as_a_rep__c from Contact where ID =: opp.Contact__c limit 1];
    
  for (Opportunity o : Trigger.new) {
    
     
     for (Contact c : Contacts){
         for (Quintile__c q : Quintiles) {
              if(q.T12_Min__c <= o.Post_Haircut_T12__c &&
              q.T12_Max__c > o.Post_Haircut_T12__c &&
              q.LOS_Min__c <= c.Total_years_as_a_rep__c &&
             q.LOS_Max__c > c.Total_years_as_a_rep__c) {
          o.Quintile__c = q.Quintile__c;
        }
          }
   }
     }
}

[If it helps, mark it as "Best Answer"]

Thanks,
Rockzz
Sure@DreamSure@Dream
Hi law,

You can update the trigger as below:

trigger OpportunityChange on Opportunity (before insert, before update) {

List<Quintile__c> Quintiles = [Select Quintile__c, T12_Min__c,T12_Max__c, LOS_Min__c, LOS_Max__c FROM Quintile__c
                                               WHERE start_date__c <= today AND End_date__c > today];
Set<ID> setOfIds=new Set<Id>();
for(Opportunity opp: Trigger.new)
{
    setOfIds.add(opp.Id);
}
List<Contact> Contacts = [Select ID, Total_years_as_a_rep__c,Opportunity from Contact where ID IN :setOfIds];
 Map<Id,Contact> mapOfIdContact=new Map<Id,Contact>();
for(Contact c:Contacts)
{
  mapOfIdContact.put(c.Opportunity,c);
}
  for (Opportunity o : Trigger.new)
{
     Contact c=mapOfIdContact.get(o.Id);
     for (Contact c : Contacts){
         for (Quintile__c q : Quintiles) {
              if(q.T12_Min__c <= o.Post_Haircut_T12__c &&
              q.T12_Max__c > o.Post_Haircut_T12__c &&
              q.LOS_Min__c <= c.Total_years_as_a_rep__c &&
             q.LOS_Max__c > c.Total_years_as_a_rep__c) {
          o.Quintile__c = q.Quintile__c;
        }
          }
   }
     }
}

mark it as Best Answer, if it solves your problem
lawlaw
Rockzz

opp.Contact__c  is  NULL
lawlaw
Sure@Dream

In your suggestion Opportunity is not a column on the object Contact

"List<Contact> Contacts = [Select ID, Total_years_as_a_rep__c,Opportunity from Contact where ID IN :setOfIds];"
SFDC_DevloperSFDC_Devloper
Hi,

try below code...
trigger OpportunityChange on Opportunity (before insert, before update) {

List<Quintile__c> Quintiles = new List<Quintile__c> ();  
List<Contact> Contacts = new List<Contact> (); 
List<Opportunity> opp=[SELECT Id,Name,Contact__c  from Opportunity ];

Quintiles = [Select Quintile__c, T12_Min__c,T12_Max__c, LOS_Min__c, LOS_Max__c
       FROM Quintile__c WHERE start_date__c <= today AND End_date__c > today];

Contacts = [Select ID, Total_years_as_a_rep__c from Contact where ID =: opp[0].Contact__c ];
    
  for (Opportunity o : Trigger.new) {
    
     
     for (Contact c : Contacts){
         for (Quintile__c q : Quintiles) {
              if(q.T12_Min__c <= o.Post_Haircut_T12__c &&
              q.T12_Max__c > o.Post_Haircut_T12__c &&
              q.LOS_Min__c <= c.Total_years_as_a_rep__c &&
             q.LOS_Max__c > c.Total_years_as_a_rep__c) {
          o.Quintile__c = q.Quintile__c;
        }
          }
   }
     }
}

Thanks
lawlaw
Contacts = [Select ID, Total_years_as_a_rep__c from Contact where ID =: opp[0].Contact__c ];

opp[0].Contact__c  only gives you the first opportunity in your list of opportunities.  What I need is the contact of the current opportunity.    

This has been changed a quite a bit and I will be posting with the new code