+ Start a Discussion
JimRaeJimRae 

Contact Duplicate Trigger Issue : System.QueryException: Non-selective query

I have created a Contact trigger that follows very closely to the cookbook Lead Duplicate Preventer code base.  It works fine and I have been able to deploy it to my production environment.  Now, I have some users that get the Query Exception error, and others that do not, even when adding the same new contact to the same account.  I can replicate this in both Dev and production, but can't figure out what the difference between users is, or what the user would have to do with the way the query executes.  I know the email field is not indexed, and that might be part of the problem, but am unclear as to why some users can execute successfully, and others get the error.  I hope that someone can help, and that this might be a good trigger that the entire SFDC Community can leverage.
Code is below!
Regards,
Jim Rae
 
 
Code:
TRIGGER CODE:

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

    Map<String, Contact> ContactMap = new Map<String, Contact>();

    for (Contact contact : System.Trigger.new) {    

        // Make sure we don't treat an email address that
        // isn't changing during an update as a duplicate.

        if ((Contact.Email != null) && (System.Trigger.isInsert || (Contact.Email != System.Trigger.oldMap.get(Contact.Id).Email))) {
    
            // Make sure another new Contact isn't also a duplicate

            if (ContactMap.containsKey(Contact.Email)) {
                String errortext = 'Another new Contact has the same email address. ('+Contact.Email+') Please search for the existing Contact record with this address and update as appropriate, or contact your Administrator for assistance';
                Contact.Email.addError(errorText);
            } else {
                ContactMap.put(Contact.Email, Contact);
            }
        }
    }

    // Using a single database query, find all the Contacts in
    // the database that have the same email address as any
    // of the Contacts being inserted or updated.
 for(String test:ContactMap.keySet()){
 system.debug('\n\nContact added to Map was:'+test);  //only here for testing purposes
 system.debug('\n\nUser running trigger is:'+UserInfo.getUserName());
 system.debug('\n\n'+UserInfo.getProfileId());
 }
    for (Contact contact : [SELECT Email FROM Contact WHERE isDeleted = false and Email != null and Email IN :ContactMap.KeySet()]) {
        SYstem.debug('In the test loop');
        Contact newContact = ContactMap.get(Contact.Email);
  String errortext = 'Another Contact has the same email address. ('+newContact.Email+')  Please search for the existing Contact record with this address and update as appropriate, or contact your Administrator for assistance';
        newContact.Email.addError(errorText);
    }

TEST CASE CODE:
public class testBlockDuplicateContactEmails {
 
 static testMethod void testDuplicateContactEmailTrigger(){  
 
  //Creating all records against "Acme Corporation - HQ" Account (00100000002qK3l)
  Contact[] smith1 = new Contact[]{
   new Contact(  Email='smith@acme.org', LastName='Smith', Accountid='00100000002qK3l' )
  };
  insert smith1;  // add a known Contact
  
  Contact[] smith2 =new Contact[]{
   new Contact(  Email='smith@acme.org', LastName='Smith', Accountid='00100000002qK3l' )
  };
  // try to add a matching lead
  try {
   insert smith2;
   } catch (System.DmlException e) { 
   String errortext1 = 'FIELD_CUSTOM_VALIDATION_EXCEPTION, Another Contact has the same email address';
   system.debug('INSERT ERROR MESSAGE IS:'+e.getMessage());
   system.assert(e.getMessage().contains(errortext1), e.getMessage());
  }
  
  // test duplicates in the same batch for bulk Contact loading or lead conversion
  Contact[] bulkcontacts =new Contact[]{
   new Contact(  Email='Johnson@acme.org', LastName='Johnson', Accountid='00100000002qK3l' ),
   new Contact(  Email='Johnson@acme.org', LastName='Johnson', Accountid='00100000002qK3l' )
  };  
  try {
   System.debug('\n\n Insert Test');
    insert bulkcontacts;
    } catch ( System.DmlException e) { 
   String errortext2 = 'FIELD_CUSTOM_VALIDATION_EXCEPTION, Another new Contact has the same email address';   
   system.assert(e.getMessage().contains(errortext2), e.getMessage());
  }
  
  // test update also
  Contact[] contactup = new Contact[]{
   new Contact(  Email='jones@acme.org',  LastName='Jones', Accountid='00100000002qK3l' )
  };
  System.debug('\n\n Update Test');
  insert contactup;
  Contact updatetest = [ select id,Email from Contact where Email = 'jones@acme.org' and Accountid='00100000002qK3l' limit 1];
  system.assert(updatetest!=null);
  updatetest.Email = 'smith@acme.org'; 
    
  try { update updatetest; } catch ( System.DmlException e) { 
   String errortext3 = 'FIELD_CUSTOM_VALIDATION_EXCEPTION, Another Contact has the same email address';
   system.debug('UPDATE ERROR MESSAGE IS:'+e.getMessage());
   system.assert(e.getMessage().contains(errortext3), e.getMessage()); 
  }
  // Try a null email address
  Contact[] contactnull = new Contact[]{
   new Contact(  LastName='Anderson', Accountid='00100000002qK3l' )
  };
  System.debug('\n\n Null Test');
  try{ insert contactnull;} catch( System.DmlException e){
   system.debug('\n\nNull Insert ERROR MESSAGE IS:'+e.getMessage());
  }
  // Try a null email address Update
  Contact[] contactnull2 = new Contact[]{
   new Contact(  LastName='Anderson', Accountid='00100000002qK3l' )
  };
  System.debug('\n\n Null Update Test');
  insert contactnull2;
  Contact updatetest2 = [ select id,Email from Contact where Email=null and LastName = 'Anderson' and Accountid='00100000002qK3l' limit 1];
  system.assert(updatetest!=null);
  updatetest2.FirstName = 'Hans'; 
    
  try { update updatetest2; } catch ( System.DmlException e) { 
   system.debug('UPDATE ERROR MESSAGE IS:'+e.getMessage());
    
  }
 }
}

 
TehNrdTehNrd
What line specifically? I'm going to guess this one...

Code:
    for (Contact contact : [SELECT Email FROM Contact WHERE isDeleted = false and Email != null and Email IN :ContactMap.KeySet()]) { 

I think there is some changes we can make to this that might make a difference. First, I don't think you need the condition isDeleted = false as I believe a query like this will only return non deleted values. The criteria "Email != null and Email IN :ContactMap.KeySet()" could also use some tweaking. You are only populated the map if the email is not null so you don't need to add that to your query.

Contact WHERE Email IN :ContactMap.KeySet()

Should do the trick. Whether this will fix your issues I'm not sure but it is worth a try.


Message Edited by TehNrd on 07-28-2008 02:33 PM
JimRaeJimRae
You are correct on the line, that is where the error occurs.  I did have the exact query you referenced, but found some other posts suggesting that adding those additional filter criteria would help.  I didn't really think they would, especially the Email !=null, since that is forced with the map creation.
If you think of anything else, please pass it along.
Jim
CTU007CTU007
Hi, Jim, have you solved this issue?
JimRaeJimRae
Unfortunately, we have too many records in our instance for this to work.  Ultimately, we are looking at CRMFusions new Dupeblocker as a solution. 
MycodexMycodex
What would constitute "too many records"? I have 5,000 contacts in our instance and was going to do the same thing. I actually wrote the trigger in a sandbox and it works pretty well. Does CRMFusions load the contacts into their database and run the checks there?

I think I might just create a workflow to update a custom email field and make that unique like the cookbook states as a workaround. I'll then put in a validation to make sure the @ symbol is in there.
JimRaeJimRae

Mycodex wrote:
What would constitute "too many records"? I have 5,000 contacts in our instance and was going to do the same thing. I actually wrote the trigger in a sandbox and it works pretty well. Does CRMFusions load the contacts into their database and run the checks there?

I think I might just create a workflow to update a custom email field and make that unique like the cookbook states as a workaround. I'll then put in a validation to make sure the @ symbol is in there.


We have about 500,000 contacts in our production instance. The CRMFusion solution creates custom objects including a binary index of some type for the check, all done in our instance.  Dupeblocker is working great.

MycodexMycodex
Thanks for the information Jim. I'll use the trigger until someone screams (small user base).
CwestonrCwestonr

I wonder if the following would be a probelm:

 

 

Integer match1 = [SELECT count() FROM Contact WHERE (Owner.Profile.Name like :Affiliate AND Affiliate__c != '') AND (FirstName = :c.FirstName AND LastName = :c.LastName) AND ( ((MobilePhone = :c.MobilePhone OR MobilePhone = :c.HomePhone OR MobilePhone = :c.Phone OR MobilePhone = :c.OtherPhone OR MobilePhone = :c.Home_Phone_2__c )AND MobilePhone != '') OR ((Phone = :c.Phone OR Phone = :c.HomePhone OR Phone = :c.OtherPhone OR Phone = :c.Home_Phone_2__c )AND Phone != '') OR ((OtherPhone = :c.OtherPhone OR OtherPhone = :c.HomePhone OR OtherPhone = :c.Home_Phone_2__c )AND OtherPhone != '') OR ((Home_Phone_2__c = :c.Home_Phone_2__c OR Home_Phone_2__c = :c.HomePhone )AND Home_Phone_2__c != '') OR ((HomePhone = :c.HomePhone )AND HomePhone != '') OR ((Birthdate = :c.Birthdate )AND Birthdate != NULL) ) LIMIT 1];

 would this get past the record limit? we only care if there is a duplicate so count is limited to 1 and we are not getting records just a count??

 

AcMEGXAcMEGX

Hi, 

 

I know this post is old however I would like to try something. Can you try adding a workflow (that will fire everytime the record is created/updated) to copy 1 of your criteria (email/phone/etc) to a custom indexed field. Use that indexed field as part of your query to avoid the Non-selective query error