+ Start a Discussion
sfdc.dev.ax856sfdc.dev.ax856 

Count related records in trigger

Hi All,

 

            I need to count related records in trigger and it should work in bulk.Any Idea ?

Shashikant SharmaShashikant Sharma

Could you please explain a bit your requirement. Do you want to count number of child records for a parent record? Like no of contacts for a Account?

 

 

 

sfdc.dev.ax856sfdc.dev.ax856

Hi Sharma,

 

           Yes u r right.I need to use that count in my trigger.

Shashikant SharmaShashikant Sharma

try this I have write this for Account and Contact

 

trigger CountAccountContacts on Contact (before insert)
{
   Set<ID> setAccId = new Set<ID>();
   Map<Id , Integer> mapAccNewContact = new Map<Id , Integer>();
   for(Contact c : trigger.new)
     {
         if(c.AccountId == null)
         {
             setAccId.add(c.AccountId);
             
             if(mapAccNewContact.ContainsKey(c.AccountId))
                 {
                     mapAccNewContact.put(c.AccountId , mapAccNewContact.get(c.AccountId) + 1);
                 }
             else
                 {
                     mapAccNewContact.put(c.AccountId , 1);
                 }
         }
     }
     
   MAP<ID , Account> mapAcc = new Map<Id , Account>([Select id , CountContact__c , (Select id From Contacts) from   Account where id in:setAccId]);
   for(ID accID :  mapAcc.keyset())
   {
       Account a = mapAcc.get(accID);
       //a.CountContact__c = a.CountContact__c + mapAccNewContact.get(accID);
       a.CountContact__c = a.Contacts.size() + mapAccNewContact.get(accID);
   }                                             
  
   update mapAcc.values();
}

 

 

 Here CountContact__c is the Number field to save the count and Contacts is the relationship bwtween account and contact

I have used given statement to calculate count you can use either of one, just remove one as per your wish

//a.CountContact__c = a.CountContact__c + mapAccNewContact.get(accID);
       a.CountContact__c = a.Contacts.size() + mapAccNewContact.get(accID);

 

 

try this one , we would also require a before delete trigger on child to reduce the count in case of deletion of child.

Let me know the result of first then we can work on before delete as well, een though the logic will be same. Just need to reduce instead of adding.

Shashikant SharmaShashikant Sharma

Here is the trigger with delete case as well, i have added before update as well as contacts account can be changed later as well so count need to be changed at that time as well

 

trigger CountAccountContacts on Contact (before insert , before update , before delete)
{
   Set<ID> setAccId = new Set<ID>();
   Map<Id , Integer> mapAccNewContact = new Map<Id , Integer>();
   List<Contact> conList = Trigger.IsInsert ? trigger.new : trigger.old;
   
   for(Contact c : conList)
     {
         if(c.AccountId == null)
         {
             setAccId.add(c.AccountId);
             
             if(mapAccNewContact.ContainsKey(c.AccountId))
                 {
                     mapAccNewContact.put(c.AccountId , mapAccNewContact.get(c.AccountId) + 1);
                 }
             else
                 {
                     mapAccNewContact.put(c.AccountId , 1);
                 }
         }
     }
     
   MAP<ID , Account> mapAcc = new Map<Id , Account>([Select id , CountContact__c , (Select id From Contacts) from   Account where id in:setAccId]);
   for(ID accID :  mapAcc.keyset())
   {
       Account a = mapAcc.get(accID);
       if(Trigger.IsInsert)
       {
       //a.CountContact__c = a.CountContact__c + mapAccNewContact.get(accID);
       a.CountContact__c = a.Contacts.size() + mapAccNewContact.get(accID);
       }
       else
       {
           //a.CountContact__c = a.CountContact__c - mapAccNewContact.get(accID);
           a.CountContact__c = a.Contacts.size() - mapAccNewContact.get(accID);
       }
   }        
                                        
   update mapAcc.values();
   
}

 let me know with your results.

Shashikant SharmaShashikant Sharma

Use this one earlier will not work ,   I used c.Accountid == null in last one instead of c.AccountId != null

 

I have verified this on my org working fine , you can just replace your object API name and field name to replicate it.

 

trigger CountAccountContacts on Contact (before insert , before delete)
{
   Set<ID> setAccId = new Set<ID>();
   Map<Id , Integer> mapAccNewContact = new Map<Id , Integer>();
   List<Contact> conList = Trigger.IsInsert ? trigger.new : trigger.old;
   
   for(Contact c : conList)
     {
         if(c.AccountId != null)
         {
             setAccId.add(c.AccountId);
             
             if(mapAccNewContact.ContainsKey(c.AccountId))
                 {
                     mapAccNewContact.put(c.AccountId , mapAccNewContact.get(c.AccountId) + 1);
                 }
             else
                 {
                     mapAccNewContact.put(c.AccountId , 1);
                 }
         }
     }
     
   MAP<ID , Account> mapAcc = new Map<Id , Account>([Select id , CountContact__c , (Select id From Contacts) from   Account where id in:setAccId]);
   for(ID accID :  mapAcc.keyset())
   {
       Account a = mapAcc.get(accID);
       if(Trigger.IsInsert)
       {
       //a.CountContact__c = a.CountContact__c + mapAccNewContact.get(accID);
       a.CountContact__c = a.Contacts.size() + mapAccNewContact.get(accID);
       }
       else
       {
           //a.CountContact__c = a.CountContact__c - mapAccNewContact.get(accID);
           a.CountContact__c = a.Contacts.size() - mapAccNewContact.get(accID);
       }
   }        
                                        
   update mapAcc.values();
   
}

 

In case of update there is going to be mixed logic that iahve not yet added because then we need to increse one and decrease other. Let me know if it is working or not.

sfdcfoxsfdcfox

The code here is entirely too complex, and as you say, does not consider update scenarios.

 

Here's the total solution:

 

trigger rollupContactsToAccount on Contact (after insert, after update, after delete, after undelete) {
    List<Contact> contacts = new list<contact>();
    Map<Id,account> accounts = new map<id,account>();
    if(trigger.new!=null)
        contacts.addAll(trigger.new);
    if(trigger.old!=null)
        contacts.addAll(trigger.old);
    for(contact c:contacts)
        accounts.put(c.accountid,new account(id=c.accountid));
    accounts.remove(null);
    update accounts.values();
}

 

trigger rollupAccountContacts on Account (before insert, before update) {
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.contactcount__c = 0;
    else
        for(account a:[select id,(select id from contacts) from account where id in :trigger.new])
            trigger.newmap.get(a.id).contactcount__c = a.contacts.size();
}

This trigger will keep your numbers in sync no matter what happens to the data.

Shashikant SharmaShashikant Sharma

@sfdcfox, I agree, Yes this looks simple approach to me. Just one more thing If we can add to this 

 

The Account will get updated for many reason not only from contact insertion,deleteion or updation only. Can we put a some kind of check so that in those scenarios

 

account a:[select id,(select id from contacts) from account where id in :trigger.new] 

 

this does not get executed evry time, so that we can save one SOQL and many SOQL rows 

 

one solution that comes to my mind is use of static boolean which will set to true in contact trigger and will be set to false in account.Please if you can suggest any other solution to this case.

sfdcfoxsfdcfox

Yes, you could include a global static class variable that would be set to false by default, and set to true in the contact trigger in order to avoid the scenario where only the account is being updated. With the current governor limits, this should only be a problem in the event that a single account had thousands of contacts. Most organizations do not store contacts this way, so the trigger shouldn't have a problem, nor significantly slow the saving process. I do agree that if the situation waranted this flag that it should be included.

danny625danny625

hello sashikant sharma

 

hi sir can u plz give the code to work for update as well .....thanks...it was really useful and am using it but i need it for update operation also

JK_RockyJK_Rocky
Thank you @sfdcfox, this worked perfectly!