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
Check Count is reduced..
Good info. Thanks!
ReplyDeleteTwo things are missing on update senario if contact is not mandatory on location obhect
ReplyDelete1.Exsting Location object record which don't have contcat and updating it with contcat.
2.Extsting Location object record which has contact and we are updating it without contact.
Also I believe in code don't require 2 for loop we can add accountId !=null inside query.
I believe some more improvement is needed in this code. nice try.
From
Enzigma