How to Filter FetchXML Using Aliased Values in Microsoft Dynamics 365

Zoe Sands, 04 October 2017

We’ve all been using the Dynamics 365/CRM query builder to generate Fetch XML queries and wished that we could filter across entities in Dynamics 365. Turns out you can! Granted you can’t do it in the UI but you can using Fetch XML. This is useful for places like reports and custom JavaScript.

Here’s what you need to do:

1. Any linked entity that you will be using values from in your query will need to be given an aliased value:
   
   <link-entity name="account" from="parentaccountid" to="accountid" alias="ac">
   
   
2. You can then add your conditions to the filter element in the entity tag by adding an entityname parameter to your condition:
   
   <condition entityname="ac" attribute="mag_accountmanager" operator="eq-userid" />

You can use this to create any manner of complex queries that span multiple entities in your Dynamics 365 reports, JavaScript and if you like Plugins.

For a simple example the following XML will return any accounts where the account or the parent account’s account manager is the current user:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
   <entity name="account">

     <attribute name="name" />

     <attribute name="telephone1" />

     <attribute name="address2_city" />

     <attribute name="primarycontactid" />

     <filter type="or">

       <condition attribute="mag_accountmanager" operator="eq-userid" />

       <condition entityname="ac" attribute="mag_accountmanager" operator="eq-userid" />

     </filter>

     <link-entity name="account" from="parentaccountid" to="accountid" alias="ac">

     </link-entity>

   </entity>

</fetch>