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:
you have firstevent for given quarter and year -> then compare all trigger records with first event and keep on storing latest first event
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.