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
Baguiar2Baguiar2 

SUM of a roll-up summary field on child accounts

Hi there,

 

I working on have a field in the parent account, that pretty much should be a SUM of all the Children's field named "Healthy_CMRR__C". I know it should be a standard functionality in SF to be able to do roll-up summary on child accounts, but as we know it is noet and I'm trying a work around . What I did was to create a field in the account called Healthy_CMRR_Child__c . Another field called Total_Healthy_CMRR__c is a formula field taht adds  Healthy_CMRR__C and Healthy_CMRR_Child__c. Healthy_CMRR_Child__c is a currency field that my trigger will update, based on the opportunities with a criteria. Here is the code:

 

trigger HealthyRenewalCMRR on Account (after update) {
  Set<Id> recordIds = new Set<Id>();
  Set<Id> parentIds = new Set<Id>();
  Set<Id> acctownerIds = new Set<Id>();
  map<Id,decimal> healthc = new Map<Id,decimal>();
  
for(account t:Trigger.new)
   if(t.parentid <> null ){
      recordIds.add(t.id);
      ParentIds.add(t.parentid);
       healthc.put(t.id,t.Healthy_CMRR__c);
     
  List < account> acctparent = [select id, Healthy_CMRR_Child__c, limit 1];
  
      IF(acctparent.size() > 0){
      
           If ( acctparent[0].Healthy_CMRR_Child__c != null){
               acctparent[0].Healthy_CMRR_Child__c = acctparent[0].Healthy_CMRR_Child__c + healthc.put(t.id,t.Healthy_CMRR__c);
               update acctparent;
           } ELSE IF (acctparent[0].Healthy_CMRR_Child__c == null){
                acctparent[0].Healthy_CMRR_Child__c =  healthc.put(t.id,t.Healthy_CMRR__c);
                 update acctparent;
                 }
}
}

 The issue is that anytime the acct is updated, it keeps adding over and over the healthy__CMRR__C value, although the value didn;t change. I know it is doing exaclty what the trigger is saying, but wonder if someone would ahve a better idea on how can I sum the values of a field of a Child record in SF.

 

Thanks a lot!

B

 


Best Answer chosen by Admin (Salesforce Developers) 
Baguiar2Baguiar2

Fixed it. A little research of course.. ;) Was trying to add an object to a decimal. Have to get the decimal fro the object and then insert into the field. working good now.

 

LIST<AggregateResult> ChildCMRR = [select sum(Total_Healthy_CMRR__c)childsum from Account where parentid in :ParentIds]; 
System.debug('ChildCMRR: ' + ChildCMRR);
Decimal D = (Decimal) ChildCMRR[0].get('childsum');
acctparent[0].Healthy_CMRR_Child__c = D;

 Thanks a lot Starz and BritishBoyin !!

All Answers

BritishBoyinDCBritishBoyinDC

I'd consider using SOQL Aggregration Queries for this - after insert/update/delete of the child object, put all the parent accounts in a set, and then run an aggregration against the child object, filtered for the Account Ids, and group by the Account Id...

 

I would then build a map of Account Id to Total, which starts off with a total of zero for each account (so it gets reset to 0 if all child records are deleted, so returns no results in the aggregration), then loop through the aggregation results, and for each account, set the total field in the map equal to the results in the aggregation

 

Finally, for each Account, add the records to a list, setting the total to the value in the map...

 

 
Starz26Starz26

That is a good option by the previous poster....

 

Here is another twist

 

trigger HealthyRenewalCMRR on Account (after update) {
 
Map<ID, Account> mParent = New Map<ID,Account>([Select ID, Healthy_CMRR_Child__c From Account where ID IN (Select ParentID From Account Where ID in :trigger.new)]);
Account[] tbuParent = New Account[]{};

for(account a :trigger.new){

	if(a.Healthy_CMRR__c != trigger.oldMap.get(a.id).Healthy_CMRR__C){
		if(trigger.oldMap.get(a.id).Healthy_CMRR__c != Null && mParent.containsKey(a.ParentID)){
			mParent.get(a.ParentID).Healthy_CMRR_child__c +=  a.Healthy_CMRR__c - trigger.oldMap.get(a.id).Healthy_CMRR__c;
			tbuParent.add(m.Parent.get(a.ParentID);
		}
	}

}
 
if(!tbuParent.isEmpty())
	update tbuParent;
 
}

 
Baguiar2Baguiar2

Thanks guys! Going by what you've suggested and I'm getting an error on the trigger that The inner and outer selects should not be on the same object . 

LIke:  Select ID, Healthy_CMRR_Child__c From Account where ID IN (Select ParentID From Account Where ID in :trigger.new) cannot be as it is referencing to the account twice.

 

I'm trying through these lines:

 

Decimal ChildCMRR = [select sum(Total Healthy CMRR__c) from Account where parentid in :recordIds];
acctparent[0].Healthy_CMRR_Child__c = ChildCMRR;
update acctparent;

 

Can't I query the sabe object on the same SOQL ?

 

Thanks a milion guys!

B


 

 
Starz26Starz26

lol, yea....

 

Sholda saw that..

 

try:

ap<ID, Account> mParent = New Map<ID,Account>([Select ID, Healthy_CMRR_Child__c From Account where ParentID IN :trigger.new)]);
Baguiar2Baguiar2

makes a lot more sense now! lol

anyways, tried that and it passed, but I'm not seeing any changes at all on the Healthy_CMRR_child__C field in the parent. By what I can see on the code, you are subtracting the value from the old CMRR (trigger.old) from the new one right ? don;t think that would work if we want to add increments as new values for Heatlthy_CMRR_Child__c are added. again, not sure..

 

I thought of something , maybe too simple like this code below. The Idea is agregate the sum of all the Total_healthy_CMRR__c from all the children accounts from a parent and insert the value of the SUM into the Healthy_CMRR_child__C in the parent. So, the Total_healthy_CMRR__c on the account is the Healthy_CMRR_child__C + Healthy_CMRR__C (a roll-up summary of opportunities related to the account).

 

Since this would only update the parent account, I'd be ok with that because accounts with no parents or children, will have only the HEalthy_CMRR__C . My issue now is that I can get the aggregate result to the Heatlhy_CMRR_CHild__C field as a "decimal". I'm getting the error "Illegal assignment from Object to Decimal at line 18 column 20". But the Heatlhy_CMRR_CHild__C is deciaml and also the Total_healthy_CMRR__c. 

trigger HealthyRenewalCMRR 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);
       
       List < account> acctparent = [select id, Healthy_CMRR_Child__c from account where id in :ParentIds limit 1]; 

     //will give the total HEalthy CMRR of All children
     LIST<AggregateResult> ChildCMRR = [select sum(Total_Healthy_CMRR__c)childsum from Account where parentid in :ParentIds]; 
     System.debug('ChildCMRR: ' + ChildCMRR);
     acctparent[0].Healthy_CMRR_Child__c = ChildCMRR[0].get('childsum');
     update acctparent;
    }
}

 Thanks !

 
Baguiar2Baguiar2

Fixed it. A little research of course.. ;) Was trying to add an object to a decimal. Have to get the decimal fro the object and then insert into the field. working good now.

 

LIST<AggregateResult> ChildCMRR = [select sum(Total_Healthy_CMRR__c)childsum from Account where parentid in :ParentIds]; 
System.debug('ChildCMRR: ' + ChildCMRR);
Decimal D = (Decimal) ChildCMRR[0].get('childsum');
acctparent[0].Healthy_CMRR_Child__c = D;

 Thanks a lot Starz and BritishBoyin !!

This was selected as the best answer