Create view: Active Contacts in My State

Microsoft Dynamics 365 Customer Engagement and Common Data Service offer us the querying language FetchXML to retrieve data. Since this is the language used for views saved in the system, we can use FetchXML Builder for XrmToolBox to alter the features of views beyond what is possible using platform features, without making them unsupported.

I recently got a question from Mike if it would be possible to create a view to show contacts that live in the same state as the current user.

That should indeed be possible, I thought to myself, and started playing with the tool…

---

At the time of writing, it is unclear whether this approach is technically supported or not by Microsoft. I have found no information indicating it shouldn’t be, but will try to verify this.

Edit: I have now received confirmation from Microsoft that
this approach is not unsupported! 😀

---

First, I opened system view Active Contacts in FetchXML Builder.

Then add a link-entity to the User entity.

To make it easier for myself with the syntax I used the owninguser lookup for this relationship.

Now I add a filter to the linked User entity to only include the current user.

This query has no magic and will only show contacts where current user is the owner of the contact record. But our task was not to use the owner lookup, but to show contacts with an address in the same state as the current user’s state.

Now to the magic…

Since we want to change the join criteria for the link-entity we will need to manually change the join attributes to the state on the contact and the user entities.

The resulting FetchXML looks like this:

<fetch version='1.0' output-format='xml-platform' mapping='logical' >
  <entity name='contact' >
    <attribute name='fullname' />
    <order attribute='fullname' descending='false' />
    <attribute name='parentcustomerid' />
    <filter type='and' >
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
    <attribute name='telephone1' />
    <attribute name='emailaddress1' />
    <attribute name='contactid' />
    <link-entity name='systemuser' from='address1_stateorprovince' to='address1_stateorprovince' >
      <filter>
        <condition attribute='systemuserid' operator='eq-userid' />
      </filter>
    </link-entity>
  </entity>
</fetch>

To try the view in my Dynamics 365 organization, I save the view as a new personal view (I don’t want to overwrite the system view Active Contacts at this point).

Testing the view, and I now see all contacts in Stockholm, where I live!

Note that since the platform doesn’t really know how to render joins that are not based on anything other than physical relationships, it will look a bit strange opening this view in Advanced Find.

Note two that even though Advanced Find didn’t render the query properly, it was still possible for me to change the columns returned for the view, so I could include the State/Provice field.