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
colemabcolemab 

Roll up summary for custom object (budget) across account hierarchies?

Everyone,

 

I have a custom object named budget__c that has a M:D relationship with the account object.  I have multiple accounts that are in a hierarchical relation ship such as:   Corporate->Division->Region->State->Office

 

The levels in the hierarchy are limited to just the 5 listed.

 

I want to write a trigger on the budget object to update the total budget on the effected accounts.  So basically, whenever the office budget gets changed, this change is seen at all levels above it.

 

Here is my poorly written first attempt.  Please note this code has a query in a loop - any advice on how to keep it out of the loop would be great.  Also, this code calculates the amount on the child and parent (i.e. grand parent and great grand parent records don't calculate changes to the grand child record).

 

trigger UpsertDelBudget on Budget__c (after delete, after insert, after update) {
	/* 
		This trigger will be used to:
		
		1. Update a 'roll up summary' field based on the account
		   hiearchy.  This will allow the division for example to show
		   the budgets of the offices in that division based on hiearchy.
		   
	*/
	
	system.debug('>>>> UpsertDelBudget Trigger Start');
	
	set<Id> AccountIDsInUpdate = new Set<Id>();
	set<Id> ParentAccountIDsInUpdate = new Set<Id>();
	set<Id> GrandParentAccountIDsInUpdate = new Set<Id>();
	
	// Build set of Account ID's in this update
	if (trigger.isInsert || trigger.isUpdate) {
		
		system.debug('>>>> Insert or Update operation');
		
		for(Budget__c B:Trigger.new)
			{
				AccountIDsInUpdate.add(B.Budget_Account__c);
				system.debug('>>>> Adding Account ID: ' + B.Budget_Account__c); 
				
				if (B.Budget_Parent_ID__c != null) {
					AccountIDsInUpdate.add(B.Budget_Parent_ID__c);
					
					ParentAccountIDsInUpdate.add(B.Budget_Parent_ID__c);
				}	// check for null parent id
					
			} // end for loop
			
	} // end if (trigger.isInsert || trigger.isUpdate) {
	
	if (trigger.isDelete) {
		
		system.debug('>>>> Delete operation');
		
		for(Budget__c B:Trigger.old)
			{
				AccountIDsInUpdate.add(B.Budget_Account__c);
				system.debug('>>>> Adding Account ID: ' + B.Budget_Account__c);

				if (B.Budget_Parent_ID__c != null) {
					AccountIDsInUpdate.add(B.Budget_Parent_ID__c);
							
					ParentAccountIDsInUpdate.add(B.Budget_Parent_ID__c);
				}	// check for null parent id	
				
			} // end for loop
	} // end if (trigger.isDelete) {

	// This loop will add grand parent ID's first run
	// then keep going up until no more parents (great grand, grand, etc) are found
	do {	
		system.debug('>>>> In Do Loop');
		// Adding grand parent IDs 
		for(Account A:
					[SELECT ID, Name, ParentID 
					FROM Account 
					WHERE ID IN :ParentAccountIDsInUpdate 
					]) 
		{
			AccountIDsInUpdate.add(A.ParentID);
			system.debug('>>>> Adding ID ' + A.ParentID);
			system.debug('>>>> Adding Name = ' + A.Name);
			GrandParentAccountIDsInUpdate.add(A.ParentID);
		} // end for loop
		if (GrandParentAccountIDsInUpdate.isEmpty() == false) {
			system.debug('>>>> Grandparents have parents - going again!');
			ParentAccountIDsInUpdate.clear();
			ParentAccountIDsInUpdate.addAll(GrandParentAccountIDsInUpdate);
			GrandParentAccountIDsInUpdate.clear();
		} else {
			system.debug('>>>> No more parents - exiting loop!');
			break; // exit the loop
		}
	} while (true); // seemingly infinite loop!
		
    // ---------------------------------------------------------------
	// build a map of Account id's and calculated totals for budgets 
		Map<Id, Double> CurrentAccountTotalBudgetMap = new Map<Id, Double>();

	for(Id A:AccountIDsInUpdate) {	
		
		system.debug('>>> Current ID ' + a);
		
		// Use Group by in SOQL statement to have a single ID with a single sum
		for(AggregateResult d: 
								[
				SELECT SUM(Current_Year_Budget__c) Total
				FROM Account WHERE ParentID = :A OR ID =:A 
								]		
		){
			CurrentAccountTotalBudgetMap.put(a,(Double)d.get('Total'));
			
			//system.debug('>>>> Current Name = ' + d.get('Name'));
			system.debug('>>>> Current Total ' + d.get('Total'));
					
		} // end for loop to find totals
	} // end of loop thru account IDs
			
	// ---------------------------------------------------------------

	system.debug('>>>> Final AccountIDsInUpdate.size ' + AccountIDsInUpdate.size() ) ;

	// Get sum value from map and create list to update
	
		List<Account> AccountsToUpdate = new List<Account>();
	
		// now loop thru the payments
		
		for(Account a: [SELECT Id, Name, Total_Current_Year_Budget__c, Current_Year_Budget__c
						   FROM Account
						   WHERE Id IN :AccountIDsInUpdate]) 
	    {	    	
			System.debug('>>>> ------------ <<<<<');
			System.debug('>>>> Name ' + a.name);
			
			
			// 1D. set the totals on the payments
			Double BudgetSum = CurrentAccountTotalBudgetMap.get(a.Id);
			if (BudgetSum != null) {
				a.Total_Current_Year_Budget__c = BudgetSum;
			} else {
				a.Total_Current_Year_Budget__c = 0;
			} // check for null values
			
			
    		AccountsToUpdate.add(a);
	    	 
	    } // end loop thru payments
		
	// Update the totals in one DML call
	update AccountsToUpdate;	
	
	system.debug('>>>> UpsertDelBudget Trigger END');
	
} // end class

 

 Any advice in fixing the two above mentioned flaws would be much appricated.

 

Best Answer chosen by Admin (Salesforce Developers) 
colemabcolemab

I got this working with your help.  Thank you very much for the recursive trigger logic!

 

Here is the code for the budget__c trigger:

 

trigger UpsertDelBudget on Budget__c (after delete, after insert, after update, after undelete) {
	
        set<id> parents = new set<id>();

        if(trigger.old!=null) {

            for(Budget__c B:trigger.old) {

            	parents.add(B.Budget_Account__c);
            }               
        }

        if(trigger.new!=null) {

            for(Budget__c B:trigger.new) {

                parents.add(B.Budget_Account__c);
            }
        }

        parents.remove(null);

        for(account[] aa:[select id from account where id in :parents]) {
        	update aa;
        }	
}

 

And here is the code for the account trigger:

trigger RollUpBuget on Account (after insert, before update, after update, after delete, after undelete) {
    
    if(Trigger.isBefore) {
        
        for(account a:trigger.new)
            if(a.Current_Year_Budget__c==null){

                a.Total_Current_Year_Budget__c=0;
                
            } else {

                a.Total_Current_Year_Budget__c=a.Current_Year_Budget__c;
    		}
    		
        for(aggregateresult a:[select parentid,sum(Total_Current_Year_Budget__c)sumb from account where Total_Current_Year_Budget__c<>null and parentid in :trigger.new group by parentid]) {
            trigger.newmap.get((id)a.get('parentid')).Total_Current_Year_Budget__c += (decimal)a.get('sumb');
            
        }            
    } else {
		

		
        set<id> parents = new set<id>();

        if(trigger.old!=null) {

            for(account a:trigger.old) {

                parents.add(a.parentid);
            }
        }
        
        if(trigger.new!=null) {

            for(account a:trigger.new) {

                parents.add(a.parentid);
            }
        }
        parents.remove(null);


        for(account[] aa:[select id from account where id in :parents])
            update aa;
            
    } // end is before
    
} // end trigger class

 

All Answers

sfdcfoxsfdcfox

I think you're doing it wrong... Simply, you should be able to use a system rollup summary to carry the Budget__c values to the account. From there, you should be able to quickly and easily recurse up the tree:

 

trigger rollupbudget on Account (after insert, before update, after update, after delete, after undelete) {
    if(Trigger.isBefore) {
        for(account a:trigger.new)
            if(a.budget__c==null)
                a.budget__c=0;
            else
                a.total_budget__c=a.budget__c;
        for(aggregateresult a:[select parentid,sum(budget__c)sumb from account where budget__c<>null and parentid in :trigger.new group by parentid])
            trigger.newmap.get((id)a.get('parentid')).total_budget__c += (decimal)a.get('sumb');
    } else {
        set<id> parents = new set<id>();
        if(trigger.old!=null)
            for(account a:trigger.old)
                parents.add(a.parentid);
        if(trigger.new!=null)
            for(account a:trigger.new)
                parents.add(a.parentid);
        parents.remove(null);
        for(account[] aa:[select id from account where id in :parents])
            update aa;
    }
}

 If for some reason the standard rollup feature isn't working as you'd like (in this case, my code assumes that Budget__c is writable, but if you had a rollup summary field, it would be read-only), you'd just need to modify this trigger to query each account's budgets and populate the budget field; the rest of the trigger takes care of itself. You'd also need just a small trigger on the budget object to kick off this trigger. It wouldn't consist of anything more than gathering up account id values and then performing an update; use my code as a template for your budget object's trigger.

colemabcolemab

SfdcFox,

 

Thanks for the reply.  I am using a filtered rollup summary field on the account object, that is how Current_Year_Budget__c gets calculated. 

 

I think the condtions on the trigger (i.e. before update, after update, etc) and the trigger on the account were what I was missing.  I am going to work on this today.   Once I get this working, I will come back and mark this as solved.

 

Thanks again.

 

More to follow . . . .

colemabcolemab

I got this working with your help.  Thank you very much for the recursive trigger logic!

 

Here is the code for the budget__c trigger:

 

trigger UpsertDelBudget on Budget__c (after delete, after insert, after update, after undelete) {
	
        set<id> parents = new set<id>();

        if(trigger.old!=null) {

            for(Budget__c B:trigger.old) {

            	parents.add(B.Budget_Account__c);
            }               
        }

        if(trigger.new!=null) {

            for(Budget__c B:trigger.new) {

                parents.add(B.Budget_Account__c);
            }
        }

        parents.remove(null);

        for(account[] aa:[select id from account where id in :parents]) {
        	update aa;
        }	
}

 

And here is the code for the account trigger:

trigger RollUpBuget on Account (after insert, before update, after update, after delete, after undelete) {
    
    if(Trigger.isBefore) {
        
        for(account a:trigger.new)
            if(a.Current_Year_Budget__c==null){

                a.Total_Current_Year_Budget__c=0;
                
            } else {

                a.Total_Current_Year_Budget__c=a.Current_Year_Budget__c;
    		}
    		
        for(aggregateresult a:[select parentid,sum(Total_Current_Year_Budget__c)sumb from account where Total_Current_Year_Budget__c<>null and parentid in :trigger.new group by parentid]) {
            trigger.newmap.get((id)a.get('parentid')).Total_Current_Year_Budget__c += (decimal)a.get('sumb');
            
        }            
    } else {
		

		
        set<id> parents = new set<id>();

        if(trigger.old!=null) {

            for(account a:trigger.old) {

                parents.add(a.parentid);
            }
        }
        
        if(trigger.new!=null) {

            for(account a:trigger.new) {

                parents.add(a.parentid);
            }
        }
        parents.remove(null);


        for(account[] aa:[select id from account where id in :parents])
            update aa;
            
    } // end is before
    
} // end trigger class

 

This was selected as the best answer
tojayanthtojayanth

Hello,

           The Below trigger does not work While you create a new account. I am trying to roll up an Ammount field directly eneterd on child account to be rolled up to the account. Your code is working fine for Updates not on Insert. Please help.

 

thanks

Jayanth