function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
pmozz01pmozz01 

Trigger Using Aggregate Function - Not Updating; not sure what I am doing wrong

Not sure what I am doing wrong here.  This is my first attempt of Aggregate functions.  I have a custom Work Order that contains a lookup relationship to an opportunity.  There can be multiple work orders on a single opportunity.  I need specific $ value fields on the work order to be summed and updated on the opportunity.  I cannot get any update to occur at all.  The test runs fine and I have 100% coverage, so I must be writing this wrong :(

 

Here is my trigger:

 

 

trigger triggerWorkorderOppUpdate on Work_Order__c (after insert, after update, after delete) {

    Set<String> oppIDs = new Set<String>();

    //************************************************
    // Build a LIST of Opportunity ID's that will
    // need recalculating
    //************************************************
    if(Trigger.isInsert || Trigger.isUpdate){
        for(Work_Order__c w : trigger.new){
            if(w.Opportunity__c != null){
                if(!oppIDs.contains(w.Opportunity__c)) 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){
                if(!oppIDs.contains(w.Opportunity__c)) oppIDs.add(w.Opportunity__c);
            }
        }
    }  // DELETE/UPDATE Trigger

    if(oppIDs .size() > 0) {

        Map<ID, Opportunity> opps = new Map<ID, Opportunity>([
        	Select o.id, o.Total_Travel_Costs__c, o.Total_Out_of_Scope__c,o.Total_Hourly_Labor__c,
		 o.Total_Flat_Rate__c, o.Total_Materials__c
		 FROM Opportunity o
		 WHERE ID IN :oppIDs]);
        Opportunity d = null;

        for (AggregateResult dr : [SELECT Opportunity__c, SUM(Total_Travel_Costs__c) Travel, SUM(Total_Out_of_Scope__c)Oscope,
		SUM(Total_Hourly_Labor__c)Labor, SUM(Total_Flat_Rate__c) Flat, SUM(Total_Materials__c)Materials
        FROM Work_Order__c GROUP BY Opportunity__c]) {

            String dID = (string)dr.Get('Opportunity__c');
            if (opps.get(dID) != null)

               d = opps.get(dID);

            // update the total fields
            Decimal trv = (Decimal)dr.Get('Travel');
            Decimal scp = (Decimal)dr.Get('Oscope');
            Decimal lbr = (Decimal)dr.Get('Labor');
            Decimal flt = (Decimal)dr.Get('Flat');
            Decimal mat = (Decimal)dr.Get('Materials');
        }
	LIST<Opportunity> OppUpdate = new LIST<Opportunity>();
				for (Opportunity ou:opps.values()) {				
				
			d.Total_Travel_Costs__c = opps.get(ou.id).Total_Travel_Costs__c;
			d.Total_Out_of_Scope__c = opps.get(ou.id).Total_Out_of_Scope__c;
			d.Total_Hourly_Labor__c = opps.get(ou.id).Total_Hourly_Labor__c;	
			d.Total_Flat_Rate__c = opps.get(ou.id).Total_Flat_Rate__c;
			d.Total_Materials__c = opps.get(ou.id).Total_Materials__c;
						
        }

        //commit the changes to Salesforce
        update opps.values();

    }

}

 

 

Here is my test:

 

 

public with sharing class testTriggerWorkorderOppUpdate {



    static testMethod void myTest() {

       //Insert a test opportunity

       Opportunity test_opportunity = new Opportunity(StageName='Target',Name='TestOp', closeDate=Date.today());

       insert test_opportunity;
       
       //Insert a Work_Order

       Work_Order__c wo = new Work_Order__c();

       wo.Opportunity__c = test_opportunity.id;
       
       wo.Close_Date_WO__c = Date.today();

       wo.Travel_Cost_Total__c = 55.00;

       wo.Out_of_Scope_Total__c = 10.00;

  //     wo.Total_Hourly_Labor__c = '2.00';
       
  //     wo.Total_Flat_Rate__c = '0.00';

       insert wo;

       //Insert an Invoice with Line Items
       
       Tech_Invoice__c inv = new Tech_Invoice__c();
       inv.Invoice_Number__c = 'ABC123';
       inv.Work_Order__c = wo.id;
       
       insert inv;
       
       Invoice_Line_Item__c lih = new Invoice_Line_Item__c();
       lih.Contractor_Invoice__c = inv.id;
       lih.type__c = 'Hourly Labor';
       lih.Item_Name__c = 'item 1';
       lih.Quantity__c = 20;
       lih.unitprice__c = 5.00;
       insert lih;
       
      
       Invoice_Line_Item__c lim = new Invoice_Line_Item__c();
       lim.Contractor_Invoice__c = inv.id;
       lim.type__c = 'Materials';
       lim.Item_Name__c = 'item 2';
       lim.Quantity__c = 5;
       lim.unitprice__c = 3.00;
       insert lim;
        
     
       Invoice_Line_Item__c lit = new Invoice_Line_Item__c();
       lit.Contractor_Invoice__c = inv.id;
       lit.type__c = 'Travel';
       lit.Item_Name__c = 'item 3';
       lit.Quantity__c = .75;
       lit.unitprice__c = .32;
       insert lit;
     

       Opportunity updated_Opportunity = [SELECT ID, Total_Travel_Costs__c, Total_Out_of_Scope__c,
		Total_Hourly_Labor__c, Total_Flat_Rate__c FROM Opportunity WHERE Id = :test_opportunity.Id];

       //Verify that the values of the contact were changed by the trigger
 
     }

 }

 

Any assistance is greatly appreciated.

 

SuperfellSuperfell

You seem to read the aggregate values from the query, but then do nothing with them.

 

Also, you could probably just do this with a roll-up summary field, and not have to write any code at all.

pmozz01pmozz01

Thanks, I suspected I wasn't doing anything with the totals; no wonder my test worked so well.  I cannot use a roll-up summary since this is not a master-detail relationship. 

 

I have attempted the code differently, but now I just get an error that says "MISSING_ARGUMENT, Id not specified in an update call: []: Trigger.triggerWorkorderOppUpdate: line 68, column 9".  Line 68 is the last line that says to update opps.values();

 

Here is my new code:

 

 

trigger triggerWorkorderOppUpdate on Work_Order__c (after insert, after update, after delete) {

    Set<String> oppIDs = new Set<String>();

    //************************************************
    // Build a LIST of Opportunity ID's that will
    // need recalculating
    //************************************************
    if(Trigger.isInsert || Trigger.isUpdate){
        for(Work_Order__c w : trigger.new){
            if(w.Opportunity__c != null){
                if(!oppIDs.contains(w.Opportunity__c)) 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){
                if(!oppIDs.contains(w.Opportunity__c)) oppIDs.add(w.Opportunity__c);
            }
        }
    }  // DELETE/UPDATE Trigger

    if(oppIDs .size() > 0) {

        Map<ID, Opportunity> opps = new Map<ID, Opportunity>([
        	Select o.id, o.Total_Travel_Costs__c, o.Total_Out_of_Scope__c,o.Total_Hourly_Labor__c,
		 o.Total_Flat_Rate__c, o.Total_Materials__c
		 FROM Opportunity o
		 WHERE ID IN :oppIDs]);
        Opportunity d = null;

        for (AggregateResult dr : [SELECT Opportunity__c, SUM(Total_Travel_Costs__c) Travel, SUM(Total_Out_of_Scope__c)Oscope,
		SUM(Total_Hourly_Labor__c)Labor, SUM(Total_Flat_Rate__c) Flat, SUM(Total_Materials__c)Materials
        FROM Work_Order__c GROUP BY Opportunity__c]) {

            String dID = (string)dr.Get('Opportunity__c');
            if (opps.get(dID) == null)
            d = new Opportunity(ID = dID,
            	Total_Travel_Costs__c = 0,
        		Total_Out_of_Scope__c = 0,
        		Total_Hourly_Labor__c = 0,
        		Total_Flat_Rate__c = 0,
        		Total_Materials__c = 0);
        		
        	else

               d = opps.get(dID);

            // update the total fields
            Decimal trv = (Decimal)dr.Get('Travel');
            Decimal scp = (Decimal)dr.Get('Oscope');
            Decimal lbr = (Decimal)dr.Get('Labor');
            Decimal flt = (Decimal)dr.Get('Flat');
            Decimal mat = (Decimal)dr.Get('Materials');
        
        	d.Total_Travel_Costs__c = trv;
        	d.Total_Out_of_Scope__c = scp;
        	d.Total_Hourly_Labor__c = lbr;
        	d.Total_Flat_Rate__c = flt;
        	d.Total_Materials__c = mat;
        	
        	opps.put(dID, d);
        }
        //commit the changes to Salesforce
        
        update opps.values();

    }

}