Bulkifying a trigger with a fiscal year/quarter specification

apextrigger

Please, give me a hint about how to approach the problem.

Problem

Every first Event based on StartDate in a Fiscal year and quarter should have the FirstEvent__c boolean field set to TRUE, other Event records should have it set to FALSE.

Example fiscal quarters:

  • 1/1/2021 – 3/31/2021 – first quarter of year 2021
  • 4/1/2021 – 6/30/2021 – second quarter of year 2021
  • 7/1/2021 – 9/30/2021 – third quarter of year 2021
  • 10/1/2021 – 12/31/2021 – fourth quarter of year 2022
  • 1/1/2021 – 3/31/2022 – first quarter of year 2022
  • 4/1/2021 – 6/30/2022 – second quarter of year 2022
  • 7/1/2021 – 9/30/2022 – third quarter of year 2022
  • 10/1/2021 – 12/31/2022 – fourth quarter of year 2022

So if we had 1000 events in a database, every event which starts the first in a given quarter of a given year should be flagged as FirstEvent__c, other Event records should be false.

Is there a way to avoid a SOQL in a for loop just like this?

trigger EventTrigger on Event (after insert, after update) {

    List<Event> updates = new List<Event>();
    
    for(Event e1 : trigger.new){
        Integer year = e1.StartDateTime.year();
        Integer quarter = (e1.StartDateTime.month() / 3.0).round(System.RoundingMode.CEILING).intValue();
    
        List <Event> events = [SELECT id, FirstEvent__c FROM Event WHERE CALENDAR_YEAR = :year AND CALENDAR_MONTH = :quarter ORDER BY StartDateTime];
    
        Boolean first = true;

        for(Event e2 : events){
            if(first){
                e2.FirstEvent__c = true;
                first = false;
            } else {
                e2.FirstEvent__c = false;
            }
            updates.add(e2);
         }
    }  
    update updates;
}

Best Answer

Even if we can ut first event query outide of for loop , you will still face issue in case of large data volumes if soql return more thn 50k records.

with your current approach you will iterate over complete events in your org based on quarter and year which is very resource intensive.

Why not do it in incremental manner. There can be 2 scenarios:

  1. you have firstevent for given quarter and year -> then compare all trigger records with first event and keep on storing latest first event

  2. you do not have any event for quarter year -> make first trigger record first event nd compare remaining event as per 1.

please go through this code for increment comparison and updating first event. in case you want to go through your approach for comparing every event then batch class is more feasible not trigger.

trigger EventTrigger on Event (after insert, after update) {
    //final list to update
    List<Event> updates = new List<Event>();
    // list to hold previous first event  which  will be updated to false if first event is true
    List<Event> EventToUpdateFEFalse  =  new List<Event>();
    
    Map<String,Event> firstEventMapByQuarterYear = new Map<String,Event>();
    // iterate  already exisiting first  event and store in map  by quarter-year 
    // this map  will keep on updating first event  for particular quarter year based on startdatetime comparison   
    for(Event e : [SELECT id, FirstEvent__c,startDateTime FROM Event where FirstEvent__c = true] ) 
    {
       firstEventMapByQuarterYear.put(String.valueof(e.StartDateTime.year()) + '-'  + String.valueof((e.StartDateTime.month() / 3.0).round(System.RoundingMode.CEILING).intValue()) , e); 
     
    
    }
    
    for(Event e1 : trigger.new){
        Integer year = e1.StartDateTime.year();
        Integer quarter = (e1.StartDateTime.month() / 3.0).round(System.RoundingMode.CEILING).intValue();
        // if  there are  no event for particular year  put  current event in map.
        if(  !firstEventMapByQuarterYear.containskey(String.valueof(year) + '-' + String.valueof(quarter)))
        {
                     firstEventMapByQuarterYear.put(String.valueof(year) + '-' + String.valueof(quarter) , e1);
        }
         // if there is first event  compare  startdatetime  and update  first event accordingly
        if( firstEventMapByQuarterYear.containskey(String.valueof(year) + '-' + String.valueof(quarter))
           && e1.startdatetime <  firstEventMapByQuarterYear.get(String.valueof(year) + '-' + String.valueof(quarter)).startdatetime)
           {
             EventToUpdateFEFalse.add(firstEventMapByQuarterYear.get(String.valueof(year) + '-' + String.valueof(quarter) ));
             firstEventMapByQuarterYear.put(String.valueof(year) + '-' + String.valueof(quarter) , e1);

            }
        
         }
         
         
    // loop through all first events and make first_event__c true 
    for(Event e : firstEventMapByQuarterYear.values())
    {
      if( e.FirstEvent__c == false)
      {
      
      updates.add(new Event(id = e.Id , FirstEvent__c = true ));
      }
    
    
    }
    
        // loop through all previous first events and make first_event__c false 

    for(Event e : EventToUpdateFEFalse)
    {
       if(e.FirstEvent__c == true)
       {
          updates.add(new Event(id = e.Id , FirstEvent__c = false ));

       }
    
    }
    
    
    update  updates;
}
Related Topic