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' />
    <attribute name='telephone1' />
    <attribute name='emailaddress1' />
    <attribute name='contactid' />
    <link-entity name='systemuser' from='address1_stateorprovince' to='address1_stateorprovince' >
        <condition attribute='systemuserid' operator='eq-userid' />

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.

14 thoughts on “Create view: Active Contacts in My State

  1. This is crazy good, love the way you demonstrated. Now I can think of various other ways to play around views. Thanks

  2. Hi!

    do you know how I might be able to make columns from the linked entity visible in CRM?

    they work in the fetch xml builder, but will not show up on the view in CRM.

    1. Hi – since FXB only alters the query, and not the layout (columns) of the view, you can create a view in Dynamics and add columns from e.g. Owning User on the Contact. Then you open that view in FXB and change the link-entity node to the join criteria according to the blog post.

  3. That solves one of the two things that we frequently needed and ended up writing scripts for custom view
    – compare the column value with another column value
    – OR condition for conditions from different entity

    Too bad Microsoft has confirmed that this approach is not unsupported ☹️

    1. Maybe a misunderstanding here…
      MS confirmed it is NOT UNsupported! Two minus means plus, so it IS supported! ☺

  4. Great post – many thanks Jonas.

    I’ve been experimenting with the combination of FetchXML Builder and View Designer, changing the link-type to expand the results across a related 1-n relationship. This works very nicely for the view.

    What I’m wanting to do now is to use the amended view for the associated/related records. However, the related view only displays the directly related records (say 2), event though the reported count shows “1 -5 of 5 “:

    Is there any way around this?

    I’m happy to share further details.

    1. Hi Alex – sorry about delayed reply…
      I’m not sure I quite understand the situation. Feel free to contact me (email found under Jonas – About in the menu above) with some more details and perhaps screen shot etc.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.