Identifying merged records

You can use duplicate detection rules to find duplicate records in Dynamics 365 (or do it even better with Data8 Duplicare!), but how do you go back and find what records have been merged, or by whom?

The merged and masterid fields

Only four tables support merging:

• account
• contact
• lead
• incident (case)

All four of these have two attributes:

• merged
• masterid

When you merge two records, as well as moving related records, the record you are removing (the one that isn’t the master, also known as the subordinate record) is updated:

1. its status is changed to Inactive
2. the merged field is set to true
3. the masterid field is set to the ID of the master record

So we can use this masterid field to find both the subordinate records (where it contains a value) and the master records (where the primary ID matches the masterid of another record).

Let’s head over to Advanced Find to build our query…

Advanced Find

If you try to filter by these fields in Advanced Find you’ll quickly find the problem:

For some reason these two fields are hidden from Advanced Find! You can’t filter on them or add them into the results as columns.

Not to worry though, let’s press on for now and build out the rest of the query. Let’s say we want to see a list of the subordinate records and their associated master records, with the names of both records, the date of the merge and the user that did it. We need to add some columns to the query:

• Account Name
• Modified On
• Modified By
• Account Name (Parent Account)

The parent account isn’t quite what we want, but it’s as close as we can get for now.

Save this as a new View:

FetchXML Builder

Now, enter the powerhouse of Dynamics 365 tools, FetchXML Builder. This tool lives within XrmToolBox and will let us edit this query beyond what we can do in Advanced Find.

In FetchXML Builder, select Open View

select the “account” entity, then your newly-created view:

You’ll see a preview of the query behind that view. Click OK to open it. You should end up with a query that looks like this (the a_xxxxxxxx part is randomly generated – yours will be different):

This is our query of accounts, including the name, modifiedon and modifiedby fields, plus the name of the related parent account.

We need to make two small changes to this query.

The first is to change the related field from parentaccountid to masterid, so that the related record we get the second account name from is the master it was merged into, rather than its parent.

The second is to change the “Link type” from outer to inner. This means that only records that have a masterid value will be included in the results.

Now your query should look like:

Press F5 or click “Execute” to run the query and check the results are what you’d expect:

Here I had two records, “Data Eight” and “Data8 Ltd”, and I merged the “Data Eight” version (subordinate) into the “Data8 Ltd” version (master).

Now we can save the modified query back into the saved view:

Using the saved view

Because this is still a regular saved view you can use it by simply choosing it from the view selector:

or run it from Advanced Find.

If you edit the view in Advanced Find you’ll see this, which looks a little strange:

but you can actually continue to edit this query if you need to, e.g. add filters to see the results by user, territory etc.

You can also use all the other standard D365 features to work with the data in this view, such as showing it in a chart, exporting it to Excel or visualising it in a Power BI report to help you get to grips with what’s going on with your duplicate data.