0

I am trying to extract the email domain from each contact record from the email address field and use it to query for matching values on the account record.

How do I loop over a list of contact records and query a field on the account record while grabbing just the domain part of the email address from the email field on each given contact in the loop? This is the code I have so far:

for(Contact con : contacts) {
  if( con.email != null ) {
     contactList.add( con );
  }
}

for(Account acc : [SELECT Id, Name FROM Account WHERE account_email__c = 
 **contactemaildomain**]  
) {
  .. code ..
}

I am confused on how to use the values from my contactList in my query to the account.

  • You are trying to find the account that has same email as contact? – Pranay Jaiswal Nov 16 '18 at 17:54
3

No matter what you do, one major problem you're going to encounter is that your trigger will eventually start failing because of non-selective query issues. So, on the account itself, you need to create a field called "Email Domain", and copy the domain part of the email to this field.

It must either be a text field, or an indexed formula (requires Customer Support to enable), or you'll still have query issues later. Keep in mind you'll want to have a trigger/process builder/workflow rule on the account to populate the field, and you'll want to touch each record once to make sure the value is correctly updated. This is a one time process.


Presuming you have an email domain field on the account, your code will be incredibly simple, using the standard aggregate-query-update pattern:

// Aggregate
Set<String> domains = new Set<String>();
for(Contact record: contacts) {
  if(record.email != null) {
    domains.add(record.email.split('@')[1]);
  }
}
// Query
Map<String, Id> accountsByDomain = new Map<String, Id>();
for(Account record: [select Email_Domain__c from account where Email_Domain__c = :domains]) {
  accountsByDomain.put(record.Email_Domain__c, record.Id);
}
// Update
for(Contact record: contacts) {
  if(record.email != null) {
    Id accountId = accountsByDomain.get(record.email.split('@')[1]);
    // ... If accountId is null, no match was found
  }
}
| improve this answer | |
  • Just out of curiosity , if we get formula field indexed, will it still be non-selective? – Pranay Jaiswal Nov 16 '18 at 18:11
  • 2
    @PranayJaiswal Interesting. I actually missed that in the notes, or perhaps I've forgotten. Yes, if you enable an index for the formula, you can then skip creating the text field/workflow/process, etc. – sfdcfox Nov 16 '18 at 18:15

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.