0

I have below query to search for Existing Contact before creating new Contact in Salesforce. If there is a Contact with matching email and another Contact with matching Phone - results are not consistent- Sometimes it picks up with matching phone and sometimes with matching email.

How to make sure it always returns first Contact with matching email then with matching phone then by matching name and street? We have close to 450K Contacts.Will it effect Performance?

    list<Contact> ExistingContact = [SELECT
            id, firstname, lastname, email, Phone, MobilePhone, MailingStreet
        FROM contact 
        WHERE (email=: con.Email AND  email !='') 
            OR (Phone=:con.Phone AND Phone !='')
            OR (MobilePhone=:con.Phone AND MobilePhone !='')
            OR (firstname =:con.FirstName AND lastname =:con.LastName AND MailingStreet =:con.street) 
        LIMIT 1];
Improve this question

2 Answers 2

Reset to default
1

You could assure, that the prevoius did not match. I mean this makes it pretty complex, but it should fullfill your needs:

WHERE (email = :con.Email AND  email !='') 
OR ( !(email = :con.Email AND  email !='') AND  (Phone = :con.Phone AND Phone !='') )
OR ( !(email = :con.Email AND  email !='') AND !(Phone = :con.Phone AND Phone !='') AND  (MobilePhone = :con.Phone AND MobilePhone !='') )
OR ( !(email = :con.Email AND  email !='') AND !(Phone = :con.Phone AND Phone !='') AND !(MobilePhone = :con.Phone AND MobilePhone !='') AND (firstname = :con.FirstName AND lastname = :con.LastName AND MailingStreet =:con.street) )
Improve this answer
4
  • 1
    Such complex query might be killing the engine
    – Pranay Jaiswal
    Commented Nov 13, 2017 at 17:52
  • @PranayJaiswal According to the documentation it can handle 4000 characters in the where clause, so I think it can also handle a couple expression.
    – itsmebasti
    Commented Nov 13, 2017 at 17:58
  • 1
    But OR clauses torch performance and selectivity. It has nothing to do with character count.
    – Adrian Larson ♦
    Commented Nov 13, 2017 at 18:03
  • we have close to 450K Contacts and will it effect performance ?
    – sfdc
    Commented Nov 13, 2017 at 18:48
0

IMHO use click rather than code. Go for matching rules and duplicate rules. It will provide a list of potential duplicates as well. Refer to this link

Improve this answer
3
  • Matching rules/Duplicate rules might not work for me as I need it for force.com site page.
    – sfdc
    Commented Nov 13, 2017 at 18:31
  • I believe the matching rules still work on force.com site page as they are triggered on create and edit. The only difference would be that you would have to use exception handling and fetch the error in try catch block to display it. The validation rules will move to matching rules and the error handling will remain in your code. That ways you could easily change rules easily without making changes to code
    – Sf_Noob
    Commented Nov 13, 2017 at 18:34
  • I want to capture matching contact and update in salesforce.I don't want to show error to user
    – sfdc
    Commented Nov 13, 2017 at 18:38

You must log in to answer this question.

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