FetchXml Distinct Aggregate not working

(1) Share

Report

Posted on by Community Member

Hi,

I'm trying to get the number of Service Activities with Distinct Clients, however the FetchXml gives the same number back whether distinct or not. We already have sample data with the same clients on different Service Activities.

<fetch version="1.0" output-format="xml-platform" mapping="logical" aggregate="true">
  <entity name="serviceappointment">
    <attribute name="new_client" alias="Count" aggregate="count" distinct="true" />
  </entity>
</fetch>

<fetch version="1.0" output-format="xml-platform" mapping="logical" aggregate="true">
  <entity name="serviceappointment">
    <attribute name="new_client" alias="Count" aggregate="count" />
  </entity>
</fetch>

They both return the same number. Am I missing something?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Community Member on at

Like (0)

Report
Hi Jarren,

try the following:

<fetch distinct='true' mapping='logical' aggregate='true'> <entity name='serviceappointment'>
<attribute name='new_client' alias='new_client_count' aggregate='count' />
</entity>
</fetch>

Please let me know if you solve.

If you found the answer helpful, please mark as Verified

Join my network on LinkedIn Follow me on Twitter

Thank You & Best Regards

Francesco Picchi

Microsoft Dynamics CRM Consultant, Bologna+Milano, ITALY

Independent Contractor

http://www.francescopicchi.com

Was this reply helpful? Yes No
Suggested answer

Rawish Kumar 13,758 on at

Like (2)

Report

Hi Jarren,

Addition to francesco's comment,

This should probably answer your question :

mark my anwer as verified if helpful.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Francesco,

This doesn't seem to work. Just to confirm, if I have 3 Service Activities, 2 with Client A and 1 with Client B the New_Client_Count will be 2 right?

Right now it's still showing 3 for this query and even if I attach distinct="true" on it.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Rawish,

I originally based my Fetch XML on this, problem was that it there was no difference to them.

Was this reply helpful? Yes No
Suggested answer

Preeti Sharma 2,678 on at

Like (0)

Report
Hi Jarren,
Try to add distinct on first row of fetch as below:

<fetch version="1.0" output-format="xml-platform" mapping="logical" aggregate="true"
distinct="true">
<entity name="serviceappointment">
<attribute name="new_client" alias="Count" aggregate="count" />
</entity>
</fetch>

Hope this helps :)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Unfortunately, I tried that and it doesn't work. They always return the same count of 60 whether it's distinct or not. And I do have multiple entities with same contacts.

I've tried it on different entities and fields but it doesn't work. I tried it in code and using XRMToolbox's FetchXML Tester and Advanced FetchXml Tester. Is this a Dynamics 365 issue?

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report
Ok so it works. You need to specifically add aggregate='true' at the header and aggregate='countcolumn'.

You can even add a groupby as below if needed.

<fetch version="1.0" output-format="xml-platform" mapping="logical" aggregate="true" distinct="true"> <entity name="serviceappointment"> <attribute name="new_group" alias="Group" groupby="true" /> <attribute name="new_client" alias="Count" aggregate="countcolumn" distinct="true" /> </entity> </fetch>

Thanks everybody

Was this reply helpful? Yes No