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 '16 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 '16 at 17:15
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 '16 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 '16 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 '16 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 '16 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 '16 at 20:25

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.