+ Start a Discussion
Baguiar2Baguiar2 

Total SUM on child records for Account

Hi there,

 

this one seems strange, but I want to check first before saying something that is out of my hands. :) I have this trigger that calculates the CMRR amounts and updates the parent account. The goal is to always have the Top parent account updated with the SUM of the CMRR fields (05 of them) from the child account records. 

 

So, whenever an account is updated, the trigger finds a parent  account (if any) and all other children related to the same parent and updates the Parent with the sum Of all children CMRR fields (05 diferent types of CMRR). Well, this works beautifully when I do update records manually. Tested them many many times and the results are perfectly using, child, grandchild records and so on. Tested in som amny diferent ways and always worked fine. What is happening is that (Ibeleive) we have data.com massively updating our accounts with the most current data as far as web addresses, phone numbers and such. After those updates, the CMRR numbers on the accounts go crazy. Things like from $0 to $2,550,356.00 CMRR. IMpossible as the parent and children have NO CMRR at all. they are at 0.    Here is the code:

 

trigger TotalsCMRR on Account (after update) {
  Set<Id> recordIds = new Set<Id>();
  Set<Id> parentIds = new Set<Id>();
  
  for(account t:Trigger.new)
   if(t.parentid <> null ){
      recordIds.add(t.id);
      ParentIds.add(t.parentid);
       
     List < account> acctparent = [select id, ChildOR__c, ChildNew__C, Healthy_CMRR_Child__c, Healthy_Upsell_CMRR_Child__c, Churned_CMRR_Child__c, New_Business_CMRR_Child__c, Retention_Risk_CMRR_Child__c from account where id in :ParentIds limit 1]; 

     
     LIST<AggregateResult> ChildCMRR = [select sum(Total_Healthy_CMRR__c)childsum, sum(Total_Churned_CMRR__c)childchurn, sum(Total_Healthy_Upsell_CMRR__c)childupsell, sum(Total_Open_New_Business_CMRR__c)childnew, sum(Total_Retention_Risk_CMRR__c)childret from Account where parentid in :ParentIds]; 
     System.debug('ChildCMRR: ' + ChildCMRR);
     
     Decimal D = (Decimal) ChildCMRR[0].get('childsum');
     acctparent[0].Healthy_CMRR_Child__c = D;
     Decimal D2 = (Decimal) ChildCMRR[0].get('childchurn');
     acctparent[0].Churned_CMRR_Child__c = D2;
     Decimal D3 = (Decimal) ChildCMRR[0].get('childupsell');
     acctparent[0].Healthy_Upsell_CMRR_Child__c = D3;
     Decimal D4 = (Decimal) ChildCMRR[0].get('childnew');
     acctparent[0].New_Business_CMRR_Child__c = D4;
     Decimal D5 = (Decimal) ChildCMRR[0].get('childret');
     acctparent[0].Retention_Risk_CMRR_Child__c = D5;
     update acctparent;
     
     IF (D > 0){
     acctparent[0].ChildOR__c = True;
    }
     IF (D == 0){
     acctparent[0].ChildOR__c = False;
     }
     IF (D4 > 0){
     acctparent[0].ChildNew__c = true;
    }
      IF (D4 == 0){
     acctparent[0].ChildNew__c = false;
    }
    update acctparent;
   }
}

 

Just as an example, on the same account (or any other account updated during the mass data.com updates) that I had the Total CMRR changed from 0 to $2,550,350.00 , if I just "touch" a child record (add a description or a phone number)for the same parent account and save it, the CMRR goes back to Normal on the parent. I beleive this tells me that the trigger works fine.??..

 

any thoughts? AS usual, thanks for the great help !

B

Best Answer chosen by Admin (Salesforce Developers) 
Jerun JoseJerun Jose

Ooh.

 

Small mistake with the aggreate SOQL

 

You need to group the ParentID field on your query.

LIST<AggregateResult> ChildCMRRList = [select parentid ParentID, sum(Total_Healthy_CMRR__c)childsum, sum(Total_Churned_CMRR__c)childchurn, sum(Total_Healthy_Upsell_CMRR__c)childupsell, sum(Total_Open_New_Business_CMRR__c)childnew, sum(Total_Retention_Risk_CMRR__c)childret from Account where parentid in :ParentIds group by ParentID]; 

 

All Answers

Jerun JoseJerun Jose

You have the classic problem of bulkifying your trigger.

 

All apex triggers run in bulk mode. i.e. when there is bulk data inputs, one trigger execution works for 200 records at a time. You will need to design the trigger to handle 200 records in the trigger.new variable.

 

trigger TotalsCMRR on Account (after update) {
	Set<Id> recordIds = new Set<Id>();
	Set<Id> parentIds = new Set<Id>();
  
	for(account t:Trigger.new)
		if(t.parentid <> null ){
			recordIds.add(t.id);
			ParentIds.add(t.parentid);
		}

	List <account> acctparent = [select id, ChildOR__c, ChildNew__C, Healthy_CMRR_Child__c, Healthy_Upsell_CMRR_Child__c, Churned_CMRR_Child__c, New_Business_CMRR_Child__c, Retention_Risk_CMRR_Child__c from account where id in :ParentIds]; 

	LIST<AggregateResult> ChildCMRRList = [select parentid ParentID, sum(Total_Healthy_CMRR__c)childsum, sum(Total_Churned_CMRR__c)childchurn, sum(Total_Healthy_Upsell_CMRR__c)childupsell, sum(Total_Open_New_Business_CMRR__c)childnew, sum(Total_Retention_Risk_CMRR__c)childret from Account where parentid in :ParentIds]; 
	System.debug('ChildCMRR: ' + ChildCMRR);
     
	list<Account> AccToUpdate = new list<Account>();
	for(AggregateResult ChildCMRR : ChildCMRRList){
		ID AccountID = (ID) ChildCMRR.get('ParentID');
		Account acc = new Account(ID = AccountID);
		Decimal D = (Decimal) ChildCMRR.get('childsum');
		acc.Healthy_CMRR_Child__c = D;
		IF (D > 0){
			acc.ChildOR__c = True;
		}
		IF (D == 0){
			acc.ChildOR__c = False;
		}
		Decimal D2 = (Decimal) ChildCMRR.get('childchurn');
		acc.Churned_CMRR_Child__c = D2;
		Decimal D3 = (Decimal) ChildCMRR.get('childupsell');
		acc.Healthy_Upsell_CMRR_Child__c = D3;
		Decimal D4 = (Decimal) ChildCMRR.get('childnew');
		acc.New_Business_CMRR_Child__c = D4;
		IF (D4 > 0){
			acc.ChildNew__c = true;
		}
		IF (D4 == 0){
			acc.ChildNew__c = false;
		}
		Decimal D5 = (Decimal) ChildCMRR.get('childret');
		acc.Retention_Risk_CMRR_Child__c = D5;

		AccToUpdate.add(acc);
	}
	if(!AccToUpdate.isEmpty())
		update AccToUpdate;
}

 A good way to test bulk updates is to try the data loader and update 20 records or so. You can then check how well your trigger is working.

Baguiar2Baguiar2

Thanks Jerun! Totally got the part of bulkifying. NOw, the code you posted gave me an error as a List <agreggateresult> cannot have a field in the query that is not  grouped or Sum. So, ParentID cannot be there.  Working here on a solution for that..

 

ANy help appreciated but really, thanks !

Jerun JoseJerun Jose

Ooh.

 

Small mistake with the aggreate SOQL

 

You need to group the ParentID field on your query.

LIST<AggregateResult> ChildCMRRList = [select parentid ParentID, sum(Total_Healthy_CMRR__c)childsum, sum(Total_Churned_CMRR__c)childchurn, sum(Total_Healthy_Upsell_CMRR__c)childupsell, sum(Total_Open_New_Business_CMRR__c)childnew, sum(Total_Retention_Risk_CMRR__c)childret from Account where parentid in :ParentIds group by ParentID]; 

 

This was selected as the best answer
Baguiar2Baguiar2

Awesome! This seems to be working jsut fine. Let me test with a bulk operation and will let u know..

 

THX!

Baguiar2Baguiar2

Was able to run a full test with a bullk update of accounts and it worked really well. Thanks Jerun!