Why Duplicate Detection job is impractical

Idea of Duplicate Detection Jobs of Dynamics 365 is with great value. It allows you to monitor the number of Duplicate records in a given time: so necessary action could be taken.

Setting a job is very easy because it allows you to select the entity (type with a Duplicate Detection Rules are set) and mention which View is selected to run against. Most importantly scheduling is possible. Once you schedule it, you will get a new report after each execution.

This is where frustration starts. For example, I am trying to monitor how fast duplicate Contacts are being grown by checking a report every week. Below is a sample report. D365 instance I am monitoring already got thousands of duplicates.

First I was surprised to see number 12. Then I realized, 12 is nothing, but the number of records showing in this page. (at least it could have been 10!, what is 12 anyway..)  Then I had to click next and go on. The simple mistake in this window is, it doesn’t show the total number. For me, it was a hectic task click next again and again to check the number of records.

Anyway, I ended up writing a console application to read the number of records in the report. Below is the Fetch Xml I used for it. May be this will help you too.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
    <entity name="duplicaterecord" >
        <attribute name="duplicateid" />
        <attribute name="createdon" />
        <order attribute="createdon" descending="true" />
        <filter type="and" >
            <condition attribute="createdon" operator="on-or-after" value="2020-07-03" />
            <condition attribute="createdon" operator="on-or-before" value="2020-07-06" />
        </filter>
        <link-entity name="asyncoperation" from="asyncoperationid" to="asyncoperationid" >
            <attribute name="name" />
            <attribute name="createdon" />
            <filter type="and" >
                <condition attribute="name" operator="like" value="%Weekly Duplicate Monitor Job: Active Contacts%" />
            </filter>
        </link-entity>
    </entity>
</fetch>

Client side Retrieval using Xrm.WebApi - Handling Lookups

One of the previous posts I provided sample codes for different client side operations using Xrm.WebApi. Click this to find it.

Anyway, one of the tricks I couldnt post was how to retrieve a Lookup which is little tricky. Lets check it using with examples.

Here I am trying to retrieve Full Name and Email Address as below.

https://xxxx.crm6.dynamics.com/api/data/v9.1/contacts(73bc115c-4317-ea11-a811-000d3a6aa9c8)?$select=fullname,emailaddress1

It works fine. Check my result.

{"@odata.context":"https://xxxx.crm6.dynamics.com/api/data/v9.1/$metadata#contacts(fullname,emailaddress1)/$entity","@odata.etag":"W/\"77570311\"","fullname":"Aaron Smith","emailaddress1":null,"contactid":"73bc115c-4317-ea11-a811-000d3a6aa9c8"}

Suppose, I need to retrieve a lookup field value. For example, Created By name. What I feel is just adding it as another field. So I am adding to the end of he field list.

https://xxxx.crm6.dynamics.com/api/data/v9.1/contacts(73bc115c-4317-ea11-a811-000d3a6aa9c8)?$select=fullname,emailaddress1,createdby

Hmm.. here comes the confusion. I don't get any values for Created By lookup field. I am sure I spelled correctly, otherwise I would get a error message about unknown field.

{"@odata.context":"https://xxxx.crm6.dynamics.com/api/data/v9.1/$metadata#contacts(fullname,emailaddress1,createdby)/$entity","@odata.etag":"W/\"77570311\"","fullname":"Aaron Smith","emailaddress1":null,"contactid":"73bc115c-4317-ea11-a811-000d3a6aa9c8"}

Well, this is the trick WebApi just doesn't return values from other entities. Lookup value is physically in related entity. So you have to extend the query with Expand keyword.

https://xxxx.crm6.dynamics.com/api/data/v9.1/contacts(73bc115c-4317-ea11-a811-000d3a6aa9c8)?$select=fullname,emailaddress1&$expand=createdby($select=fullname,systemuserid)

Here we get the result like a charm.

{"@odata.context":"https://xxxx.crm6.dynamics.com/api/data/v9.1/$metadata#contacts(fullname,emailaddress1,createdby(fullname,systemuserid))/$entity","@odata.etag":"W/\"77570311\"","fullname":"Aaron Bailey","emailaddress1":null,"contactid":"73bc115c-4317-ea11-a811-000d3a6aa9c8","createdby":{"@odata.etag":"W/\"29915606\"","fullname":"Paul Wicks","systemuserid":"267874e4-645c-e911-a870-000d3a6a065c","ownerid":"267874e4-645c-e911-a870-000d3a6a065c"}}

In summery, make sure you link entity with Expand command to retrieve lookup values.

Simple!