+ Start a Discussion
Alex ValavanisAlex Valavanis 

Roll up child accounts to parent account

Hi, I'm pretty sure this question has been raised a couple of times ( :) ) but would appreciate if someone could give me a step by step quide on how to roll up child accounts to parent accounts.

I do have a roll up on accounts which sums up the amount of closed won opportunities (2015,2016,2017 price etc. etc. ) but how am i going to roll up the accounts to the parent account (Field "Total Net Amount") ?

P.S Apex code is chinese to me :) but willing to try if you give me guidance

User-added image
 
Best Answer chosen by Alex Valavanis
Roshni RahulRoshni Rahul
Hi Alex,

Can you be more specific on your req and the field names. Because what i understood is that you need to sum up the fields like 2015 price,2016 price,2017 price to a custom field named 'total_amount__c'  . Then you can use the below trigger. Here instead of the field 'child_amount__c' you can use your fields like 2015 price,2016 price,2017 price.
@Rajat you need to use parentid instead of ParentAccountId
trigger ParentAccUpdate on Account   (after insert,after update, after delete,after undelete) {
    if(checkRecursive.runOnce())
        
    {
        
        
        List<id> ParentIds = new List<id>();
        if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
            For(Account Acc: Trigger.new){
                ParentIds.add(Acc.parentid);
            }
        }
        if(Trigger.isDelete){
            For(Account Acc: Trigger.old){
                ParentIds.add(Acc.parentid);
            }
        }
        List<account> accountToUpdate = new List<account>();
        decimal sum = 0;
        if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate || trigger.isdelete){
            For(account q : [SELECT total_amount__c ,(SELECT id,child_amount__c FROM  ChildAccounts) FROM account WHERE id =: ParentIds]){ 
                sum = 0;
                for(Account p : q.ChildAccounts){
                    
                    sum = sum +p.child_amount__c;//Use the fields you want here to get the sum and include those                     fields in above query eg is given below from my understanding
                    /*               
sum1 = sum1 + p .2015_price__c; declare sum1 sum2 sum3 above in place of sum
sum2 = sum2 + p .2016_price__c;
sum3 = sum2 + p .2017_price__c;

q.total_2015_price__c = sum1;
q.total_2016_price__c = sum2;
q.total_2017_price__c = sum3;

*/
                    q.total_amount__c = sum;
                    
                }
                accountToUpdate .add(q);
            }
            try{
                update accountToUpdate ;
            }Catch(Exception e){
                System.debug('Exception :'+e.getMessage());
            }
        }
    }   
}



Hope it helps.Let me know if you have any issues.

Regards 
Roshni

All Answers

Roshni RahulRoshni Rahul
Hi Alex,
To get the Net total amount, create a field in account.
Then write trigger on account object. I had the same rquirement, and follow the code in below link.
https://developer.salesforce.com/forums/ForumsMain?id=9060G000000I4PsQAK

It worked for me.
Regards,
Roshni
rajat Maheshwari 6rajat Maheshwari 6

Hi Alex,

@Roshni appreciate your suggestion !!! but it will not hold the sum correctly when child account will delete.

To utilize rollup summary features completely, I will suggest this code : - 

trigger AccountRollupTrigger on Account (after insert,after update,after delete)
  {
     Set<Id> setParentAccId = new Set<Id>();


if(Trigger.isInsert || Trigger.isUpdate))
  {
       for(Account acc: Trigger.new)
         {
            if(acc.ParentAccountId!=null)
                   setParentAccId.add(acc.ParentAccountId);
         }
}

if(Trigger.isDelete)
    {
       for(Account acc_Old : Trigger.old)
           {
                if(acc_Old.ParentAccountId!=null)
                    setParentAccId.add(acc_Old.ParentAccountId);
            }
    }

Map<Id,Double> mp_IdDouble = new Map<Id,Double>();

for(AggregateResult agg: [Select ParentAccountId, SUM(TotalAmount) from Account where ParentAccountId IN : setParentAccId group by ParentAccountId ])
   {
       mp_IdDouble.put((Id)agg.get('ParentAccountId'),(Double)agg.get('expr0'));
   }

List<Account> ParentListUpdate = new List<Account>();

  for(Account acc : [Select Id, Total_Net_Amount__c from Account where Id IN :setParentAccId])
{
    Double TotalAmount= mp_IdDouble.get(acc.Id);
    acc.Total_Net_Amount__c = TotalAmount;
    ParentListUpdate .add(acc);
  }
 
if(ParentListUpdate !=null && ParentListUpdate.size()>0)
  update ParentListUpdate ;

}
 

Please let me know in case of any help. I am happy to help you :)

Note : - I am not saying that @Roshni code is wrong, but this code which I am suggesting is ideal for your use case.

Thanks

 

Roshni RahulRoshni Rahul
Hi Alex,

Can you be more specific on your req and the field names. Because what i understood is that you need to sum up the fields like 2015 price,2016 price,2017 price to a custom field named 'total_amount__c'  . Then you can use the below trigger. Here instead of the field 'child_amount__c' you can use your fields like 2015 price,2016 price,2017 price.
@Rajat you need to use parentid instead of ParentAccountId
trigger ParentAccUpdate on Account   (after insert,after update, after delete,after undelete) {
    if(checkRecursive.runOnce())
        
    {
        
        
        List<id> ParentIds = new List<id>();
        if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate){
            For(Account Acc: Trigger.new){
                ParentIds.add(Acc.parentid);
            }
        }
        if(Trigger.isDelete){
            For(Account Acc: Trigger.old){
                ParentIds.add(Acc.parentid);
            }
        }
        List<account> accountToUpdate = new List<account>();
        decimal sum = 0;
        if(Trigger.isInsert || Trigger.isUndelete || Trigger.isupdate || trigger.isdelete){
            For(account q : [SELECT total_amount__c ,(SELECT id,child_amount__c FROM  ChildAccounts) FROM account WHERE id =: ParentIds]){ 
                sum = 0;
                for(Account p : q.ChildAccounts){
                    
                    sum = sum +p.child_amount__c;//Use the fields you want here to get the sum and include those                     fields in above query eg is given below from my understanding
                    /*               
sum1 = sum1 + p .2015_price__c; declare sum1 sum2 sum3 above in place of sum
sum2 = sum2 + p .2016_price__c;
sum3 = sum2 + p .2017_price__c;

q.total_2015_price__c = sum1;
q.total_2016_price__c = sum2;
q.total_2017_price__c = sum3;

*/
                    q.total_amount__c = sum;
                    
                }
                accountToUpdate .add(q);
            }
            try{
                update accountToUpdate ;
            }Catch(Exception e){
                System.debug('Exception :'+e.getMessage());
            }
        }
    }   
}



Hope it helps.Let me know if you have any issues.

Regards 
Roshni
This was selected as the best answer
rajat Maheshwari 6rajat Maheshwari 6

Hi Roshni,

1. Why do we need to utilize the  sums up of amount of closed won opportunities (2015,2016,2017 price) in coding, when It already done with rollup summary standard salesforce features ?

2. ParentAccountId is simply represent the relationship field for account, so whatever the field name will be, It take place.

3. Yes, we can use checkRecursive, what you have suggest.

Rest I am thinking, Nothing is wrong in my suggesting code :)

 

Note : Every Problems have multiple solution, so @Alex whatever the solution best suit for your use case, GoAhead

 

Thanks

 

Roshni RahulRoshni Rahul
Hi Rajat

If you look at the code you can see we are not taking sums of amount of closed won opportunities (2015,2016,2017 price). Its already there in the account object in 3 fields as i said in the comment section. I am taking their sum and putting it into the parent record ie if a parent record have 2 childs i am summing up the individual roll up fields(2015,2016,2017 price) and putting them into the respective total sums for each year in the parent record or you can put the sum of all years in the total_amount__c field.

Thank you
rajat Maheshwari 6rajat Maheshwari 6

Hi Alex,

If you also want to roll up the sum of Child price (2015,2016,2017) from child account to parent account in addition with ChildAmount ,then please do minor modification in code which you have marked the best answer and it will work for you . Below is code snippet - 

trigger AccountRollupTrigger on Account (after insert,after update,after delete)
  {
      if(checkRecursive.runOnce()){
     Set<Id> setParentAccId = new Set<Id>();


if(Trigger.isInsert || Trigger.isUpdate))
  {
       for(Account acc: Trigger.new)
         {
            if(acc.ParentAccountId!=null)
                   setParentAccId.add(acc.ParentAccountId);
         }
}

if(Trigger.isDelete)
    {
       for(Account acc_Old : Trigger.old)
           {
                if(acc_Old.ParentAccountId!=null)
                    setParentAccId.add(acc_Old.ParentAccountId);
            }
    }

Map<Id,Double> mp_NetAmount = new Map<Id,Double>();
Map<Id,Double> mp_child2015Price = new Map<Id,Double>();
Map<Id,Double> mp_child2016Price = new Map<Id,Double>();
Map<Id,Double> mp_child2017Price = new Map<Id,Double>();

for(AggregateResult agg: [Select ParentAccountId, SUM(childNetAmountfield), SUM(child2015Pricefield), SUM(child2016Pricefield), SUM(child2017Pricefield) from Account where ParentAccountId IN : setParentAccId group by ParentAccountId ])
   {
       mp_NetAmount .put((Id)agg.get('ParentAccountId'),(Double)agg.get('expr0'));
       mp_child2015Price.put((Id)agg.get('ParentAccountId'),(Double)agg.get('expr1'));
       mp_child2016Price.put((Id)agg.get('ParentAccountId'),(Double)agg.get('expr2'));
       mp_child2017Price.put((Id)agg.get('ParentAccountId'),(Double)agg.get('expr3'));
   }

List<Account> ParentListUpdate = new List<Account>();

  for(Account acc : [Select Id, Total_Net_Amount__c, Total_2015_Price__c, Total_2016_Price__c, Total_2017_Price__c  from Account where Id IN :setParentAccId])
{
    Double TotalNetAmount= mp_NetAmount.get(acc.Id);
    Double Total2015Price= mp_child2015Price.get(acc.Id);
    Double Total2016Price= mp_child2016Price.get(acc.Id);
    Double Total2017Price= mp_child2017Price.get(acc.Id);
    
    acc.Total_Net_Amount__c = TotalNetAmount;
    acc.Total_2015_Price__c= Total2015Price;
    acc.Total_2016_Price__c= Total2016Price;
    acc.Total_2017_Price__c  = Total2017Price;
    
    ParentListUpdate .add(acc);
  }
 
if(ParentListUpdate !=null && ParentListUpdate.size()>0)
  update ParentListUpdate ;
}
}
 

Apex class for checkrcursive - 

public Class checkRecursive{
    private static boolean run = true;
    public static boolean runOnce(){
    if(run){
     run=false;
     return true;
    }else{
        return run;
    }
    }
}


Now everything is good. I would like to say thanks to @Roshni to let me know the other use case, which i was missed.

@Alex, please let me know, in case of any help. I am happy to help you :)

Thanks,

Alex ValavanisAlex Valavanis
Hi guys, Much appreciated and thank you for the feedback.
As mentioned fields "2015 Price", "2016 Price", "2017 Price" are roll ups from child accounts closed won opportunities - and i want a rollup in the Parent account of all the "2015 prices", " 2016 prices" etc. etc.
rajat Maheshwari 6rajat Maheshwari 6

Hi Alex,

Yes, the latest code snippet will work for you, please goAhead with that and let me know ,If you need any help.

Thanks

Roshni RahulRoshni Rahul


@Alex, code that i have last updated is based on the requirement that you have mentioned. 
Just check it out
rajat Maheshwari 6rajat Maheshwari 6

@Alex, 

:D :D, Please do one work, Unmark my solution and Mark the @Roshni last updatedCode as Best.

I am happy to be able to given you the solution as per your requirement.

Have a nice day !!!

Thanks

rajat Maheshwari 6rajat Maheshwari 6
@Alex,

Thank you very much !!! If need help, please let me know. I am happy to help you :)