If we have requirement to rollup summary on two level using Apex trigger
We have Account as Grandparent
Contact as parent
Location__c as child
When any record inserted / updated or deleted in Location object we need to calculate number of contacts count and calculate number of location count associated with each contact associated with account and update below two custom fields on Account.
No Of Contacts (All contact associated with account )
No Of Contact Locations -->(all location count from each contact associated with Account)
Apex Trigger:
trigger locationTrigger on Location__c (after insert, after update, after delete) {
Set<Id> contactIdSet = new Set<Id>();
List<Account> accUpdateList = new List<Account>();
if(trigger.isInsert){
for(Location__c locRec: trigger.new){
if(locRec.Contact__c != null){
contactIdSet.add(locRec.Contact__c);
}
}
}
if( trigger.isUpdate){
for(Location__c locRec: trigger.new){
if(locRec.Contact__c != null &&
locRec.Contact__c != trigger.oldMap.get(locRec.Id).Contact__c){
contactIdSet.add(locRec.Contact__c);
contactIdSet.add(trigger.oldMap.get(locRec.Id).Contact__c);
}
}
}
if(trigger.isDelete){
for(Location__c locRec: trigger.old){
contactIdSet.add(locRec.Contact__c);
}
}
Set<Id> accountIdSet = new Set<Id>();
set<Id> allContactIdSet = new Set<Id>();
for (Contact con : [SELECT Id,AccountId
from Contact where Id In:contactIdSet ]) {
if(con.AccountId != null){
accountIdSet.add(con.AccountId);
}
}
for (Contact con : [SELECT Id from Contact where AccountId In:accountIdSet ]) {
allContactIdSet.add(con.Id);
}
Map<Id, Integer> accountWithLocCountMap = new Map<Id, Integer>();
List<AggregateResult> locAggrList = [SELECT Count(Id)locCount,Contact__r.AccountId AccId
from Location__c
where contact__c In:allContactIdSet
group by Contact__r.AccountId];
for(AggregateResult aggr: locAggrList){
if((string)aggr.get('AccId') != null){
accountWithLocCountMap.put((string)aggr.get('AccId'), (Integer)aggr.get('locCount') );
}
}
if(locAggrList.isEmpty() ||
(!locAggrList.isEmpty() && accountWithLocCountMap.isEmpty())){
for(Id accId: accountIdSet){
accountWithLocCountMap.put(accId, 0 );
}
}
Map<Id, Integer> accountWithContactMap = new Map<Id, Integer>();
List<AggregateResult> conAggrList = [SELECT count(Id)conCount, AccountId
FROM Contact
where AccountId In:accountWithLocCountMap.keySet()
group by AccountId];
for(AggregateResult aggr: conAggrList){
accountWithContactMap.put((string)aggr.get('AccountId'), (Integer)aggr.get('conCount') );
}
if(conAggrList.isEmpty()){
for(Id accId: accountIdSet){
accountWithContactMap.put(accId, 0 );
}
}
for(Id accId : accountWithContactMap.keySet()){
Account acc = new Account();
acc.Id = accId;
acc.No_Of_Contacts__c = accountWithContactMap.get(accId);
acc.No_Of_Contact_Location__c = accountWithLocCountMap.get(accId);
accUpdateList.add(acc);
}
if(!accUpdateList.isEmpty()){
update accUpdateList;
}
}
Test Results:
Account Which having 2 contacts
Create New location on one of Contact
Check Contact and location count on account
Add another location on another contact
Check Count on Account
Then Try to delete one of location
Check Location count on account
Update Location and associate to another contact which not associated with this old contact account (update contact which account is different)
Check Count is reduced..
