Correcting Duplicate Data in Dynamics

   Words by Paul McQuillan

   on 20/02/2019 10:00:00

Duplicate data in any CRM system can make a single view of the customer difficult and crucially hinder user adoption.

In a way, duplicate data fits a similar fashion as the ‘broken window’ theory of policing, small doses are often unavoidable but letting the problem pile up can result in the failure of the project.

Fortunately Dynamics 365 gives us some simple powerful tools to find and eliminate duplicates and stay on top of the problem.

Data Management - Our first port of call is visiting the Data Management area of Dynamics to look at our rules for identifying duplicate data.

image

Duplicate Detection Rules - within this area of Settings, we can navigate into the Duplicate Detection Rules area.

image

Here we have a list of the rules for how Dynamics will compare Record A to other Records in the system – with each rule defining a criteria for the Entity Type of Record A.

Initially this is best to focus on a single Entity at a time to look at what possible records we want to search against to find a potential duplicate.

In the example below, we focus on four rules we have on the Account Entity to build our list of rules for which records might be a duplicate that we want to review, cleanse or merge.

image

We can build each Rule to define what Dynamics will think is a potential duplicate to our Account Record A:

image

The rule itself here can match our Source Entity Type to any other Entity Type within Dynamics – and then use the list of Criteria to determine how Fields in our Source Entity Type (in this case, Account) should be compared to Fields in the Matching Entity Type.

This article by one of my colleagues can be quite useful to help understand this screen: https://www.crmcs.co.uk/content/avoiding-duplicates-in-dynamics.aspx 

Once we have our Rules defined, we can then put these to use.

Duplicate Detection Jobs – we can navigate into this area within Data Management and use this to produce a Report on Duplicates within a particular Entity that we can use to cleanse these duplicates.

We can start here by clicking NEW to create a new Job.

image

This then allows us to define what Records we want to check for Duplicates – using the familiar Dynamics Advanced Find interface to build our criteria for what Records to check for Duplicates.

image

NOTE: this screen does allow us to search our entire CRM Database for Duplicates (within one entity) but Dynamics will raise an Error if it finds over 5000 Duplicates for a single Job, and so you may need to run separate Jobs for smaller Criteria within that Entity if your CRM has a considerable amount of data ithin that Entity.

image

Finally we can then give our Duplicate Detection Job a Name for reference and whether to Email a Dynamics User on Job Completion.

image

This will run for a period of time depending on the Number of Records being compared against Duplicates – and then ultimately finish leaving us with a Report that we can use to review, merge or delete the Duplicate Records found for this Criteria within CRM.

image

Here we can then decide what to do with the Record and its potentially Duplicate Record.

The most obvious action here is to Merge the two records together:

image

Here we can either:

Select Master – this will allow us to pick the most up to date record to keep, and also allow us to pick between which Fields to keep between the Main Record and the Duplicate Record.  This is the slower more manual way to correct the potential duplicates but is also the most accurate.

image

Merge Automatically – this is more the blunt tool to bring the data inline more quickly but less accurately – this takes the merging of individual fields and automates this as the following:

  • Master Record has the Field populated – then the Master is always used in favour of the Child
  • Master Record has the Field blank, but the Child Record has the Field populated – then the Child value is used for this Field

This assumes that the default Master Record is the record being selected at the top of the Dedupe Report Screen, and so the record finding the duplicates is the Master and the duplicate identified is the Child.

This gives us a way of running Jobs on our CRM Database to build these Deduplication Reports – and then working through the Report to Merge the Records with the Duplicate that have been identified. (or in some cases, simply deactivate the found duplicate)

Whilst a pain-staking job, this can be useful for getting the data right and giving our Sales, Marketing or Service Teams a lower barrier to engaging with CRM and making the solution a success.

The following two points can be common errors when working with the Deduplication Report screen:

Why is my Duplicate Detection Job showing 0 Matches when I highlight a Record?

If the Job has returned a Potential Duplicate but then is showing No Potential Duplicates, this doesn’t make sense, but this is where CRM has performed the Duplicate Detection Job against a particular set of Deduplication Rules – but then these Rules have since changed.

So CRM has still found that this Account potentially has 1 or more Duplicates as the Rules were when running the Job, but cannot find these Duplicates under the Rules as they are in place now.

image

If we reactivate the Rules from when the Job was run, this will then resume showing these duplicates.

Cyclical Parenting?

If we see the following error, then this means that the Duplicate Check has found 2 Accounts that are already related to each other as Parent-Child Accounts in a Hierarchy.

Here, merging would create a loop in Hierarchy of Parent-Child-Parent and so disrupt how the Accounts are being stored.

image

Going Further

Ultimately manually deduping a database in bulk can be a long and tedious task – and so the challenge here is to review the quantity and type of duplicate data and then look at whether the Data Cleansing could be better automated.

This involves building a list of rules for what data should be automatically merged or removed from CRM and then running intelligent processes to cleanse the data.

This can be particularly effective when coupled with external data-checking sources such as DDL, Linkedin or Companies House that provide an external validation of the data within the cleansing process.

There are a number of tools and developments that can be useful to this aim – from large scale data migration and cleansing systems (such as Scribe, Inaplex and others) to specific routines run through Dynamics Custom Development or Workflow to help automate cleansing within CRM.

At CRMCS we use a pattern called Overtime Tasks that run a scan over particular records in Dynamics (driven by particular Advanced Finds in CRM) to then process each record in turn and apply the Cleansing or Data Validation Rules as defined.

Obviously this can only automate the automatable, using rules, logic and external services to check the data – as the application of common sense is still difficult for systems to apply! (even in this age of increasing Machine Learning and AI)

But this can be used in conjunction with Human Intelligence to review how a Human would cleanse the duplicates in CRM, draw this down into an automatable process, and then apply this process across many hundreds, thousands or tens of thousands of records in quick succession – in a way that would be very time consuming for a human to review each record by.

Deduplication and Data Cleansing is an area that CRM and various line of business systems have focused on for some time, and so the following articles may be useful when looking at performing a Cleansing Job on CRM Data:

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

Dynamics 365 Marketing vs ClickDimensions It’s time to pause, reflect and acknowledge a new era of inclusivity and collaboration. Part 2 - How to get the most from a Technology Expert – Asset Management Hub Property & Asset Management Hub Part 1 – Balancing CRM and Asset Management Scopes - Asset Management Hub Creating a Multi-Lingual PowerApps Portal How to Set Up a Microsoft Teams Site Using DocDrive365 Microsoft Teams - Adding a Microsoft Teams URL to a Dynamics Appointment Dynamics 365 Marketing – Customer Voice Survey Not Appearing In Emails? Using SQL Management Studio to connect to the Dynamics DB Calling a Power Platform AI Builder Model via oData How to use DocDrive365 to integrate permissions between Business Units in Dynamics with Sites in SharePoint Getting started with the Power Platform AI Builder. Power Apps Portal Information Hub DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Part 5 - Power Apps Portals: How To Connect Azure B2C With Linked-In Part 4 – Power Apps Portals: Styling Azure B2C for Power Apps Portals The 3 Phases for Using Multi-Select Option Sets in Flow with Microsoft Forms Part 3 – PowerApps Portals: Azure B2C and Power Apps Portals – User Flow for Signup and Signin Part 2 - Power Apps Portals: New Application Registration in Azure B2C for our Power Apps Portal Part 1 – Power Apps Portals: Creating a New Azure AD B2C Tenant The Automation Bot: Launching Contextual Flow from Teams Creating a New Bot for Teams Debugging your Teams Bot using Ngrok Adding a Microsoft Teams URL to a Dynamics Appointment
Contact Us

Want expert advice or a demo?

Get in touch now and see how we can help your business grow.

  • Name
  • Email Address
  • Phone Number
 
Close

Understanding Your Challenges

Our strong understanding of CRM and emerging technologies within the Microsoft environment means we deliver the right solutions for you.

Proven Real-World Solutions

As a leader in the field of Dynamics solutions, our pedigree developing and delivering real-world solutions is unsurpassed.

Long Term Support

We provide support beyond our design, implementation and 'go-live' delivery using Sprints and continual updates to our AppSource apps.

CRMCS | Design by Thinktank Marketing | Citrus-Lime Limited

To improve your experience today and in the future, this site uses cookies. Read our full Privacy Policy & Cookie information here I Understand