How Many Duplicate Records Do You Have?

Wed, 11 Jun 2008 18:14:00 GMT

Microsoft Dynamics CRM 4.0 has built-in duplicate detection which is great, but in 3.0, which many businesses are still using, the only options for duplicate detection were some third-party applications or a a custom-developed one like the minimally functional example in the 3.0 SDK.

[BEST PRACTICES NOTE: Duplicate detection should be only one of the tools in you data quality arsenal. The best approach is to have some solid practices that govern data entry, and to train users well on things like searching for a record before entering new data.]

Because of the shortcomings of duplicate detection in version 3.0, a lot of these systems have ended up with some data quality issues. If you're running CRM 3.0 and you'd like a quick way to check your duplicates, here's a simple SQL query you can run to find them.

Open SQL Server Management Studio and select your CRM database (it will be called something like 'YourCompanyName_MSCRM')
Right-click the CRM database name and select 'New Query.'
In the query window, type the following query:

SELECT name, COUNT(name) AS NumOccurrences
FROM FilteredAccount
GROUP BY name
HAVING (COUNT(name) > 1)
ORDER BY NumOccurrences DESC

Then click the 'Execute' button to run the query. You'll get back a list that looks like this:

name	NumOccurrences
Bakersfield Furniture	3
Dodge City Wholesale	2

If you are using an account number or other identifier that should be unique, you can replace 'name' in the query with the name of that field to be more precise. Then you can go about locating the accounts in CRM and deciding what to do with them. Remember that CRM 3 and 4 have a "merge" function that lets you select two records at a time in a list view and merge them together by clicking the merge icon at the top of the list.

CRM MVP Connection

How Many Duplicate Records Do You Have?