function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
go_bear_2001go_bear_2001 

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.

 

Thanks,

Klein

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

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 


go_bear_2001go_bear_2001
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