You need to sign in to do that
Don't have an account?
Help with writing bulk triggers for query with date range
I have this trigger working but have a hard time trying to convert it to a bulk trigger to get around w/ the governor limit of invoking too many SQLs:
==========
trigger Assign_FiscalMonth on Opportunity_Line_Item__c (before insert, before update) {
List<Opportunity_Line_Item__c> optyToUpdate = new List<Opportunity_Line_Item__c>();
Date d = opp.Win_Date__c;
Period p = [select PeriodLabel from Period where Type='Month' and Startdate <= :d and Enddate >= :d];
opp.Fiscal_Month__c = p.PeriodLabel;
}
}
09/15/2009 10/13/2009 Oct
10/14/2009 11/20/2009 Nov
11/21/2009 12/16/2009 Dec
and so on.....
I'm thinking of create a set for all the date field (Win_Date__c), but could not come up w/ any way where I can use IN in my query.
Really appreaciate if anyone shares any light on this.
Thanks,
Klein
I would do like this (and did like this):
date dtEarliest = null; date dtLatest = null; List<OpportunityLine_Line_Item__c> oliListTmp = new List<OpportunityLine_Line_Item__c>(); for(OpportunityLine_Line_Item__c oli: Trigger.New){ // find earliest Win_Date__c and set it in dtEaliest
if(dtEarliest == null || dtEarliest > oli.Win_Date__c) dtEarliest = oli.Win_Date__c;
// find latest Win_Date__c and set it in dtLatest
if(dtLatest == null || dtLatest < oli.Win_Date__c) dtLatest = oli.Win_Date__c; //// so, you just look for Period.EndDate >= dtEarliest && Period.StartDate <= dtLatest // and prepare for the working list oliListTMp.add(oli); } for(Period p : [select PeriodLabel from Period where Type='Month' and StartDate <= :dtLatest and EndDate >= :dtEarliest]){ if(oliListTmp.size() == 0) break; for(integer i=oliListTmp.size()-1; i>=0; i--){ if(p.startdate <= oliListTmp[i].Win_Date__c && p.enddate >= oliListTmp[i].Win_Date__c){ oliListTmp[i].Fiscal_Month__c = o.PeriodLabel; oliListTmp.remove(i); } } } if(oliListTmp.size()!=0) { //some Opportunity_Line_Item__c couldn't find it's period... do something }
If there are very old Win_Date__c and very future WIn_Date__c, this may cost much... but I hope it's not going to be over than 1000 periods.
ThomasTT
ThomasTT: Thanks much for your detail code. It should work for us since we only have 12 periods per year and we have 5-6 fiscal years need to set.
--Klein