0

I am attempting to bring back a list of contacts per account, but I need the contacts with type of Billing to be returned before contacts of type Account Owner, then in addition to this I would like to return the billing contact who was last modified most recently. Is this possible with SOQL?

Current SOQL:

SELECT id, MS_Contact_Type__c, Account.id, Email, name 
FROM Contact 
WHERE MS_Contact_Type__c IN ('Billing', 'AO') AND Account.id IN: emailActionMap.keySet() 
ORDER BY lastModifiedDate Desc

MS_Contact_Type__c is a multiselect picklist.

I tried this as a workaround, but I'm not sure its working as expected.

//create contacts map
map<id, list<Contact>> accountIdToContactListMap = new map<id, list<Contact>>();
map<Id, Contact> contactMap = new map<id, Contact>();
list<Contact> contactsBilling = new list<Contact>();
list<Contact> contactsAO = new list<Contact>();
for(Contact c : [select id, MS_Contact_Type__c, Account.id, Email, name FROM Contact WHERE MS_Contact_Type__c IN ('Billing', 'AO') AND Account.id IN: emailActionMap.keySet() ORDER BY lastModifiedDate Desc]) {
    if(c.MS_Contact_Type__c.contains('Billing') ) {
        contactsBilling.add(c);
    } else {
        contactsAO.add(c);
    }
}

for(contact contact0 : contactsBilling) {
    if(!accountIdToContactListMap.containsKey(contact0.Account.id)) {
        accountIdToContactListMap.put(contact0.Account.id, new list<contact>{contact0});
        contactMap.put(contact0.id, contact0);
    } else{
        list<Contact> contactList = accountIdToContactListMap.get(contact0.Account.id);
        contactList.add(contact0);
        accountIdToContactListMap.put(contact0.Account.Id, contactList);
        contactMap.put(contact0.id, contact0);
    }
}

for(contact contact1 : contactsAO) {
    if(!accountIdToContactListMap.containsKey(contact1.Account.id)) {
        accountIdToContactListMap.put(contact1.Account.id, new list<contact>{contact1});
        contactMap.put(contact1.id, contact1);
    } else{
        list<Contact> contactList = accountIdToContactListMap.get(contact1.Account.id);
        contactList.add(contact1);
        accountIdToContactListMap.put(contact1.Account.Id, contactList);
        contactMap.put(contact1.id, contact1);
    }
}
  • 1
    Wait Type is multi-select? Could you take a moment to edit your post to use specific API Names and clearly indicate which field is which type? Also, do you want to sort alphabetically descending? Because that changes the syntax as well. – Adrian Larson Apr 2 at 19:00
  • Please elaborate on your description. As @Adrian Larson said, what is the field type / API name. Your question is 'Is this possible with SOQL?'. The answer is yes. Can you bring some context to your question? What have you tried, what is the SOQL query you need help with .... – Z33dawg Apr 2 at 19:09
  • It may actually not be possible. I seem to remember not being able to filter on a mult-select, but maybe I'm just mixing it up with Long Text Area (and Rich, obviously). – Adrian Larson Apr 2 at 19:17
  • 1
    You actually can filter on multi-select using INCLUDES, EXCLUDES but I don't think you can ORDER BY. Either way there are alternative solutions: Formula field or processing the list in apex. Hard to help until he clarifies his problem – Z33dawg Apr 2 at 19:23
  • I have edited my post to include what i attempted as well as field name/type. and Yes i would need to sort alphabetically descending so that billing type contacts are returned before the AO type. – Logie C Apr 2 at 20:08

Your Answer

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

Browse other questions tagged or ask your own question.