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?
Only four tables support merging:
- incident (case)
All four of these have two attributes:
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:
- its status is changed to Inactive
mergedfield is set to
masteridfield 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…
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:
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
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
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
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:
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.