Skip to main content

Create advanced views in Microsoft Dynamics CRM using FetchXML Builder

by Jonas Rapp
MVP, Tech Lead, CRM-Konsulterna

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

FetchXML in Notepad

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 ...

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
Learn more about us here

About Jonas Rapp

Jonas Rapp is a Microsoft Most Valuable Professional in the Business Applications category since 2017, an open source tooling advocate and a speaker on techy topics around the Power Platform and Dynamics 365.

Blog: https://jonasr.app/

GitHub: https://github.com/rappen/

Twitter: https://twitter.com/rappen/

More about Jonas Rapp