Contacts/Cases rollup for Accounts in Salesforce

** Update Sept 3rd 2015 – This code is not recommended as it does not scale for Contacts greater than 1,000. I will be posting an update version of this code soon. **

If you’re familiar with using roll-up/summary fields in Salesforce, you’ve probably come up against the fact that you can’t create those fields types to count the number of cases or contacts related to a given account record. I don;t know why that is, but I recently created a workaround using Apex triggers to perform that function in the same automated fashion.

You’ll need to add two fields to your account object. The labels can be changed but the API names should be left as is, if you don’t want to change the code.

  • Number of Cases – Number_of_Cases__c
  • Number Of Contacts – Number_of_Contacts__c

Then add the following triggers to your org. First, the Case trigger;

/* Provide summary of Number of Cases on Account record */

trigger CaseTrigger on Case (after delete, after insert, after undelete,
after update) {

    Case[] cases;
    if (Trigger.isDelete)
        cases = Trigger.old;
    else
        cases = Trigger.new;

    // get list of accounts
    Set<ID> acctIds = new Set<ID>();
    for (Case cse : cases) {
            acctIds.add(cse.AccountId);
    }
   
    Map<ID, Case> casesForAccounts = new Map<ID, Case>([select Id
                                                            ,AccountId
                                                            from Case
                                                            where AccountId in :acctIds]);

    Map<ID, Account> acctsToUpdate = new Map<ID, Account>([select Id
                                                                 ,Number_of_Cases__c
                                                                  from Account
                                                                  where Id in :acctIds]);
                                                                
    for (Account acct : acctsToUpdate.values()) {
        Set<ID> caseIds = new Set<ID>();
        for (Case cse : casesForAccounts.values()) {
            if (cse.AccountId == acct.Id)
                caseIds.add(cse.Id);
        }
        if (acct.Number_of_Cases__c != caseIds.size())
            acct.Number_of_Cases__c = caseIds.size();
    }

    update acctsToUpdate.values();
}

Next the Contact trigger;

/* Provide summary of Number of Contacts on Account record */

trigger ContactSumTrigger on Contact (after delete, after insert, after undelete,
after update) {

    Contact[] cons;
    if (Trigger.isDelete)
        cons = Trigger.old;
    else
        cons = Trigger.new;

    // get list of accounts
    Set<ID> acctIds = new Set<ID>();
    for (Contact con : cons) {
            acctIds.add(con.AccountId);
    }
   
    Map<ID, Contact> contactsForAccounts = new Map<ID, Contact>([select Id
                                                            ,AccountId
                                                            from Contact
                                                            where AccountId in :acctIds]);

    Map<ID, Account> acctsToUpdate = new Map<ID, Account>([select Id
                                                                 ,Number_of_Contacts__c
                                                                  from Account
                                                                  where Id in :acctIds]);
                                                                
    for (Account acct : acctsToUpdate.values()) {
        Set<ID> conIds = new Set<ID>();
        for (Contact con : contactsForAccounts.values()) {
            if (con.AccountId == acct.Id)
                conIds.add(con.Id);
        }
        if (acct.Number_of_Contacts__c != conIds.size())
            acct.Number_of_Contacts__c = conIds.size();
    }

    update acctsToUpdate.values();

}

Now those two fields will maintain a real-time count of the contacts/cases related to a parent account even if those records are deleted and undeleted (restored from the Recycle bin).

You can Download (7k) all the triggers and test classes necessary for deployment via Eclipse or Ant. Enjoy.

Advertisements
This entry was posted in Salesforce, Web/Tech and tagged , , . Bookmark the permalink.

17 Responses to Contacts/Cases rollup for Accounts in Salesforce

  1. Maria says:

    Thank you very much for your contribution. I found an issue with this code. When you change the account that the contact is assigned to, the field Number_of_Contacts__c is not updated in the previous account. What i mean is if I move the contact from Account A to Account B, Account A still shows that there is a contact when in reality I have moved it to Account B. What I need to happen is that in Account A, the field gets updated to nº of contacts -1. Does anyone know how I can resolve it??
    Thanks

    Like

  2. Travis says:

    Hello,
    Any idea how to modify this to only pull open cases

    Like

  3. Grant says:

    Hi this is great! But how can you run tests on this to see if it passes and does not exceed the governor limits for salesforce?

    Like

  4. Grant says:

    Hi Eric
    Unfortunately your code does not support a case count of 1000+ records you receive an error of: System.LimitException: Too many query rows: 1001
    Please can you help here?
    Thanks
    Grant

    Like

  5. Doug says:

    Hi Travis,
    We needed the same and modified line 21 to this below and it worked great:
    where AccountId in :acctIds and Case.IsClosed = false]);

    Like

  6. Ian says:

    Maria,
    I am seeing the same problem as well.
    Basically, the trigger only updates based on a list of the NEW values of the Account field.
    This is specified here:
    Contact[] cons;
    if (Trigger.isDelete)
    cons = Trigger.old;
    else
    cons = Trigger.new;
    I.e. It only looks for companies that are in the Contact.Account field AFTER the change has been made. (So the old company does not get recalculated)
    Though I must say I’m not yet sure how to solve this…

    Like

  7. Amanda F says:

    Hey Eric, Your formula above totally worked – thank you!! Have any suggestions on how to add a few filters, like only total contacts with certain titles?? Any help or suggestions would be much appreciated. Thanks!

    Like

  8. William M says:

    Hello Eric
    This is great. Thank you!
    Similar to Amanda – any suggestions on breaking the case count on the Account by case record type?
    – W

    Like

  9. Peter says:

    With this code it should also recalculate the old Account.
    for (Contact con : cons) {
    acctIds.add(con.AccountId);
    acctIds.add(Trigger.oldMap.get(con.Id).AccountId);
    }
    Now, it works perfect for me.

    Like

  10. subramani says:

    i got the error like this……
    Error: Compile Error: Invalid initial type LIST for MAP at line 21 column 38

    Like

  11. John says:

    Thanks Eric, great stuff … many appreciates. I used your code as the basis for a trigger to roll-up the number of attachments on an object fyi.

    Like

  12. Thanks Eric, old SFDC proserv buddy…
    I used this to roll up attachments to a parent…works great for me so far.
    Fahd

    Like

  13. RWW says:

    When I add this code to the above trigger:
    acctIds.add(Trigger.oldMap.get(con.Id).AccountId);
    I get this error when adding new contacts:
    Attempt to de-reference a null object: Trigger.ContactSumTrigger: line 16, column 1
    If i comment out the ‘added’ line from above the error goes away.
    I am a newb on code so no idea what i’m missing.

    Like

  14. I wrote this code quite a while ago, so it doesn’t follow some of the best practices I’ve learned since. It doesn’t scale and will hit some Governor limits on Accounts with large volumes of Cases/Contacts. It also doesn’t accommodate for Contacts/Cases being reassigned to new accounts. I plan to redact some parts of this post and post some new code that better conforms to accepted best practices. Until then, use this at your own risk.

    Like

  15. Zakeer says:

    Error: Compile Error: Invalid initial type LIST for MAP at line 21 column 38

    Like

  16. Chris says:

    This is the most straight forward support I have seen for trying to accomplish this task. I would like to know if anyone could provide updates to this code for it to only count cases that occurred in the past thirty days and with a case type of complaint. I am new to code and still learning and to get my IT department to try this in sandbox and give me guidance it will probably take a month. I have access to sandbox and would like to accelerate this project.

    Like

  17. Rachel says:

    Hi, I was looking for code coverage for deployment and couldn’t found any.
    Can anyone help?

    Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s