+ Start a Discussion

HELP! How to add multiple conditions in a where clause?

Contact Duplicate Trigger:


I've got the general idea on this, but my multiple conditions for the where clause are not working.  I need it to throw the error either if the email address matches or if the first name, last name, and one of the phone numbers match.  Parentheses don't seem to be helping drive the logic.  Who can give me some guidance here?


When I use this code it throws the error if the first and last name match regardless of whether there is a match on one of the phone numbers.  But I want it to only throw the error if a the full name and one of the phone numbers match an existing contact record.


trigger ContactDuplicateTrigger on Contact (before insert) {
for (Contact c : Trigger.new){
Contact[] contacts= [select id from Contact where
    ((FirstName = :c.FirstName and LastName = :c.LastName) and
    (Phone=:c.Phone or MobilePhone=:c.MobilePhone or HomePhone=:c.HomePhone or OtherPhone=:c.OtherPhone or Home_Phone_2__c=:c.Home_Phone_2__c)
)    or Email = :c.Email];
if (contacts.size() > 0) {
c.LastName.addError('Contact cannot be created - Contact already exists with the same email or name-phone combination.');


I think your query is matching on the null phone numbers, giving you false positives.


You'll most like want to move the SOQL query out of your loop to be governor friendly -- otherwise you'll crash when there are more than 20 contacts inserted in a batch and possibly sooner if other triggers are firing. 


You might try using a single query to build a map of the possibly matching contacts (e.g. with first name, last name matching and at least one of the phone numbers is non-null) and use first name + last name + a phone number as the keys of the map.


This requires 1 SOQL query and three loops:


(1) Loop through trigger.new to collect up the names and numbers

(2) Loop through results of a SOQL query searching for name matches and number matches & build the map

(3) Loop through trigger.new again to check for duplicates using the map.


It can be hard or impossible to express your matching criteria precisely in a SOQL query for (2) -- you might find it helps to build formula fields on the Contact to help make the SOQL logic more accurate.


Things to consider:


* Phone numbers can be hard to match against due to formatting issues.


* You'll have to keep an eye on how many contacts you might be processing in this scenario, as you could run into other limits.


* You might want to watch out for duplicates among the new contacts being inserted -- this can be a more common source of duplicates that one might expect (e.g. a data loader file accidentally containing two of every record!)


Hope this helps,

Think & Enjoy -- JoeK