Can anyone assist me in coming up with a formula/roll up summary for the Account object that will give a numerical value from calculating the number of related contacts
-
4This is harder than it seems at first sight because a roll-up summary field can't be created across this relationship. So triggers have to be used; see e.g. Trigger for Contact count on Account giving Exception: Too many code statements: 200001 and the various answers. If your org has large numbers of Contacts, you are also in danger of hitting the 50,000 "Total number of records retrieved by SOQL queries" governor limit.– Keith CNov 17, 2014 at 16:36
2 Answers
You can install the Declarative Rollup Summary Tool, which was created by the CTO of FinancialForce and is a free utility which enables you to create rollup summaries really neatly across master-detail and lookup relationships declaratively.
You can find more information on this here: https://github.com/afawcett/declarative-lookup-rollup-summaries.
The declarative lookup rollup summary you would want in order to achieve the # of contacts per accounts is:
Lookup Rollup Summary Name: 'Number of Contacts per Account' Parent Object: Account Child Object: Contact Relationship Field: AccountId Field to Aggregate: Id Aggregate Operation: Count Aggregate Result Field: Number_of_Contacts__c Active: Yes Calculation Mode: Realtime Calculation Sharing Mode: User
I am not sure you can achieve this declaratively, if there is then i would be interested myself :)
The alternate method would be to write a trigger on contact object to update the realtime count value of related contacts.
/* 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();
}
Note: This code is taken from http://www.ericsantiago.com/eric_santiago/2009/12/contactscases-rollup-for-accounts-in-salesforce.html. The credit for this code goes to the author
Hope this helps