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
cathy369cathy369 

In Opportunity trigger try to retrieve other Opportunity records based on criteria

In my opportunity, I have fields where they can schedule machines for jobs.  The machine is a custom object that has a maximum qty that can be scheduled in a rolling 9 day period.  I'm trying to keep them from over scheduling the machine so I need to select all opportunities in the 9 day range that are still open and are scheduled on the same machine as the new opportunity. 

I've written the below trigger, but I don't know how to format the List or Map to get the collection of Opportunities I need - I get an error, Error: Compile Error: expecting a colon, found 'o.Mfg_Machine__c' at line 28 column 66 (I've bolded the offending List statement below).

Any help would be greatly appreciated!

trigger oppMachineSchd on Opportunity (before update, before insert) {

   Map<Opportunity> oppMfgNew = new Map<id,Opportunity>();
   List<Opportunity> oppsOnMachine = new List<Opportunity>();
   Set<Id> machId = new Set<Id>();

   for (Opportunity opp:System.Trigger.new){
      if (opp.isWon && opp.Manufacturing_Due_Date_F__c != null && Manufacturing_Complete_D__c == null){
         system.debug('in new: ' + opp.Machine__c + ' ' + opp.Manufacturing_Due_Date_F__c);
         oppMfgNew.add(opp);  
         machId.add(opp.Mfg_Machine__c);
      }
   }
   
   if (OppMfgNew.size() > 0){
      Map<Id, Machine__c> mach = new Map<Id, Machine__c>(
        [SELECT Id, Name, Max_7_Day_Qty__c 
           FROM Machine__c 
           WHERE id in :machId]);

// get all open             
      for (Opportunity o:oppMfgNew){
         BeginDate = o.Manufacturing_Due_Date_F__c.addDays(-9);
         system.debug('working on: ' + o.Name + ' ' + o.Manufacturing_Due_Date_F__c + ' ' + BeginDate);
         List<Opportunity> oppsOnMachine = [SELECT Name, Mfg_Machine__c, Manufacturing_Due_Date_F__c, Quantity__c
                                           FROM Opportunity 
                                           WHERE Mfg_Machine__c = o.Mfg_Machine__c AND Manufacturing_Complete_D__c = null AND Manufacturing_Due_Date_F__c >= BeginDate 
                                                 AND Manufacturing_Due_Date_F__c <= o.Manufacturing_Due_Date_F__c]; 
        
         for (Opportunity oom:oppsOnMachine){
            system.debug('oom: ' + Name + ' ' + oom.Mfg_Machine__c + ' ' + oom.Quantity__c);
            totQty += oom.Quantity__c;
         }
        
         totQty += o.Quantity__c;
         Machine__c mQty = mach.get(o.Machine__c);
         if (totQty > mQty.Max_7_Day_Qty__c){
            o.addError('Machine is overscheduled; Please select a later date');
         }
      }
   }
}       
 
Best Answer chosen by cathy369
RaidanRaidan
Hi,

You will have to use the binding variable. The SOQL will look like this: 
SELECT Name, Mfg_Machine__c, Manufacturing_Due_Date_F__c, Quantity__c
                                           FROM Opportunity 
                                           WHERE Mfg_Machine__c = :o.Mfg_Machine__c AND Manufacturing_Complete_D__c = null AND Manufacturing_Due_Date_F__c >= BeginDate 
                                                 AND Manufacturing_Due_Date_F__c <= :o.Manufacturing_Due_Date_F__c
BTW, you will have to bulkify your code. Executing a SOQL inside of a loop is a big no-no in Force.com development.

All Answers

RaidanRaidan
Hi,

You will have to use the binding variable. The SOQL will look like this: 
SELECT Name, Mfg_Machine__c, Manufacturing_Due_Date_F__c, Quantity__c
                                           FROM Opportunity 
                                           WHERE Mfg_Machine__c = :o.Mfg_Machine__c AND Manufacturing_Complete_D__c = null AND Manufacturing_Due_Date_F__c >= BeginDate 
                                                 AND Manufacturing_Due_Date_F__c <= :o.Manufacturing_Due_Date_F__c
BTW, you will have to bulkify your code. Executing a SOQL inside of a loop is a big no-no in Force.com development.
This was selected as the best answer
cathy369cathy369
Hello there,

Thanks very much for your help.

I've changed the trigger to be bulkified...I'm posting it as I'm not a very saavy apex coder and not sure if this is the most efficient way??

Anyways, thanks again.

p.s. how did you paste your code in?
_____________________________
trigger oppMachineSchd on Opportunity (before update, before insert) {

   Map<id,Opportunity> oppMfgNew = new Map<id,Opportunity>();
   Map<Id, Machine__c> mach = new Map<Id, Machine__c>();
   List<Opportunity> oppsOnMachine = new List<Opportunity>();
   Set<Id> machId = new Set<Id>();

   date dueDate = system.today();
   date beginDate = system.today();
   decimal totQty = 0.0;
   
   for (Opportunity opp:System.Trigger.new){
      if (opp.isWon && opp.Manufacturing_Due_Date_F__c != null && opp.Manufacturing_Complete_D__c == null){
         oppMfgNew.put(opp.id,opp); 
         machId.add(opp.Mfg_Machine__c);         
// find the date range for all records being processed
         if (opp.Manufacturing_Due_Date_F__c > dueDate){
            dueDate = opp.Manufacturing_Due_Date_F__c;
         }
         if (opp.Manufacturing_Due_Date_F__c < beginDate){
            beginDate = opp.Manufacturing_Due_Date_F__c;
         }
      }
   }
  
   if (OppMfgNew.size() > 0){
      mach = new Map<Id, Machine__c>([SELECT Id, Name, Max_7_Day_Qty__c FROM Machine__c 
                                      WHERE id in :machId]);
      oppsOnMachine = [SELECT Name, Mfg_Machine__c, Manufacturing_Due_Date_F__c, Quantity__c
                       FROM Opportunity 
                       WHERE Manufacturing_Complete_D__c = null AND Manufacturing_Due_Date_F__c >= :beginDate 
                             AND Manufacturing_Due_Date_F__c <= :dueDate]; 
   }

// get all open             
   for (Opportunity o:oppMfgNew.values()){

      system.debug('working on: ' + o.Name + ' ' + o.Manufacturing_Due_Date_F__c + ' ' + o.Manufacturing_Due_Date_F__c.addDays(-9));
    
      for (Opportunity oom:oppsOnMachine){
         if (oom.Mfg_Machine__c == o.Mfg_Machine__c && oom.Manufacturing_Due_Date_F__c <= o.Manufacturing_Due_Date_F__c &&
               oom.Manufacturing_Due_Date_F__c >= o.Manufacturing_Due_Date_F__c.addDays(-9)){
            totQty += oom.Quantity__c;
         }
      }
        
      totQty += o.Quantity__c;
      Machine__c mQty = mach.get(o.Mfg_Machine__c);

      if (totQty > mQty.Max_7_Day_Qty__c){
         o.addError('Machine is overscheduled; Please select a later date');
      }
   }
}
 
RaidanRaidan
Cathy,

I don't see any red flags in your code. I don't know about efficiency :-) You can always refactor your code after you write the unit test later. To paste your code to the editor, just click the < > button next to the image (in the toolbar). Please mark my answer if you think we have resolved the issue. Thanks!
cathy369cathy369
Thanks again Raidan.