function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ChristiChristi 

Account Score formula based on Contact Score sum

We are switching to an Account Based Marketing model and looking to create an "Account Score" formula field that basically takes the sums up the individual "Contact Scores" of all contacts associated with an account. I know of a few services like engagio that can do this but if there is a more organic (and free) way to do this I want to know.
Can anyone help? Truly appreciate any help I can get. 
Deepak Maheshwari 7Deepak Maheshwari 7

Hi Christi,

 

For this, you need to make a trigger to roll up the sum of Contact Scores on its corresponding Account.
LBKLBK
Hi Christi,

Account - Contact relationship is 1:N, so you cannot access Contact data from formula.

Also, since it is a Lookup relationship, you cannot have direct rollup summary fields either.

You need a trigger on Contact object to populate this score in Account.

Try this trigger below. 
trigger updateAccountScore on Contact (after insert, after update)
 {
    Map<Id, Decimal> mapAccountScore = new Map<Id, Decimal>();
    SET<Id> keys = trigger.newMap.keySet();
    List<Contact> lstContacts = [SELECT Id, AccountId, Contact_Score__c FROM Contact WHERE Id IN :keys];
	
    for (Contact objContact : lstContacts)//Query for Files
    {    
        Decimal nAccountScore = 0;
         if (mapAccountScore.containsKey(objContact.AccountId)) {
            nAccountScore = mapAccountScore.get(objContact.AccountId);
         }
         mapAccountScore.put(objContact.AccountId, nAccountScore + objContact.Contact_Score__c);
    }

	if(mapAccountScore.size() > 0){
		List<Account> lstAccounts = [SELECT Id, Account_Score__c FROM Account WHERE ID IN :mapAccountScore.keySet()];
		
		for (Account acct : lstAccounts) 
		{
			acct.Account_Score__c = mapAccountScore.get(acct.Id);
		}
		update lstAccounts;
	}
}

I have assumed that the API names for the score fields are Contact_Score__c (Contact) and Account_Score__c (Account) and they are Decimal type.

Adjust the names as it fits your design.

Let me know if this helps.
ChristiChristi
Thanks a lot LBK, I finally had a chance to create the trigger in my Sandbox. I hope you forgive my limited SFDC expertise, but was wondering if there was a way to push this retroactively rather than waiting for a new account to be created?
LBKLBK
Hi Christi,

Happy to help. And, I am also learning new aspects on the way.

Yes. You need to do the update once then let the trigger take care of the future updates.

You can use the following code (extracted from the same trigger above) to update the data once. 
Map<Id, Decimal> mapAccountScore = new Map<Id, Decimal>();
List<Contact> lstContacts = [SELECT Id, AccountId, Contact_Score__c FROM Contact];

for (Contact objContact : lstContacts)//Query for Files
{    
	Decimal nAccountScore = 0;
	 if (mapAccountScore.containsKey(objContact.AccountId)) {
		nAccountScore = mapAccountScore.get(objContact.AccountId);
	 }
	 mapAccountScore.put(objContact.AccountId, nAccountScore + objContact.Contact_Score__c);
}

if(mapAccountScore.size() > 0){
	List<Account> lstAccounts = [SELECT Id, Account_Score__c FROM Account WHERE ID IN :mapAccountScore.keySet()];
	
	for (Account acct : lstAccounts) 
	{
		acct.Account_Score__c = mapAccountScore.get(acct.Id);
	}
	update lstAccounts;
}
You can run this code in "Developer Console" - "Anonymous Window".

You need to consider your data volume here.

For example, line 2 of the above code will try to fetch all the Contacts in your ORG and update the Account Score for them. If you have 10K or 20K records, this will work without any issues.

If you have a lot more than that, you need to figure out a way to do it batches. You may like to try something like this. 
List<Contact> lstContacts = [SELECT Id, AccountId, Contact_Score__c FROM Contact WHERE Account.Name LIKE 'A%'];
The above query will return all the Contacts whose Account Name starts with 'A', and so on.

Idea is to bring down the Query output size so that you will not face any issues in UPDATING the accounts.

Hope this helps.

Let me know if you have more queries.
 
ChristiChristi
Thanks LBK. Before I implement this, however, I need to figure out why the first trigger validation test failed. Can you help me?
I used the default test options to run my validation test which resulted in failure due to
"Code Coverage Failure - Your code coverage is 64%. You need at least 75% coverage to complete this deployment."
Screenshot of errors

Can you help me understand where the problem lies? This would help me immensely. Thanks a lot.

 
LBKLBK
Have you written a test class for this trigger already?

You need to have one to improve the code coverage.

If you do not have one already, try this. 
@isTest(seeAllData=false)
private class TestUpdateAccountScore  {
	static testMethod void updateAccountScoreTest() {
   	Test.startTest();
	
	Account objAccount = new Account (Name = 'Test Account', Account_Score__c = 0);
	insert objAccount;
 	 
	Contact objContact1 = new Contact(FirstName = 'TestF', LastName = 'TestL', AccountId = objAccount.Id, Contact_Score__c = 2);
	insert objContact1;
	
	Account objAccount1 = [SELECT Id, Account_Score__c FROM Account WHERE Id = :objAccount.Id];

   	// Verification
   	System.assertEquals(objAccount1.Account_Score__c, 2);
 
   	Test.stopTest();
	}
}
Let me know how it goes.
 
Lorena Vigil 5Lorena Vigil 5
Hello,

I am trying to achieve the same as above - return the sum total contact's score at the account level. I implemented this code in my sandbox but the account score is only reflecting the most current contact score instead of the sum of total contact scores. Any advice?