+ Start a Discussion
bozotheclownbozotheclown 

Inserting Agr Value in Related Object

Hello.  I posted a related note a few days ago but am still running into some issues with my problem - so I was curious to see if anyone had any further suggestioins.

 

In short, I am trying to use Apex code to sum the number of customer work order records (in custom object Work_Orders__c) for each customer that are in the initial stage - and then update a specific field (WO_Total__c) in the customer object (Customer__c) with this value.  Due to how this application was developed, I am not able to accomplish this via a rollup field...so I have to use Apex.

 

The below code is not running into any syntax errors...but the WO_Total__c field in the customer object is still not being updated with any value.

 

Any thoughts would be greatly appreciated.  Thanks.

 

 

trigger CountWOStatus on Work_Orders__c (after insert) {

 

    Map <String, Integer> WOCount = new Map <String, Integer> ();

 

    id WOid = null;
    Integer nbr = null;

 

list<id> ListOfWO = new list<id>();
for (Work_Orders__c WOs: Trigger.new){
if (WOs.Customer__r.Name != null)
ListOfWO.add(WOs.Customer__r.Name);
}

 

    for (AggregateResult agr: [SELECT  Customer__r.Name, count(Id) numOfWO FROM Work_Orders__c
        WHERE Customer__r.Name in :ListOfWO
 AND (CurrWOStatus__c = 'Initial Stage')
        GROUP BY Customer__r.Name]){


       WOid = string.valueOf(agr.get(' Customer__r.Name '));
       nbr = integer.valueOf(agr.get('numOfWO'));
       WOCount.put (WOid, nbr);
       }
  

    try {
       for (Work_Orders__c IndivWOUpdate : Trigger.new){
       if (Integer.valueOf(WOCount.get(IndivWOUpdate.Id)) != null){
       IndivWOUpdate.Customer__r.WO_Total__c = Integer.valueOf(WOCount.get(IndivWOUpdate.Id));
}
       } // end for IndivWOUpdate
     }// end try

 
    catch (System.Queryexception e){
       System.debug('WOItemCount ERROR:' + e);

    } // end catch

}

BritishBoyinDCBritishBoyinDC

I don't think this is going to work - the trigger can only update it's own object, not a related object:

 

 

   IndivWOUpdate.Customer__r.WO_Total__c = Integer.valueOf(WOCount.get(IndivWOUpdate.Id));

 

You need to create a sObject list for the Customer__c object,  and use that final loop to add each Customer and their WO total to that list, and then execute an update for that list...

 

 

TrueCloudTrueCloud

 


BritishBoyinDC wrote:

I don't think this is going to work - the trigger can only update it's own object, not a related object:

 

 

   IndivWOUpdate.Customer__r.WO_Total__c = Integer.valueOf(WOCount.get(IndivWOUpdate.Id));

 

You need to create a sObject list for the Customer__c object,  and use that final loop to add each Customer and their WO total to that list, and then execute an update for that list...

 

 


 

BritishBoyinDC, this is not true. You can update not only its own object and related objects but any objects that are even not related. The apex trigger fire from a specific object on specific actions such as before or after for (insert, update, delete, undelete. 
Now back to the issue that you friend bozotheclown is facing with his trigger. My friend you are writing a trigger that is working too hard to achieve a simple solution. I see you are doing a fair job of optimizing the trigger for bulkification but do you need to consider
a) What happens with the customer name changes?
b) What happens when the record is updated?
c) What happens when the WO is deleted from the system?
Caution 1:
One of the pit falls that I see with this trigger is that, when the WO is updated from Initial Stage to something else, your field on Customer will not be updated, showing the wrong number of WO numbers. The number of initial WO's will be corrected only when a new Work Order is created. I don't think that would be a good idea to do so. 
Caution 2: 
Another pitfall that is see with this trigger is that it is trying to sum a lot of records. and you are running the select statement in the for loop that may hit to many scripts limit if not too many SOQL query limit.
Caution 3:
I would strongly recommend using the Apex Batch method whenever you are summing up all records that are fired on a trigger. Using batch apex you will be able to sum the total number of records for the Customer without worrying too much about the governor limit.
Now after all the long caution and advise let me ask you one quick question. Do you anticipate to create multiple WO's through integration or any other method?
If these WO's are created by users than instead of summing the WO's everytime can you update the Customer record by either adding +1 or -1 everytime a new WO is created or modified and doesn't meet the criteria. 
Let me give you a sample code for both methods and I will let you choose the best method:
Now please accept this as a solution if you find this works for you :).

 

BritishBoyinDC, this is not true. You can update not only its own object and related objects but any objects that are even not related. The apex trigger fire from a specific object on specific actions such as before or after for (insert, update, delete, undelete. 
Now back to the issue that you friend bozotheclown is facing with his trigger. My friend you are writing a trigger that is working too hard to achieve a simple solution. I see you are doing a fair job of optimizing the trigger for bulkification but do you need to consider the following

 

a) What happens with the customer name changes?

b) What happens when the record is updated?

c) What happens when the WO is deleted from the system?


Caution 1:One of the pit falls that I see with this trigger is that, when the WO is updated from Initial Stage to something else, your field on Customer will not be updated, showing the wrong number of WO numbers. The number of initial WO's will be corrected only when a new Work Order is created. I don't think that would be a good idea to do so. 

 

Caution 2: Another pitfall that is see with this trigger is that it is trying to sum a lot of records. and you are running the select statement in the for loop that may hit to many scripts limit if not too many SOQL query limit.

 

Caution 3:I would strongly recommend using the Apex Batch method whenever you are summing up all records that are fired on a trigger. Using batch apex you will be able to sum the total number of records for the Customer without worrying too much about the governor limit.

 

Now after all the long caution and advise let me ask you one quick question. Do you anticipate to create multiple WO's through integration or any other method?

 

If these WO's are created by users than instead of summing the WO's everytime can you update the Customer record by either adding +1 or -1 everytime a new WO is created or modified and doesn't meet the criteria. 

 

Let me give you a sample code for the first method to add and subtract total WO's and see if that works for you. If you think you still need to sum all WO's then we can work through that next. 

 

 

/*

Created by: Rupesh Desai
Date: 09/22/2010

*/

trigger CountWOStatus on Work_Orders__c (after insert, after update) {

Set<Id> customerID = new Set<Id>(); //Initailize a set for adding customer ids

	for (Work_Orders__c wo : Trigger.new) {
		if (wo.Customer_Name__c != null) {		
			customerID.add(wo.Customer_Name__c);
		}
	} // All Customer Ids have been addded
	
	List<Customer__c> custUpdate = [SELECT Id, WO_Total__c from Customer__c where Id IN : customerID]; // Create a List of Customer Records to be updated.

/*=======================================================================================================================================
If the Trigger isInsert then check if the Work Order is on Inital Stage and simply add one to the list.
========================================================================================================================================*/

	if (Trigger.isInsert) { 
		for (Work_Orders__c wo : Trigger.new) {
			
			for (integer i=0; i < custUpdate.size(); i++) {
				
				if ((wo.Stage__c == 'Initial Stage') & (wo.Customer_Name__c == custUpdate[i].Id)) {
					custUpdate[i].WO_Total__c += 1;
				}
			}
		}
	}
/*=======================================================================================================================================
If the Trigger isUpdate then check if the Work Order is on Inital Stage then either add or subtract it to the list.
========================================================================================================================================*/
	if (Trigger.isUpdate) {
		
		for (Work_Orders__c wo : Trigger.new) {
		
			Work_Orders__c beforeUpdate = System.Trigger.oldMap.get(wo.Id);
			
			for (integer i=0; i < custUpdate.size(); i++) {
			
				if ((beforeUpdate.Stage__c == 'Initial Stage') & (beforeUpdate.Stage__c == wo.Stage__c)) {
					if (wo.Customer_Name__c == custUpdate[i].Id) {
						custUpdate[i].WO_Total__c += 1;
					}
				}
				else if ((beforeUpdate.Stage__c == 'Initial Stage') & (beforeUpdate.Stage__c != wo.Stage__c)) {
					if (wo.Customer_Name__c == custUpdate[i].Id) {
						custUpdate[i].WO_Total__c -= 1;
					}
				}
			} // End of for Loop
		}
	}
	update custUpdate;
}

 

 

Please select this post as a solution if this resolves the issue.

 

Note: WO_Total__c field should never be left blank. You should set the default value in the field setting to "zero." For existing records, you should set the default value to zero by exporting and re-importing the customer records using the Apex Data Loader.

 

This trigger will work only for new implementation. if you are going to apply this for existing implementation with data in the Salesforce.com already I would recommend the following steps. Since this only fires when the record is inserted or updated, the numbers will not be correct.

 

Assumption: You have already deployed this to production:

 

Step 1: Create a temporary field "Update" on the Work_Orders__c object. Make it a number field.

 

Step 2: Using the Apex Data Loaders first export Customer__c with the following fields (Id, WO_Total__c)

 

Step 3: Update the WO_Totals__c in the csv file to zero for all records.

 

Step 4: Using the Apex DataLoader update all Customer__c object.

 

Step 5: Export Work_Orders__c with the following fields (Id, Update__c) using the data loader

 

Step 6: Update the field "Update__c" to a value of 500(You can choose any number) the idea is to update the records to run the trigger.

 

Step 7: Using Apex DataLoader update all Work_Orders__c records.

 

Hope this helps.....!!!!

 

BritishBoyinDCBritishBoyinDC

I'm pretty sure that an after insert trigger on Work Orders can't update a Customer without an explicit Update call....?

 

The point about the SOQL query Limits is a valid one, and using the Name does seem more complex than just using the lookup reference.

 

But Batch Apex also seems like an overkill unless the number of WO's in Initial Stage is going to break the limits. If it is not, this code seems to work  for when a new order is inserted, an existing order is updated, or an order is deleted.

 

 

trigger CountWOStatus on Work_Orders__c (after insert, after update, after delete) {

Map<id, Integer> ListOfWO = new Map<id, Integer>();

if (Trigger.isInsert || Trigger.IsUpdate) {
for (Work_Orders__c WOs: Trigger.new){
if (WOs.Customer__c != null)
ListOfWO.put(WOs.Customer__c,0);
}
}

if (Trigger.isDelete) {
for (Work_Orders__c WOs: Trigger.old){
if (WOs.Customer__c != null)
ListOfWO.put(WOs.Customer__c,0);
}
}
 
    for (AggregateResult agr: [SELECT  Customer__c, count(Id) numOfWO FROM Work_Orders__c 
    WHERE Customer__c in :ListOfWO.keyset() AND CurrWOStatus__c = 'Initial Stage' GROUP BY Customer__c]){
       
        ListOfWO.put((Id)agr.get('Customer__c'),integer.valueOf(agr.get('numOfWO')));
        }
  
List<Customer__c> cupdates = new List<Customer__c>();

for (Id cmap : ListOfWO.keySet()) {
cupdates.add(new Customer__c (Id = cmap,WO_Total__c = ListOfWO.get(cmap)));
}

  
try {update cupdates;}
    
catch (System.Queryexception e){
System.debug('WOItemCount ERROR:' + e);
} // end catch
}

 

 

 

 

TrueCloudTrueCloud

BritishBoyinDC I missed the "Update Call" part. You are correct, that is required.

 

The only reason I brought up batch Apex was because most of the time people have integration with Transactional systems which usually deals with lot of records.

 

Either of the suggestions would suffice the needs. Sorry if I came to harsh on saying, "that's not true."

 

Thanks for your input.

TrueCloudTrueCloud

Did any of the solutions work for you?

bozotheclownbozotheclown

FIrst of all, my apologies for replying incredibly late.  I unexpectedly had to take a few days off and am just reviewing what I missed.

 

Thank you all so much for the help. I this last piece of code works.

 

One question/confirmation.  Is the delete trigger being used to address situations where WO records are deleted (this might be an obvious question...but I am still new to Apex so I just want to be clear)?

 

Again, thanks.

BritishBoyinDCBritishBoyinDC

That's the idea - it runs on After Delete, so should recalculate based on the post deletion number of records...