I created a trigger on account object, that needs to populate a number field called 'ContactPhones'.

The input of ContactPhones, must be equal to the related contacts of the inserted account, with the 'Phone' not empty.

I made something like:

for(Account acct : Trigger.new){
        List<Account> Phones = [Select name, id, (Select name, id, Phone from Contacts) from Account where Id = :acct.Id];
        acct.ContactPhones   = Phones.size();

Where am I doing wrong?

The list is not being populated, always ends with a value of 0.

And I know that this SOQL shouldn't be inside the for loop, but I'm struggling to find out how to make it outside of it. Any help with this will be much appreciated, also.

Thanks in advance.

  • Does this trigger run before insert? Because at that moment there won't be any account in the database yet to query (it hasn't been inserted yet), and there won't be any related contacts to query either. If it is before insert the acct.Id will also be null (the Id field isn't populated till after insert). – Kasper Oct 17 at 3:06
  • @Kasper, after insert probably also won't have Contacts, since it's just insert – ytiq Oct 17 at 5:18
  • @Kasper, indeed it is before insert. – Growred Oct 17 at 19:02

By far the best way to do this is to use an aggregate query. Doing this in before update of Account will collect the number of Contacts with the Phone field set to a value:

Set<Id> ids = Trigger.newMap.keySet();

List<AggregateResult> results = [
    SELECT COUNT(Id) contacts, AccountId account
        FROM Contact
        WHERE AccountId IN :ids AND Phone != NULL
        GROUP BY AccountId

for (AggregateResult result : results) {
    Trigger.newMap.get(result.get('account')).ContactPhones__c = (Integer) result.get('contacts');

Note, however, that this processing will not be invoked when a Contact with a Phone number and AccountId is added or deleted or when a Contact is updated to change Phone or AccountId, so I suggest you actually take a different approach where you do this processing in the Contact trigger and incrementally manage the count when one of these scenarios happens.

| improve this answer | |
  • I think I would alias using accountId rather than account for clarity – cropredy Oct 17 at 17:21
  • Thanks for the help guys!! – Growred Oct 17 at 19:02

I guess you can do something like this

// added filter in inside query for phone, using set of acc ids from Trigger map
List<Account> accsWithContacts = new Map<Id, Account>([
  SELECT name, id, (Select name, id, Phone from Contacts WHERE Phone != NULL) 
  FROM Account 
  WHERE Id = :Trigger.newMap.keyset()

// using map to get correct record
for(Account acct : Trigger.new){
        acct.ContactPhones   = accsWithContacts.get(acct.Id).Contacts.size();

In order to make this work you also need to use before update

| improve this answer | |
  • is already in before insert! Thanks for the help! – Growred Oct 17 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.