1

I have the following trigger:

trigger updateAccount on Contact (before insert, before update){

    Set<String> emailDomain = new Set<String>();
    Map<String,Id> accountMap = new Map<String,ID>();

    for(Contact con : trigger.new) {
        if(Con.AccountID == null && con.CntEmail_Domain__c !=null){
            emailDomain.add(con.CntEmail_Domain__c);
        }
    }

        for(contact ac : [Select id,Name,AccountId,CntEmail_Domain__c from Contact where CntEmail_Domain__c in : emaildomain]){
            accountMap.put(ac.CntEmail_Domain__c,ac.AccountId);
        }

        for(Contact con : Trigger.New){
            if((Con.AccountId == null) && (Con.CntEmail_Domain__c !=null)) {
                Con.AccountId = accountMap.get(Con.CntEmail_Domain__c);
            }
        }
}

It's working properly in Sandbox, so I deployed it into my production.

Now when I uploading new Contacts through data wizard or data loader I am getting the following error:

CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY:updateAccount: execution of BeforeInsert

caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

Even if a field is indexed a filter might still not be selective when:

  1. The filter value includes null (for instance binding with a list that contains null)

  2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)

():--

2
  • Is CntEmail_Domain__c a formula?
    – sfdcfox
    Apr 13, 2016 at 17:02
  • yes it is, here's the formula: IF(NOT(ISBLANK(Email)),RIGHT(Email,LEN(Email)-FIND('@',Email)),null)
    – B.Wayne
    Apr 13, 2016 at 17:15

1 Answer 1

2

Filtering against a formula causes a full table scan of the database. Instead, you should strongly consider making this a text field so you can index the results. You can do that in your trigger (before insert, before update) so that you can find these domains:

for(Contact record: Trigger.new) {
    record.EmailDomain__c = record.Email == null?
        null: record.Email.split('@')[1];
}

You may need to apply the External ID attribute to the field to allow searching, or you can request a custom index by salesforce.com technical support. Also, reading Working with Very Large SOQL may be of some use to you.


Example Code:

trigger updateContactsAccount on Contact (before insert, before update) {
    // Calculate domains
    for(Contact record: Trigger.new) {
        record.Domain__c = record.Email == null? null: record.Email.split('@')[1];
    }
    // The following code represents a standard "Aggregate Query Update" pattern

    // Part 1: Aggregate results to query
    Map<String, Id> domains = new Map<String, Id>();
    // Find domains in current trigger context with matching account
    for(Contact record: Trigger.new) {
        if(record.Domain__c != null) {
            // It's okay if there's no account yet, we'll fix this later.
            domains.put(record.Domain__c, record.AccountId);
        }
    }
    // Part 2: Query the database for records we're interested in
    // Find contacts outside of trigger context with matching account
    for(Contact record: [SELECT AccountId, Domain__c FROM Contact WHERE Domain__c IN :domains.keySet() AND AccountId <> NULL]) {
        // We'll prefer accounts already in the database for this domain, but ignore records already in this transaction
        if(Trigger.isInsert || !Trigger.newMap.containsKey(record.Id)) {
            domains.put(record.Domain__c, record.AccountId);
        }
    }
    // Part 3: Update the records with the queried information
    // Match contacts to the domain
    for(Contact record: Trigger.new) {
        if(domains.containsKey(record.Domain__c)) {
            record.AccountId = domains.get(record.Domain__c);
        }
    }
}
6
  • The reason I have it as a formula field, so that it captures the domain of the email address and inserts it in that custom field. the Code you provided will do that as well?
    – B.Wayne
    Apr 13, 2016 at 17:31
  • @B.Wayne Yes, just create a new field, and put this in your before insert/before update triggers. Just remember that in before triggers, the data hasn't been saved, so you will probably also want to check other contacts in the same trigger context for the same domain name(s) before checking the rest of the database.
    – sfdcfox
    Apr 13, 2016 at 17:33
  • hey would you be able to tell me how the code will look once I add your portion there? for the Contacts I already have in SFDC within Account names, can I manual update this new field (let's call it Domain); and once I upload these new contacts; it'll match up with those contacts then fill in the Account name?
    – B.Wayne
    Apr 13, 2016 at 19:18
  • @B.Wayne It'll look a lot like what I just updated my answer with. I tested this in my org. Be aware, though, that massive domains (e.g. gmail.com, live.com, etc) should be black-listed to avoid the middle query from blowing up, as there's currently no safeguard against having too many of a domain in your contacts table.
    – sfdcfox
    Apr 13, 2016 at 19:43
  • I tried that code; again worked in Sandbox, once I deployed it to Production, my Team was unable to update any objects. Is there any other way to bypass the error with my original code?
    – B.Wayne
    Apr 13, 2016 at 20:25

You must log in to answer this question.

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