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
Olver_BassovOlver_Bassov 

System.LimitException: Too many SOQL queries: 101


I'm getting this error on my Trigger. 

I believe the part of the code that is causing this is:

public static void ProcessScheduledMaintenanceEmailsTrigger(
            List<Scheduled_Maintenance_Email__c> sched_new,
            List<Scheduled_Maintenance_Email__c> sched_old) {
    
        for (integer i = 0; i < sched_new.size(); i++) {
            if (sched_new[i].Email_Triggered__c == true && sched_old[i].Email_Triggered__c == false) {
                // To send the email, change the state to the scheduled renewal state, which
                // will cause the opportunityAutoRenewalStateChangedTrigger to fire, which
                // in turn will call SendOrScheduleEmail() below
                List<Opportunity> opps = [  SELECT
                                                Id,
                                                Auto_Renewal_State__c
                                            FROM
                                                Opportunity
                                            WHERE
                                                Id= :sched_new[i].Opportunity__c
                ];


If someone knows how to fix this it will be much appreciatted. I have been googling about it but haven't understood how I'd take the select out of the loop.

Thank you

bob_buzzardbob_buzzard
You'll need to pull back all of the opportunities for all of the sched_new records in one go, and then process those individually.

public static void ProcessScheduledMaintenanceEmailsTrigger(
            List<Scheduled_Maintenance_Email__c> sched_new,
            List<Scheduled_Maintenance_Email__c> sched_old) {
    

        // set up the related opportunity ids
        Set<Id> oppIds=new Set<Id>();
        for (integer i = 0; i < sched_new.size(); i++) {
            if (sched_new[i].Email_Triggered__c == true && sched_old[i].Email_Triggered__c == false) {
              oppIds.add(sched_new[i].Opportunity__c);
        }

        // query them all back at once
        List<Opportunity> opps = [select id, Auto_Renewal_State__c from Opportunity where id in :oppIds];

        // put them in a map keyed by id
        Map<Id, Opportunity> oppsById=new Map<Id, Opportunity>();
        oppsById.putAll(opps);

       
        for (integer i = 0; i < sched_new.size(); i++) {
            if (sched_new[i].Email_Triggered__c == true && sched_old[i].Email_Triggered__c == false) {
                // To send the email, change the state to the scheduled renewal state, which
                // will cause the opportunityAutoRenewalStateChangedTrigger to fire, which
                // in turn will call SendOrScheduleEmail() below

                // get the opportunity from the map based on the id in the lookup
                Opportunity opp=oppsById.get(sched_new[i].Opportunity__c);
  
                // do what you need to do with the opportunity here


Subhash GarhwalSubhash Garhwal
Hi Olavo Grego,

You are using SOQL query in for loop. You need to apply this SOQL query outside the for loop :
I have made some changes in code.

public static void ProcessScheduledMaintenanceEmailsTrigger(List<Scheduled_Maintenance_Email__c> sched_new, List<Scheduled_Maintenance_Email__c> sched_old) {

//Set ot hold Opportunity Id
Set<String> setOppIds = new Set<String>();

for (integer i = 0; i < sched_new.size(); i++) {

  if (sched_new[i].Email_Triggered__c == true && sched_old[i].Email_Triggered__c == false) {

   //Add Opportunity In set
   setOppIds.add(:sched_new[i].Opportunity__c);
  }
}

List<Opportunity> opps = [  SELECT Id, Auto_Renewal_State__c FROM Opportunity WHERE Id IN : setOppIds];
}

Olver_BassovOlver_Bassov
bob thanks for your reply,

When I try to compile I'm getting `Duplicate variable: i (attempt to re-create the variable with type: integer)` 
The two lines with for ( integer = i ...   is not compiling

What should I do ?  Should I change the second for to another variable like j or Should I just use the i without defining it again ? 

I'm beginner with apex.

Thank you.

bob_buzzardbob_buzzard
I'd change the first one to idx (and obviously the use inside the loop) - leave your initial stuff as it is.
Olver_BassovOlver_Bassov
Thanks for your reply Subhash Garhwal. 

Thanks for all the help guys. It was very appreciated.
Olver_BassovOlver_Bassov
Hi Subhash Garhwal ,

I'm trying to compile your code but is giving me an error on this line

setOppIds.add(:sched_new[i].Opportunity__c);

error ->  expect right parenthesis found : 


Do you know how I can fix this ?

Thanks

Subhash GarhwalSubhash Garhwal
Hi Olver,
I just made the typo mistake.
try this one :
setOppIds.add(sched_new[i].Opportunity__c);