Overcoming the Limitations of CRM’s Duplicate Checking Functionality

Microsoft CRM comes with a real time duplicate checking engine that warns users of potential duplicates as they enter new records.  Sounds good doesn’t it?  However, it’s not a comprehensive solution and has a few weaknesses especially when you start dealing with large numbers of customer records where the likes of good old John Smith starts to pop up a lot.   But with a bit of targeted customisation you can get it working for you.

Let’s start with some requirements.  Say we want a duplicate check to run as new Contacts are entered which looks for any existing contacts with the same name and email address.   Sounds useful.   And on the surface it looks like you can configure this.   Here’s what you would logically configure in CRM to try and address this requirement:


Now if you publish this rule and then push through a test you will find the duplicate checker doesn’t report a suspected duplicate when you expect it to.

The first thing to check is whether CRM has completed the creation of match code for this rule.  This shows up under System Jobs in the Settings area, look to see if a MatchCode job has run since you made the change, and check it has a status of Completed:

What CRM is doing here is its creating a match code for each Contact in the database based on the rule you defined.  In this case the match code string will be “<contact.fullname><contact.emailaddress>”.   The duplicate checker is then able to check against this one field to find duplicates.  CRM actually creates a new SQL table to house this matching data.

So, you need to let CRM complete that step, then you can retry your test.   But, again you will find the duplicate checker doesn’t appear to be working.

The problem appears to be an issue of timing.  The duplicate checker appears to be reading the ‘”Full Name” of the Contact being entered before CRM has actually calculated what that is (based on the First Name and Last Name fields that were entered). 

The workaround to this is to have the duplicate rule refer to the First Name and Last Name fields instead, e.g..:

Again, wait for CRM to rebuild it’s match code table, and then retry your test. 

Now, you should see the duplicate warning starting to appear:

I mentioned customisation earlier and we haven’t had to do any yet, and yet it looks like we have this working now.   One problem.   Try entering a new Contact with the same name as an existing contact where neither of the Contacts has an email address.   In this instance the duplicate checking engine will consider this to be a match.   So, in the absence of email addresses we are now matching on name alone.   If dealing with a lot of customers then this will result in a lot of false positives being reported, which frustrates users, who start to pay less attention to the duplicate detection dialog box.

The reason CRM is getting this wrong is because of it’s match code approach.   Imagine these contacts exist in your CRM:

Full Name Email Address Match Code
John Smith johnsmith@abc.com JohnSmithjohnsmith@abc.com
John Smith johns@xyz.co.nz JohnSmithjohns@xyz.co.nz
John Smith null JohnSmith

And you enter a new John Smith without an email address.   CRM determines its match code as:

John Smith null JohnSmith


And it then looks up that match code in its match code table, and find record # 3.

Stink.  😦

Next post, I’ll show you to address this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s