0

I'm trying to count the number of contacts at an account with a specific status.

So if any contacts at the account have a status of 'A', that should set the Account Stage (on the account) to 'A Working'. Whereas if any have a status of 'B', it should set the Account Stage to 'B Working'. Otherwise, the Account Stage should be set to 'Not Working'.

This works when I've used two separate aggregate queries, but I'd prefer to only use one query. To do this, I've tried to use a map containing the contact statuses but it isn't working because I'm not sure how to relate the map to the account?

   global without sharing class contactTriggerHandler {
        Public static void Method(List < contact > newcontact) {
            String Owner;
            String AssignedTo;
            String Type;
            Set < Id > accountIds = new Set < Id > ();
            
            
            for (contact con: newcontact) {
                accountIds.add(con.accountid);
                Type = con.Account_Type__c;  
            }
            
            List<contact> contacts = [
                SELECT
                Id,status__c,accountid
                FROM contact
                WHERE accountid in :accountIds
            ];
            
            Map<String, List<contact>> mapstatusToAccountList = new Map<String, List<contact>>();
            Map <Id, Account> lstAccountsToUpdate = new Map <Id, Account> ();
            
            for(contact contactstocount : contacts){
                if(mapstatusToAccountList.keyset().contains(contactstocount.status__c)){
                    mapstatusToAccountList.get(contactstocount.status__c).add(contactstocount);
                } else {
                    mapstatusToAccountList.put(contactstocount.status__c, 
New List<contact>{contactstocount});
                }
            } 
    

next part:

      for (Account acc: [
            SELECT Id, Name,Account_Stage__c
            from Account 
            where Id IN: accountIds FOR UPDATE
        ]) {
            Account accObj;
            if (lstAccountsToUpdate.containsKey(acc.Id)) {
                accObj = lstAccountsToUpdate.get(acc.Id);
            } else {
                accObj = new Account(Id = acc.Id);
                lstAccountsToUpdate.put(acc.Id, accObj);
            }
            
            if (mapstatusToAccountList.get('Assigned A').size() > 1 ) { 
                accObj.Account_Stage__c = 'A Working';
            }  
            if (mapstatusToAccountList.get('Assigned B').size() > 1 ) { 
                accObj.Account_Stage__c = 'B Working';
            }  
            else{
                accObj.Account_Stage__c = 'Not Working';
            }
        }
        
        if (lstAccountsToUpdate.size() >0) {
            UPDATE lstAccountsToUpdate.values();    
        } 
    }
}
1
  • You should (almost) never use the global access modifier. If you actually need it, you will know you need it and why.
    – Adrian Larson
    Jul 29, 2022 at 18:55

1 Answer 1

1

Answering the question as asked

You just need an additional layer of nesting in your collection. Instead of a Map<String, List<contact>>, you could use a Map<Id, Map<String, List<contact>>>

To populate that, you'd mostly just need to add in an additional check to see if the Account Id already exists in the keyset of the outmost map.

From

for(contact contactstocount : contacts){
    if(mapstatusToAccountList.keyset().contains(contactstocount.status__c)){
        mapstatusToAccountList.get(contactstocount.status__c).add(contactstocount);
    } else {
        mapstatusToAccountList.put(contactstocount.status__c, new List<contact>{contactstocount});
    }
}

to

for(contact contactstocount : contacts){
    if(!accountIdToStatusToListContactMap.containsKey(contactstocount.AccountId)){
        accountIdToStatusToListContactMap.put(contactstocount.AccountId, new Map<String, List<contact>>());
    }

    // The outer map is guaranteed to have a value for the key at this point
    Map<String, List<Contact>> mapstatusToAccountList = accountIdToStatusToListContactMap.get(contactstocount.AccountId);

    // If you're not looking to use set methods, you can just use .containsKey()
    if(mapstatusToAccountList.containsKey(contactstocount.status__c)){
        mapstatusToAccountList.get(contactstocount.status__c).add(contactstocount);
    } else {
        mapstatusToAccountList.put(contactstocount.status__c, new List<contact>{contactstocount});
    }
}

I think there's a better way

You mentioned using aggregate queries, but you haven't actually used an aggregate query here. To avoid confusion:

  • Salesforce uses the term "aggregate query" to represent parent-child subqueries (and semi-joins and anti-joins, I believe)
  • You can use all of the Aggregate Functions when you use GROUP BY, and using GROUP BY will cause the query to return a List<AggregateResult>

Assuming that you really do need the row locking for Accounts, and that status__c is a picklist, you can make your life simpler by actually using a parent-child subquery here.

The idea is that you can ORDER BY a picklist and LIMIT 1. If your picklist values are in an appropriate order, you only need a single Contact to make your determination. If there are any 'B' status records, that's the one you'll get. If there are no 'B' status records, but there is an 'A' status record, that's the one you'll get.

List<Account> accountsToUpdate = new List<Account>();

// Making use of both a parent-child subquery AND a semi-join to keep things in a single
//   query (with little/no need to extract the AccountIds beforehand)
// This also removes the need to manually tie Contacts to Accounts (the query does that for us)
for(Account acctWithContact :[SELECT Id, (SELECT Id, Status__c FROM Contacts ORDER BY Status__c DESC LIMIT 1) FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Id IN :contactsList)]){

    // Since we're working on Contacts related to an Account, it's safe to say that
    //   the embedded List<Contact> will never be empty (so we don't need to check that).
    // Also, accessing the embedded list directly is usually unsafe (you normally
    //   want to use a nested loop to access it); but since we're limiting to a single
    //   child record, it is safe to access directly
    switch on acctWithContact.Contacts[0].Status__c {
        when 'B' {
            acctWithContact.Account_Stage__c = 'B Working';
        }
        when 'A' {
            acctWithContact.Account_Stage__c = 'A Working';
        }
        when else {
            acctWithContact.Account_Stage__c = 'Not Working';
        }
    };
    
    accountsToUpdate.add(acctWithContact);
}

update accountsToUpdate;
3
  • Hi @Derek F, thanks for your help. Do you know why the map might not get populated during a batch update? While testing this, it seemed to work during a UI update, but when trying dataloader with a batch >1, the map was null.
    – NJW
    Jul 28, 2022 at 19:33
  • @NJW Not off the top of my head, unless you're inserting Contact records without any AccountId (or related to Account Ids that aren't in the org you're using DataLoader in). Also, I do recommend the second approach over the one with the maps.
    – Derek F
    Jul 28, 2022 at 23:08
  • I appreciate the alternative way, I'll definitely try it. I'd like to understand why the first method doesn't work for bulk updates though. To clarify, I did use aggregate queries initially but got rid of them to figure out this other way. With this, should the inner map relate to the account somehow? I know the outer map does, but then that doesn't contain the other elements.
    – NJW
    Jul 29, 2022 at 13:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .