Create advanced views in Microsoft Dynamics CRM using FetchXML Builder

The back-end functionality for querying data in Microsoft Dynamics CRM has been greatly improved in the last few years. One of the improvements is the possibility to define conditions applying to entities other than the immediate parent entity, which is the key to composing "not-in" queries (see MSDN documentation). In addition, there have been improvements in aggregation functionality.

However, the front-end view designer and Advanced Find have not kept pace with the back-end progress, and it basically has the same functionality as in the first release of CRM 2011.

In this article, I will explain and demonstrate an easy way to create a view query emulating the SQL union functionality.

Challenges

Composing the query

As there is not support in the view designer to create outer joins or not-in queries, you are forced to use some text or xml editor to compose the queries. Unless you are a wizard at writing FetchXML, this can be a quite tedious and complex task. And to test the query, you have to write your own code to execute the query or use a tool like FetchXML Tester bundled with XrmToolBox.

Manually replacing the query

It is possible though to use a lot of the new querying possibilities in views, even though it is not possible to design them in the CRM UI. You just have to know how to get these advanced queries into the actual view definitions. ...