+ Start a Discussion
alxalx 

Contact Counting Batch Update

Hey guys,

 

I found this wonderful scheduled batch update code on the forums:

 

global class countContacts implements Database.Batchable<sObject>{
	global final String gstrQuery = 'select ID, Number_of_Contacts_w_Email__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.Number_of_Contacts_w_Email__c){
			    	objAccount.Number_of_Contacts_w_Email__c = intNumberOfContacts;
					listAccount.add(objAccount);
			  	 }
		       }	         
       if (listAccount.size() > 0 ) {update listAccount;}
    }
    	global void finish(Database.BatchableContext BC){
    }
}

 

 

 

The field Number_of_Contacts_w_Email__c counts all the contacts in an account. But as the variable name implies, i would like it to count only contacts with an email address. I know that count() can take in a fieldname, but the returned AggregateResult is not an integer?

 

Anyone have any tips on how i can count contacts only under certain conditions like email, or if its a reference contact?

 

Also: Does anyone know how this code would run with like 50k accounts?

 

Thanks!

 

edit: link to my code - http://community.salesforce.com/t5/Apex-Code-Development/Update-Contact-Count-for-Accounts/m-p/173038

ScoobieScoobie

Use where clauses to filter the record you want to report on e.g. where email <> ''

 

Additionally, you have a SELECT statement inside your for loop, you definitely want to refactor this to take that out or the governor limits will blow with large batches.

 

What exactly is your business requirement? I'm not sure I understand what you are trying to achieve.

Pradeep_NavatarPradeep_Navatar

Since 50k records hits the governor limit and there is no direct way to get the count of bulk records you can create a custom field of type number in Account and increment/decrement its value based on the criteria using trigger.

 

Hope this helps.

alxalx

I basically need fields that show how many contacts are in each account based on certain criteria. Criteria like, count contact only if it has an email address, or count contact only if it is a reference contact.

 

Right now i've scheduled a batch update that takes in 200 accounts at once. Would using the SELECT method still hit limits?

 

 

alxalx

Could someone also explain what the WHERE clause means?

 

WHERE AccountID = :objAccount.ID

 

 

Pradeep_NavatarPradeep_Navatar

The SELECT query will hit the Governor limit if you have a huge number of records in Account. Try this logic :

 

On creation/edition/deletion of Contact record check

If(this Contact has email address || this Contact is a reference contact)

{

                Update ‘Contact_Count__c’ field, in Account, by increment 1 or decrement 1. (There will be only one Account you have to update as Contact is linked with a single Account)

}

 

About WHERE  clause

WHERE AccountID = :objAccount.ID

Denotes if AccountId(Account record id in Contact) == Account record id in current Account record.

 

Hope this helps.

alxalx

Navatar,

 

Are you saying i should just use a trigger?

 

Also, what exactly is the Governor limit for SELECT? I dont think my accounts will have more than 50 contacts at most.