Do not return duplicates in Dynamics 365 Web API data retrieval query

Question

I'm using the following query parameters to retrieve all active membership records with the Dynamics 365 Web API (PHP).

$params = array(
    "$select" => "member_number,statuscode,statecode",
    "$filter" => "statecode eq 1"
);

This returns all active membership records (statecode 1). However, because a particular member can have a soon-to-expire membership and a recently-renewed membership (among others), a single entity can return multiple active records, bloating my results. I only need to know once if an entity is active, not six times.

Is there a way to exclude duplicate records so that I only receive one instance of each active membership record? I'm looking for the Dynamics 365 Web API equivalent of using DISTINCT in an SQL query.

Apologies for the delay, and thanks for your answer. As I'm unfamiliar with FetchXML, I'm going to need to set up a separate Dynamics testing environment to experiment with this. I aim to do this over the next few days and will certainly accept your answer if I can get it working. Thanks again! — RossAlexander, Aug 26 '19 at 13:56
I recommend you to play around with the fetchxml queries in XrmToolBox FetchXML builder.. — Arun Vinoth-Precog Tech - MVP, Aug 26 '19 at 13:57
Unfortunately, I still haven't had a chance to attempt this. The temporary workaround has been to use the 'startswith(entity, 'value')' filter in my cURL request parameters. This returns a manageable amount of results because I'm filtering out a huge amount of unneeded results. However, I'm still getting duplicates on the results that I am returning. So, I would still like to leave this question open and explore your answer asap, as the current solution is really only a temporary one. Again, sorry for the delay, and thanks for your answer! It's been on my mind more than you know! — RossAlexander, Sep 17 '19 at 07:28

score 0 · Answer 1 · answered Aug 23 '19 at 13:22

I am not from php background but I will try.

Distinct can be used only in FetchXML query, and fetchxml can be used along with web api.

I can find the below example - an online reference with AlexaCRM toolkit.

$fetchXML = <<<FETCHXML
<fetch mapping="logical" distinct="true"> 
    <entity name="new_membership">
        <attribute name="member_number" /> 
    </entity>
</fetch>
FETCHXML;

$fetchExpression = new \AlexaCRM\Xrm\Query\FetchExpression( $fetchXML );
$collection = $client->RetrieveMultiple( $fetchExpression );

Aron · Answer 2 · 2019-10-03T02:36:12.393

While I do not recall the WebAPI having a Distinct operator, FetchXML does.

And, you can run FetchXML via the API

For example, without Distinct:

https://myOrg.crm.dynamics.com/api/data/v9.0/contacts?fetchXml= <fetch mapping='logical'> <entity name='contact'> <attribute name='fullname' /> <filter> <condition attribute='fullname' operator='eq' value='Nick Saban'/> </filter> </entity> </fetch>

Returns:

{ "@odata.context":"https://myOrg.crm.dynamics.com/api/data/v9.0/$metadata#contacts(fullname,contactid)", "value":[ { "@odata.etag":"W/\"175969918\"", "fullname":"NICK SABAN", "contactid":"ca338867-8831-e511-8103-c4346bac6974" }, { "@odata.etag":"W/\"187905023\"", "fullname":"NICK SABAN", "contactid":"fff12a11-ef85-e511-810a-fc15b4281ce0" } ] }

With Distinct:
https://myOrg.crm.dynamics.com/api/data/v9.0/contacts?fetchXml= <fetch mapping='logical' distinct='true'> <entity name='contact'> <attribute name='fullname' /> <filter> <condition attribute='fullname' operator='eq' value='Nick Saban'/> </filter> </entity> </fetch>

Returns:

{ "@odata.context":"https://myOrg.crm.dynamics.com/api/data/v9.0/$metadata#contacts(fullname)", "value":[ { "fullname":"NICK SABAN" } ] }

score 0 · Answer 3 · answered Oct 02 '19 at 18:55

0

Since Dynamics supports ODATA 4.0, you can query using $apply in this way:

$params = array(
    "$apply" => "groupby((member_number))",
    "$filter" => "statecode eq 1"
);

This query would return a different JSON but with a collection of the distinct "member_numbers" in your entity. Hope it helps.

This answer is from this question. Please check it out for more details.

answered Oct 02 '19 at 18:55

Luc

320
4
11

Thanks for the info! This looks promising, and I'm testing it out now. I'll update if/when I find a solution. Thanks again! – RossAlexander Oct 03 '19 at 14:34

Do not return duplicates in Dynamics 365 Web API data retrieval query

3 Answers3