Sign Up ›

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

Trigger Help Please - Using AggregateResult to Compare Values

Good morning, I am hoping someone can set me straight on what I am attempting to do here. 

 

  • I have a custom object Work Order that has a lookup relationship to Opportunity. 
  • There can be many Work Orders related to one opportunity. 
  • On Opportunity, I have a custom field that is populated via a trigger to count all of the Work Orders associated

I have a second trigger that is meant to

  • identify those Work Orders that are closed and,
  • if the count of Closed Work Orders = the count of all Work Orders
  • update the Opportunity Stage to Completed

I am getting lost on how to write the logic to compare the COUNT of the closed Work Orders to the value in the Opportunity field.  I'd appreciate any help!!!  Thanks.

 

trigger trigWorkorderCloseOpp on Work_Order__c (after insert, after update) {

    //************************************************
    // Build a LIST of Opportunity ID's that may
    // need closing
    //************************************************

	set <id> oppIDs = new set <id>();
	list <Opportunity> opportunity = [SELECT ID FROM Opportunity WHERE ID in:oppIDs];

	
    if(Trigger.isInsert || Trigger.isUpdate){
        for(Work_Order__c w : trigger.new){
            if(w.Opportunity__c != null && w.stage__c == 'Work Order Closed - Quickbooks')
            {oppIDs.add(w.Opportunity__c);
            } else if (w.Opportunity__c != null && w.stage__c == 'Job Completed')
            {oppIDs.add(w.Opportunity__c);
            
            }

	        }

      // INSERT/UPDATE Trigger

    if(Trigger.isDelete || Trigger.isUpdate){
        for(Work_Order__c w : trigger.old){
           if(w.Opportunity__c != null && w.stage__c == 'Work Order Closed - Quickbooks')
            {oppIDs.add(w.Opportunity__c);
            } else if (w.Opportunity__c != null && w.stage__c == 'Job Completed')
            {oppIDs.add(w.Opportunity__c);
             }
        }
    }

    if(oppIDs .size() > 0) {
    
        Map<ID, Opportunity> oppMap = new Map<ID, Opportunity>([Select id, WO_Count__c, StageName from Opportunity Where Id in :oppIds]);  	
    	
    	Opportunity d = null;
    		
    	for (AggregateResult ar : [SELECT ID, Opportunity__c, COUNT(Id)total FROM Work_Order__c WHERE Opportunity__c in: oppIds GROUP BY Opportunity__c])
    	
    		{
    			String dID = (String)dr.get('Opportunity__c');
    			Integer a = Integer.valueOf(ar.get('total'));
    			if(oppMap.get(dID) == null)
    			 d = new Opportunity(ID=string.valueOf(ar.get('Opportunity__c')));
    			
    			else
    			if(oppMap.containskey(dID)&& a.equals(Opportunity.WO_Count__c))
        		d = oppMap.get(dID);
    			
    			
    		}

    		d = opps.get(dID);
    		d.StageName = 'Job Completed';
    		
    		update opps.values();
    		
    }
    }
}

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Jeremy.NottinghJeremy.Nottingh

Maybe I don't understand your use case, but it seems like you want to automatically close an Opportunity when all Work Orders on that Opportunity are closed, right?

 

I would do this with a Trigger on Work Order that (If the Work Order was just closed) queries all the Open Work Orders on the Opportunity, and if the number of Work Orders returned is zero, you know they're all closed. I'm assuming here that your relationship is called Work_Orders__r.

 

 

Trigger CheckOpportunityWorkOrders on Work_Order__c (after insert, after update) {

   set<id> oppids = new set<id>();
   for (Work_Order__c wo: Trigger.new) {
      if (wo.stage__c == 'Work Order Closed - Quickbooks') oppids.add(wo.Opportunity__c);
   }

   list<Opportunity> affectedOpps = [select (select id from Work_Orders__r where stage__c != 'Work Order Closed - Quickbooks')
from Opportunity where id in :oppids];

   for (Opportunity o : affectedopps) {
      if (o.Work_Orders__r.size() == 0) {
         //Close your Opportunity here
      } else {
         //You can update your Opportunity field here with 
         //o.Work_Orders__r.size() if you want, but it may not be necessary
      }
   }

 

If there are more than zero open Work Orders, you can record that number if you like, but only if you'll need it later.

 

Jeremy

 

 

 

All Answers

Jeremy.NottinghJeremy.Nottingh

Maybe I don't understand your use case, but it seems like you want to automatically close an Opportunity when all Work Orders on that Opportunity are closed, right?

 

I would do this with a Trigger on Work Order that (If the Work Order was just closed) queries all the Open Work Orders on the Opportunity, and if the number of Work Orders returned is zero, you know they're all closed. I'm assuming here that your relationship is called Work_Orders__r.

 

 

Trigger CheckOpportunityWorkOrders on Work_Order__c (after insert, after update) {

   set<id> oppids = new set<id>();
   for (Work_Order__c wo: Trigger.new) {
      if (wo.stage__c == 'Work Order Closed - Quickbooks') oppids.add(wo.Opportunity__c);
   }

   list<Opportunity> affectedOpps = [select (select id from Work_Orders__r where stage__c != 'Work Order Closed - Quickbooks')
from Opportunity where id in :oppids];

   for (Opportunity o : affectedopps) {
      if (o.Work_Orders__r.size() == 0) {
         //Close your Opportunity here
      } else {
         //You can update your Opportunity field here with 
         //o.Work_Orders__r.size() if you want, but it may not be necessary
      }
   }

 

If there are more than zero open Work Orders, you can record that number if you like, but only if you'll need it later.

 

Jeremy

 

 

 

This was selected as the best answer
pmozz01pmozz01

Thanks, Jeremy! I knew I was making this much more difficult then it needed to be!  I have had a chance to finalize, but I can see that this will work now.

 

I have another question and am wondering if you have any ideas on workarounds.  I am working for a business that has an incredible number of validation rules in their instance; almost all of which I have to figure out a way around.  Is there anyway to have either validation rules not apply when a trigger is run?  I often get many errors just trying to test that are all due to the validation rules.  A strange one that I constantly get (and cannot even figure out how my trigger is firing the validation rule), throws an error saying java.lang.ArithmeticException: Division undefined.

 

Thanks, thought I'd ask.  I greatly appreciate your help today.
Jeremy.NottinghJeremy.Nottingh

If I were you, I would probably go through the sandbox environment and selectively disable validation rules until I could figure out which one is throwing the error. Most likely it's a "division by zero" type error, possibly caused by a field that it wants to be populated, but is actually null.

 

I have, in the past, and with the permission of management, added a "loophole" to validation rules that allow for testing without all the bending over backwards. E.g. my validation rule for "CloseDate cannot be earlier than this month" looks something like:

 

and( CloseDate < DATE( YEAR(TODAY()), MONTH(TODAY()), 1),
     Name != 'jeremystestopp')

 

 

This will fire validation only if the name of the Opportunity in question is not 'jeremystestopp'. Obviously, be careful with this kind of thing for the sake of security and clean data.

 

Jeremy
pmozz01pmozz01

I think after working on this the past 8 hours that I am almost there, but the trigger is still not working.  If I leave out one part of the code, then the test runs and completes saying 100% coverage, but nothing is really getting updated.  I am not sure if I need a second map after the "affectedOpps".  Actually, at this point I  have tried so many different combinations, I have no idea what I need.

 

Here's my code as it currently is where I was trying to get the opportunity id and update the stageName field.

 

Trigger CheckOpportunityWorkorders on Work_Order__c (after insert, after update) {

   set<id> oppids = new set<id>();
  
   list <Opportunity> opportunity = [SELECT ID FROM Opportunity WHERE ID in:oppIDs];
   
   	
   		    if(Trigger.isInsert || Trigger.isUpdate){
   		    	for (Work_Order__c wo: Trigger.new) {
            if (wo.stage__c == 'Work Order Closed' ||  wo.stage__c == 'Submitted' ){
      		oppids.add(wo.Opportunity__c);
   }
   		    }
   		    }

  Map <ID, Opportunity> affectedOpps = new Map <ID, Opportunity>([select id, stageName, (select id, Opportunity__c from Work_Orders__r 
  where stage__c != 'Work Order Closed' OR stage__c != 'Job Completed') 
	from Opportunity where id in :oppids]);
	  	for (Opportunity o : affectedOpps){
  	  	
      if (affectedOpps(o.id).Work_Orders__r.size()==0){
      	
   
      o.stageName = 'Job Complete';
       update affectedOpps;
      	}
	
      
  	}
}

 After the "if statement" I have tried to put another Map in but I cannot get it to save. and get the error:  IN operator must be used in iterable expression.   Without it, I get the error "DML requires SObject or SObject list type MAP <ID, Opportunity>.  Please Help!

 

Here's how I did the code with the 2nd MAP:

Trigger CheckOpportunityWorkorders on Work_Order__c (after insert, after update) {

   set<id> oppids = new set<id>();
  
   list <Opportunity> opportunity = [SELECT ID FROM Opportunity WHERE ID in:oppIDs];
   
   	
   		    if(Trigger.isInsert || Trigger.isUpdate){
   		    	for (Work_Order__c wo: Trigger.new) {
            if (wo.stage__c == 'Work Order Closed' ||  wo.stage__c == 'Submitted' ){
      		oppids.add(wo.Opportunity__c);
   }
   		    }
   		    }

  Map <ID, Opportunity> affectedOpps = new Map <ID, Opportunity>([select id, stageName, (select id, Opportunity__c from Work_Orders__r 
  where stage__c != 'Work Order Closed' OR stage__c != 'Job Completed') 
	from Opportunity where id in :oppids]);
	  	for (Opportunity o : affectedOpps){
  	  	
      if (affectedOpps(o.id).Work_Orders__r.size()==0){
      	
 	MAP <ID, Opportunity> updateOps = new MAP <ID, Opportunity>([Select id, stageName from Opportunity WHERE id in :affectedOpps]);
 	
 //     	[SELECT ID, stageName FROM Opportunity];{
  
      o.stageName = 'Job Complete';
       	
				update updateOps;
      	}
	
      
  	}
}

 
aKallNVaKallNV

Curious to know if you ever solved java.lang.ArithmeticException: Division undefined. I'm struggling with it.

 

Thanks,

Andy