+ Start a Discussion
BrendaFlynnBrendaFlynn 

Update Contact Count for Accounts

This is my first week working with Salesforce, coming from a Coldfusion (don't be a hater!) and SQL Server background. 

 

Our users want to be able to see the number of Contacts for an account at a glance. To accommodate this, we've developed a custom field -- Account.Contact_Count__c which is an integer. Now for the hard part; populating it. We can't do a rollup because you can't roll up child data. So I wrote this bulk updater to do the work. I scheduled it (successfully) in the hopes that would get around the data governance limits, but no such luck.

 

We have thousands of accounts, and I won't know if they require updating unless I run a query on them. Is there a good way to run this in a background at low demand times? Is there a way that I can run it in blocks of data? Is there some super-duper easy way of running this that I simply didn't know about? Can I have this class call another class in order to reset the count of transactions?  Also, please note that as far as I can tell you can't combine count() with sub queries. I tried to make variations on this query work a number of different ways:

 

Select a.Contact_Count__C, count(Select ID From Contact Where AccountID = a.ID)

From Account a

 

I never got it to work. 

 

I'm currently orders of magnitude away from being able to execute this code. Help me understand that right way to accomplish a task like this in salesforce!

 

 I've run this both on-demand (so I can see errors) and scheduled. 

global class ModifyContractCount {

//completeRecount updates all records with the latest count of contacts

//the @future makes this available to be scheduled

@future(callout=true)

public static void completeRecount()

{ //Identify the number of contacts for a particular account and update the Contact Count variable appropriately //Create list of records to update.

List<Account> AcctsToUpdate = [ SELECT A.ID, A.Contact_Count__c FROM Account A LIMIT 50 //NOTE: Works with 50 or fewer ];

//Loop through returned records

for(Account a : AcctsToUpdate)

{

//Identify number of child contacts

Integer NumberOfContacts =

[ SELECT count()FROM Contact WHERE AccountID = :a.Id

];

//If the Accounts.Contact_Count__c is not the same as the number of child contacts, update the record

if (NumberOfContacts != a.Contact_Count__c)

{

a.Contact_Count__c = NumberOfContacts; update a; } //end for loop } //end completeRecount method }

];

//If the Accounts.Contact_Count__c is not the same as the number of child contacts, update the record

if (NumberOfContacts != a.Contact_Count__c)

{a.Contact_Count__c = NumberOfContacts;

update a;}

//end for loop

}

//end completeRecount method }

 

 

Message Edited by BrendaFlynn on 03-05-2010 10:24 AM
AlexiAlexi

I too have the same problem...


I tried to add an auto number to the contact field and hoped that I'd be able to do a Roll-Up Summary and count the contacts for each account, but no joy...!


Its seems quite strange that people have been requesting this function since 2008 and its still hasnt been addressed by SF.


Their typical reply is always you'll have to find a work arround, but this take quite some time and puts projects on the back foot. If anyone can help with this problem please let us both know ASAP.


Thanks.

Trevor0603Trevor0603

Guys,

 

The solution to this is to use both the new "scheduler" & Batch Job facilities.

 

The Batch Apex will give you the facility to traverse the accounts by a defineable row scope within hitting the governor limits.

 

I have created the two class which you would need to acheive this

 

1. Class which allows the update jobs to be schedules (can be done through the Setup->App Setup->Develop->Apex Classes ->Schedule Apex Button

global class AccountContactCountUpdate implements Schedulable{
 global void execute(SchedulableContext ctx)
    {
  id batchinstanceid = database.executeBatch(new setAccountContactCount(), 200);
    }
}

 

2. Class which does the "heavy" lifting in terms of the update - In passing 200 to execute batch it will do a batch of 200 accounts at a time - Here is the code to update the accounts.

 

global class setAccountContactCount implements Database.Batchable<sObject>{
 global final String gstrQuery = 'select ID, Contact_Count__C from Account';

    global Database.QueryLocator start(Database.BatchableContext BC){
     return Database.getQueryLocator(gstrQuery);
    }

    global void execute(Database.BatchableContext BC, List<sObject> scope){
  List<Account> listAccount = new List<Account>();
       for(SObject objSObject : scope){
      Account objAccount = (Account)objSObject;
      Integer intNumberOfContacts = [SELECT count()FROM Contact WHERE AccountID = :objAccount.ID];  
   if (intNumberOfContacts <> objAccount.Contact_Count__C)
   {
    objAccount.Contact_Count__C = intNumberOfContacts;
       listAccount.add(objAccount);
   }
       }     
       if (listAccount.size() > 0 ) {update listAccount;}
    }

    global void finish(Database.BatchableContext BC){

    }
}

 

 

See how this goes

 

Regards,

 

Trevor Miller

David81David81

This thread might be of some use to you as it deals with Task counts on Leads and Contacts.

 

http://community.salesforce.com/t5/Apex-Code-Development/Show-Open-Task-Count-on-Lead-Detail-Page-Bulk-Trigger/td-p/172463

 

Could easily be adapted to Contact counts on the Account object.

thunksalotthunksalot

Thanks so much for this, Trevor.  I'm curious what scheduler options you recommend for this?  I've scheduled it to run every morning.  

 

(I sure hope SF implements roll-up summaries for the Account>Contacts relationship soon because that seems quite a bit more efficient than running this on thousands of accounts every single day.  Oh well.)

Chad ThroppChad Thropp

You wouldn't happen to have an example of a test class for this? I've never written a test class in Apex before and I know you need at least 75% code coverage to try out the logic.

DrBenDrBen

Still nothing on this? Crazy!

In account view.. how many contacts are in that account? So simple. Has it for opportunities....

 

C'mon SF... let me know when this is done.

I am a manager user (CEO) - the very thought of writing code/script is anathema. 

 

Cheers

jen.nelsonDEV@configero.comjen.nelsonDEV@configero.com

Hello, All - thought the second one on this list might be of help.  Cheers

 

Configero Unveils New Grid Technology and Releases 2 New Apps on the App Exchange!

Configero Data Loader for Salesforce (http://appexchange.salesforce.com/listingDetail?listingId=a0N30000009wRkqEAE)
100% native data loader that is OS-agnostic, SFDC Edition-agnostic (*Group through Unlimited*), allows reference id matching on insert and update and includes an actionable mini-grid preview screen prior to commit

Configero Mini-grid for Salesforce Related Lists (http://appexchange.salesforce.com/listingDetail?listingId=a0N30000009wSwvEAE)
100% native "mini" version of the grid interface designed specifically for Related Lists; brings multi-column queries, multi-column sorts, bulk edits and user-specific customizations into related lists including those not traditionally customizable like Attachments or Tasks