FetchXML: get count of records based on a field of a linked entity

Question

I have a scenario where i want to get the count of people from an Entity who have multiple location address marked as current address in other linked entity.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" aggregate="true">
<entity name="client">
<attribute name="clientid"/>
<attribute name="name"/>
<attribute name="createdon"/>
<order attribute="name" descending="false"/>
<link-entity name="locationadd" from="clientid" to="clientid" alias="aa">
  <attribute name="locationadd" aggregate="countcolumn" />
  <filter type="and">
    <condition attribute="isthiscurrentadd" operator="eq" value="1"/>
  </filter>
  </link-entity>
  </entity>
</fetch>

please improve your question, specifying more details and the real names of the entities — Guido Preite, Jun 09 '14 at 10:56
@GuidoPreite this is the SQL query which i want to convert to Fetchxml. [code] select ifx_name,count(ifx_isthiscurrent) from ifx_client,ifx_locationaddress where ifx_client.ifx_clientid=ifx_locationaddress.ifx_clientid and ifx_isthiscurrent=1 group by ifx_name having count(ifx_isthiscurrent) > 1 [code] — Neodine, Jun 13 '14 at 08:14

score 1 · Accepted Answer · answered Jun 24 '14 at 05:02

The answer is

<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true' aggregate='true'> 
<entity name='client'> 
<attribute name='clientid' alias='PersonName' groupby='true' /> 
<link-entity name='locationadd' from='clientid' to='clientid' alias='aa'> 
<attribute name='isthiscurrentadd' alias='Count' aggregate='count'/> 
<filter type='and'> 
<condition attribute='isthiscurrentadd' operator='eq' value='1'/> 
</filter> 
</link-entity> 
</entity> 
</fetch>

score 0 · Answer 2 · answered Jun 10 '14 at 05:49

Try following - this code will return you the number of clients.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" aggregate="true">
<entity name="client">
<attribute name="clientid" aggregate='count' alias='clientscount'/>
<link-entity name="locationadd" from="clientid" to="clientid" alias="aa">
  <attribute name="locationadd" aggregate="countcolumn" />
  <filter type="and">
    <condition attribute="isthiscurrentadd" operator="eq" value="1"/>
  </filter>
  </link-entity>
  </entity>
</fetch>

FetchXML: get count of records based on a field of a linked entity

2 Answers2

Linked