Introduction:

In this blog post, we will explore an example that shows how to roll up child counts using aggregate query. It will also demonstrate how to sum values on a parent record. This example applies to lookup relationships but you can use the same with master-detail also.

Example Scenario:

Imagine a business that manages projects (A__c records) and their associated tasks (B__c records) in Salesforce. Tasks (B__c) has a look relationship to projects (A__c) records. Each project has a field to track the total number of tasks (Record_Count__c). Another field sums up the estimated effort or cost of all tasks (Total__c).

Whenever a task is added, updated, removed, or reassigned between projects, the corresponding project’s fields must reflect the changes. For example:

1. Adding a New Task: When a task is added to a project, the task count should increase. Additionally, the total effort and cost fields on the project should increase accordingly.
2. Updating a Task: If the task’s effort/cost changes, the corresponding project’s total should be updated. Similarly, if the task is moved to another project, the old project needs to reflect the correct task counts. The new project also needs to reflect the correct totals.
3. Deleting a Task: When a task is removed, the project’s task count and total effort/cost fields should decrease accurately.
4. Restoring a Task: If a previously deleted task is restored, the associated project’s fields must include it again.

Project managers always have up-to-date information about the number of tasks. They also know their cumulative effort and cost without manual calculations. The RollUpBOnA trigger automates these updates, enhancing data accuracy and reducing administrative effort.

Trigger Overview:

This is a Salesforce Apex trigger. It maintains summary fields on a parent record (A__c). The updates are based on changes to its child records (B__c).

What it Updates :

• Record_Count__c: Counts how many child records are linked to each parent
• Total__c: Sums up the Value__c field from all child records

When it Runs:

The trigger fires when child records (B__c) are:

• ✨ Created (Insert)
• 🔄 Updated
• ❌ Deleted
• ↩️ Undeleted

How It Works:

Here is a step-by-step explanation of what the RollUpBOnA trigger does:

1. Declare a Set for Parent Record IDs:
   The trigger begins by declaring a set. It uses parentIds to store the IDs of the parent A__c records. These records need to be updated.
2. Identify Relevant Parent IDs for Insert or Undelete Events:
   • During insert or undelete events, the trigger loops through the Trigger.new collection.
   • If a child record (B__c) has a non-null parent (A_No__c), the parent ID is added to the parentIds set.
3. Identify Relevant Parent IDs for Delete Events:
   • During delete events, the trigger loops through the Trigger.old collection.
   • If a deleted child record has a non-null parent, the parent ID is added to the parentIds set.
4. Handle Updates to Parent IDs or Values:
   • During update events, the trigger compares the old (Trigger.oldMap) and new (Trigger.new) versions of each B__c record.
   • If the parent ID (A_No__c) changes, both the old and new parent IDs are added to the set.
   • If the parent ID remains the same but the value (Value__c) field changes, add the parent ID to the set.
5. Exit if No Parent IDs to Update:
   If no parent IDs are collected in the set, the trigger exits. There is no further processing.
6. Aggregate Queries to Count Records and Sum Values:
   • The trigger uses an aggregate query to count the number of B__c records (COUNT(Id)). It sums their Value__c fields for each A__c record in the parentIds set.
   • The results are stored in two maps: one for the record count (childRecordCount) and one for the total value (childValuesSum).
7. Retrieve and Prepare Parent Records for Update:
   • The trigger queries all A__c records in the parentIds set, retrieving their Record_Count__c and Total__c fields.
   • It updates these fields based on the values stored in the maps (childRecordCount and childValuesSum). If a parent ID is not found in the maps, the corresponding field is set to 0.
8. Collect Parent Records to Update:
   Each updated parent record is added to a list (parentsToUpdate) for processing.
9. Perform the Update Operation:
   Finally, the trigger updates all the A__c records in the parentsToUpdate list. This ensures their Record_Count__c and Total__c fields are accurate. They are also kept up-to-date.

Sample Code:

	/**
	* RollUpBOnA Trigger
	* —————-
	* This trigger is executed after a B__c record is inserted, updated, deleted, or undeleted.
	* The purpose is to update the Total__c and Record_Count__c fields on the related A__c record.
	* Key Features:
	* – Identifies B__c records linked to an A__c record (via A_No__c).
	* – Counts the number of B__c records and sums their Value__c field for each A__c record.
	* – Updates the Total__c and Record_Count__c fields on the A__c record.
	* – Ensures any updates made re-trigger workflows or additional triggers, if applicable.
	* – Demonstrates the use of aggregate queries to optimize performance.
	*/
	trigger RollUpBOnA on B__c (after delete, after insert, after update, after undelete) {
	// Declare a set to store the Ids of the parent object A__c
	Set<Id> parentIds = new Set<Id>();
	// Check if the trigger is in the 'insert' or 'undelete' context
	if (Trigger.isInsert || Trigger.isUndelete) {
	// Loop through the new records
	for (B__c child : Trigger.new) {
	// Check if the child record has a parent ID
	if (child.A_No__c != null) {
	// Add the parent ID of each new or undeleted child record to the set of parent IDs
	parentIds.add(child.A_No__c);
	}
	}
	}
	// Check if the trigger is in the 'delete' context
	if (Trigger.isDelete) {
	// Loop through the old records
	for (B__c child : Trigger.old) {
	// Check if the child record has a parent ID
	if (child.A_No__c != null) {
	// Add the parent ID of each deleted child record to the set of parent IDs to be updated
	parentIds.add(child.A_No__c);
	}
	}
	}
	// Check if the trigger is in the 'update' context
	if (Trigger.isUpdate) {
	// Loop through the new records
	for (B__c child : Trigger.new) {
	// Assume the old parent ID
	Id oldParentId = Trigger.oldMap.get(child.Id).A_No__c;
	// Assin the new parent ID
	Id newParentId = child.A_No__c;
	// Check if the parent ID has changed
	if (oldParentId != newParentId) {
	// If the old parent ID is not null, add it to the set of parent IDs
	if (oldParentId != null) parentIds.add(oldParentId);
	// If the new parent ID is not null, add it to the set of parent IDs
	if (newParentId != null) parentIds.add(newParentId);
	} else if (child.Value__c != Trigger.oldMap.get(child.Id).Value__c) {
	// If the value field has changed, add the parent ID to the set of parent IDs
	parentIds.add(newParentId);
	}
	}
	}
	// If there are no parent IDs to update, exit the trigger
	if (parentIds.isEmpty()) return;
	// Declare maps to store child record counts and sum of values
	Map<Id, Integer> childRecordCount = new Map<Id, Integer>();
	Map<Id, Decimal> childValuesSum = new Map<Id, Decimal>();
	// Query the aggregate data using aggregate queries for count and sum and loop through the results
	// to populate the childRecordCount map with the number of child records for each parent
	// and the childValuesSum map with the sum of values for each parent
	for (AggregateResult ar : [
	SELECT A_No__c parentId, COUNT(Id) recordCount, SUM(Value__c) totalValue
	FROM B__c
	WHERE A_No__c IN :parentIds
	GROUP BY A_No__c
	]) {
	// Extract the parent ID from the aggregate result
	Id parentId = (Id) ar.get('parentId');
	// Store the record count in the childrecordCount map against the parent ID
	childRecordCount.put(parentId, (Integer) ar.get('recordCount'));
	// Store the sum of values in the childValuesSum map against the parent ID
	childValuesSum.put(parentId, (Decimal) ar.get('totalValue'));
	}
	// Declare a list to hold the parent records to update
	List<A__c> parentsToUpdate = new List<A__c>();
	// Query the parent records and loop through them to update the Record_Count__c and Total__c fields om parent records
	for (A__c parent : [
	SELECT Id, Record_Count__c, Total__c
	FROM A__c
	WHERE Id IN :parentIds
	]) {
	// Update the Record_Count__c on the parent record
	// If the parent ID is in the child record count map, set the Record_Count__c to that value or 0
	parent.Record_Count__c = childRecordCount.containsKey(parent.Id) ? childRecordCount.get(parent.Id) : 0;
	// Update the Total__c on the parent record
	// If the parent ID is in the child values sum map, set the Total__c to that value or 0
	parent.Total__c = childValuesSum.containsKey(parent.Id) ? childValuesSum.get(parent.Id) : 0;
	// Add the parent record to the list of records to update
	parentsToUpdate.add(parent);
	}
	// If there are any parent records to update, proceed with the update
	if (!parentsToUpdate.isEmpty()) {
	update parentsToUpdate;
	}
	}

view raw RollUpBOnA.java hosted with ❤ by GitHub

	/**
	* This is a test class for the RollUpBOnA trigger.
	* It includes various test methods to cover different scenarios:
	* 1. Inserting a B with an A
	* 2. Deleting a B
	* 3. Undeleting a B
	* 4. Updating a B's A
	*/
	@isTest
	public class RollUpBOnA_Test {
	/*
	* This method sets up the test data for the test class.
	* It creates two test A records that will be used in the test methods.
	*/
	@TestSetup
	static void makeData() {
	insert new List<A__c> {
	new A__c(Name = 'Test1'),
	new A__c(Name = 'Test2')
	};
	}
	/*
	* This method tests the insertion of a B with an A.
	* It verifies that the Record Count and Total field on the A is updated correctly.
	*/
	@isTest
	static void testInsertBWithA() {
	A__c a = [SELECT Id FROM A__c WHERE Name = 'Test1' LIMIT 1];
	Test.startTest();
	insert new B__c(Name = 'Test B', Value__c = 10, A_No__c = a.Id);
	Test.stopTest();
	A__c updated = [SELECT Record_Count__c, Total__c FROM A__c WHERE Id = :a.Id];
	System.assertEquals(1, updated.Record_Count__c, 'B count should be 1');
	System.assertEquals(10, updated.Total__c, 'Total should be 10');
	}
	/*
	* This method tests the deletion of a B.
	* It verifies that the Record Count and Total field on A is updated correctly.
	*/
	@isTest
	static void testDeleteB() {
	A__c a = [SELECT Id FROM A__c WHERE Name = 'Test1'];
	B__c b = new B__c(Name = 'Test B', Value__c = 5, A_No__c = a.Id);
	insert b;
	Test.startTest();
	delete b;
	Test.stopTest();
	A__c updated = [SELECT Record_Count__c, Total__c FROM A__c WHERE Id = :a.Id];
	System.assertEquals(0, updated.Record_Count__c, 'B count should be 0 after delete');
	System.assertEquals(0, updated.Total__c, 'Total should be 0 after delete');
	}
	/*
	* This method tests the undelete operation on a B.
	* It verifies that the Record Count and Total field on the A is updated correctly after undelete.
	*/
	@isTest
	static void testUndeleteB() {
	A__c a = [SELECT Id FROM A__c WHERE Name = 'Test1'];
	B__c b = new B__c(Name = 'Test B', Value__c = 15, A_No__c = a.Id);
	insert b;
	delete b;
	Test.startTest();
	undelete b;
	Test.stopTest();
	A__c updated = [SELECT Record_Count__c, Total__c FROM A__c WHERE Id = :a.Id];
	System.assertEquals(1, updated.Record_Count__c, 'B count should be 1 after undelete');
	System.assertEquals(15, updated.Total__c, 'Total should be 15 after undelete');
	}
	/*
	* This method tests updating the parent A of a B.
	* It verifies that the Record Count and Total field on both A records is updated correctly.
	*/
	@isTest
	static void testUpdateBAssociation() {
	List<A__c> aList = [SELECT Id FROM A__c ORDER BY Name];
	B__c b = new B__c(Name = 'Test B', Value__c = 20, A_No__c = aList[0].Id);
	insert b;
	Test.startTest();
	b.A_No__c = aList[1].Id;
	update b;
	Test.stopTest();
	List<A__c> updated = [SELECT Record_Count__c, Total__c FROM A__c WHERE Id IN :aList ORDER BY Name];
	System.assertEquals(0, updated[0].Record_Count__c, 'Old A should have 0 Bs');
	System.assertEquals(0, updated[0].Total__c, 'Old A should have 0 total');
	System.assertEquals(1, updated[1].Record_Count__c, 'New A should have 1 B');
	System.assertEquals(20, updated[1].Total__c, 'New A should have total 20');
	}
	/*
	* This method tests updating the Value on B.
	* It verifies that the Total field on A record is updated correctly.
	*/
	@isTest
	static void testUpdateBValue() {
	A__c a = [SELECT Id FROM A__c WHERE Name = 'Test1'];
	B__c b = new B__c(Name = 'Test B', Value__c = 20, A_No__c = a.Id);
	insert b;
	Test.startTest();
	b.Value__c = 30;
	update b;
	Test.stopTest();
	A__c updated = [SELECT Record_Count__c, Total__c FROM A__c WHERE Id = :a.Id];
	System.assertEquals(1, updated.Record_Count__c, 'A should have 1 B');
	System.assertEquals(30, updated.Total__c, 'Total should reflect updated value');
	}
	/*
	* This method tests bulk insert of B records.
	*/
	@isTest
	static void testBulkInsertB() {
	A__c a = [SELECT Id FROM A__c WHERE Name = 'Test1'];
	List<B__c> bList = new List<B__c>();
	for (Integer i = 0; i < 200; i++) {
	bList.add(new B__c(Name = 'B ' + i, Value__c = 1, A_No__c = a.Id));
	}
	Test.startTest();
	insert bList;
	Test.stopTest();
	A__c updated = [SELECT Record_Count__c, Total__c FROM A__c WHERE Id = :a.Id];
	System.assertEquals(200, updated.Record_Count__c, 'B count should be 200');
	System.assertEquals(200, updated.Total__c, 'Total should be 200');
	}
	/*
	* This method tests inserting B record without A.
	*/
	@isTest
	static void testNullAId() {
	B__c orphanB = new B__c(Name = 'Orphan B', Value__c = 999); // No A_No__c
	Test.startTest();
	insert orphanB;
	Test.stopTest();
	List<A__c> allA = [SELECT Record_Count__c, Total__c FROM A__c];
	for (A__c a : allA) {
	System.assertEquals(true, a.Record_Count__c == null || a.Record_Count__c == 0, 'Should not affect any A record');
	System.assertEquals(true, a.Total__c == null || a.Total__c == 0, 'Should not affect any A record');
	}
	}
	}

view raw RollUpBOnA_Test.java hosted with ❤ by GitHub

Key Benefits:

• 🚀 Efficient (uses aggregate queries)
• 🎯 Accurate (handles all DML scenarios)
• 💪 Robust (handles null values and edge cases)
• 🔄 Maintains roll-up summary fields in real-time

Note – The above code shows an example of using aggregate query for calculations. If you want to use the map approach, please refer to this blog post Roll up summary trigger To Update Child Records Count and total Sum of values of child records on Parent Record using Maps.