Upsert causes custom and standard Duplicate Rules to fire

Question

Could you help me understand why does my duplicate rule is firing upon insert?

This is the controller:

Boolean idExists = false;
List < Lead > leads = [SELECT Id, Name FROM Lead WHERE Email =: email OR Company =: companyName OR Name =: name LIMIT 1];

idExists = leads.isEmpty() ? false : true;

try {
    leads = createOrUpdateLead(leads, idExists, name, title, companyName, email, phone, companyId, companyRegNum, address);
} catch (Exception e) {

}

global static List < Lead > createOrUpdateLead(List < Lead > leads, Boolean idExists, String name, String title, String companyName, String email) {

    Lead l = new Lead(
        LastName = name,
        Title = title,
        Company = companyName,
        Email = email
    );
    if (idExists) {
        l.Id = leads[0].Id;
    }

    upsert l;

    leads.add(l);
    return leads;
}

The duplicate rule creteria is:

Lead: Email EXACT MatchBlank = FALSE

When the method is done executing, I get the following error:

Upsert failed. First exception on row 0 with id 00Q1l0000040kJFEAY; first error: DUPLICATES_DETECTED, This Lead is a duplicate. Please use the existing Lead: []

When I disable the rule and try again, I get another error (Standard Error I guess):

Upsert failed. First exception on row 0; first error: DUPLICATES_DETECTED, This Lead already exists as a Contact. Please use the existing Contact record.: []

Answer 1

There's no such thing as a "standard duplicate rule" in the sense that Salesforce would block the creation of a Lead based on a Contact email address. This was configured by an administrator.

Your problem could stem from the fact that you're using the OR operator in your query to get an existing record. Consider what could happen if you have two leads:

Id          Name       Company     Email
janeDoeId   Jane Doe   Acme, Inc.  [email protected]
bobSmithId  Bob Smith  Acme, Inc.  [email protected]

So, let's say someone was looking for Bob Smith at Acme, Inc, using the following inputs:

email = [email protected]
name = Bob Smith
companyName = Acme, Inc.

Using the OR operator means any one of the criteria has to match.

Let's run this query:

Results: [
  { Id: idForJaneDoe, Name: 'Jane Doe' },
  { Id: idForBobSmith, Name: 'Bob Smith' }
]

Why did this happen? We put in all the information for Bob, but as it turns out, the criteria also matched Jane! If you consult the truth table for your two records, we find that:

            -- Field Matches -- 
         | Email | Company | Name
---------+-------+---------+------
Jane Doe | false | true    | false
Bob Smith| true  | true    | true

Also, let's look at the OR truth table:

     | false | true
-----+-------+-----
false| false | true
 true| true  | true

Since OR matches when either value matches, Jane matches only because her Company name matched: false OR true OR false equals true.

At this point, assuming Jane got selected, we'd be trying to update the database so it appears as:

Id          Name       Company     Email
janeDoeId   Bob Smith  Acme, Inc.  [email protected]
bobSmithId  Bob Smith  Acme, Inc.  [email protected]

And this would result in the duplicate rule being correctly triggered!

You'll probably want to use either AND criteria to be more specific, or you'll want to perform at least some sort of sanity check to make sure you're not overwriting the wrong Lead.

In either case, it's probably a mistake to try and perform such a match automatically. The user (or some other user) should be given an opportunity to review the match to make sure that this is really an update or not.