+ Start a Discussion

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>();

    for(Opportunity_Line_Item__c opp: Trigger.New){
        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;      
What it's supposed to do is for each Opportunity_Line_Item object, we examine a date custom field (Win_Date__c) and try to determine which fiscal month it's belonging to, and then set the value according to another custom field (Fiscal_Month__c) on that same object.
We already set the period label correct in the setup menu, so the Period table would have some thing like:
Startdate        Enddate          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.




Message Edited by go_bear_2001 on 09-21-2009 06:06 PM

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: 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.