Create advanced views in Microsoft Dynamics CRM using FetchXML Builder

May 18 2015

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 view definitions. This is a process which usually requires in-depth knowledge about the CRM solution files and the customization xml document. 

FetchXML customizations 

FetchXML Builder for XrmToolBox

Over the last years, XrmToolBox by MVP Tanguy Touzard has become one of the must-have tools for CRM developers, customizers, administrators, and in some cases even normal users.

XrmToolBox is open source and completely free to use, and built to easily allow other developers to take advantage of its framework and create an extendible all-purpose toolbox for Microsoft Dynamics CRM.

One of the "fourth party tools" available for the "third party toolbox" is the FetchXML Builder. This tool helps you compose queries in a graphical user interface.

XrmToolbox FetchXML Builder

You can execute the queries to test them and view the results in a grid, as serialized XML, or as JSON.

FetchXML Builder ResultGrid

FetchXML Builder can open a query from an existing view in CRM, alter the query, and then write it back to the view.

More information on the functionality can be found on my blog and on the FetchXML Builder home page. In the rest of this article I will examine how to build more complex queries on Dynamics CRM data using FetchXML and FetchXML Builder.

Creating a UNION view in CRM

To create a view using advanced FetchXML capabilities, a few steps are required:

  1. Create the new view with desired column set
  2. Open the view in FetchXML Builder
  3. Alter the query
  4. Save the view back to CRM

In this example, I will create a view that displays accounts that have any open opportunity with a budget amount of at least SEK 10.000. Nothing fancy about that, but I will also include another condition in the view to display accounts that have any contact with any opportunity with the same criteria.

Creating the view

First, the view must be created in CRM. This is done just like creating any other system view. The filter in the view is not important at this stage, but the columns to be displayed in the view should now be properly defined.

Save and publish the changes to make the view visible from FetchXML Builder.

Open the view in FetchXML Builder

Start XrmToolBox, connect to the CRM organization, and open FetchXML Builder.

Select Open - Open View... to display a window with available entities and their views.

FetchXML Bulider Open View

First select which entity to open, then the view that shall be altered.

When selecting a view, the preview-window displays the current FetchXML for that view.

FetchXML Builder Select View

Clicking OK will display the outline representing the query.

FetchXML Builder Outline

Alter the query

Use the commands available in FetchXML Builder to alter the query. This is done by selecting a node in the outline, and defining the properties of that node. Right-clicking the node will display available commands for that node.

To see in "real time" how the resulting FetchXML is affected by the changes you make, select Options - Live XML Update. This will open a window to the right of the XrmToolBox window, showing the current xml. It is also possible to type xml in that window, which will then update the outline in FetchXML Builder.

FetchXML Builder Options 

To construct the query, I add two outer joins (link-entity with link-type="outer") and a composite filter expression to include accounts from either of the joins. To ensure that accounts matching both of the criteria do not appear twice (or more) the distinct checkbox on the root fetch node is ticked.

FetchXML Builder query outline 

Resulting FetchXML for this UNION query:

FetchXML Union Query

The results of the query can be tested by hitting F5 or clicking the Execute-button.

FetchXML Builder ResultGrid

Save the view to CRM

To update CRM with the new view definition, simply click Save - Save view...

FetchXML Builder Save View 

This will verify that the returned column set matches the expected columns, to ensure that the LayoutXML defined when the view was created in CRM is still valid.

Refreshing the browser will read the updated view definition, and displaying the view will show the desired results:

FetchXML CRM View

Side effects

Since this view is not possible to design in the CRM UI, the view is not even displayed among existing views in Advanced Find.

Dynamics CRM System Views display

View designer

The view is displayed with column layout and possibilities to define sorting and add new columns etc. But there is no link to "Edit Filter Criteria" as there usually is. This is because CRM actually realizes that this view is beyond its design capabilities, and only lets you alter the view in ways CRM knows CRM can handle. 

Dynamics CRM View Designer 

Resources

XrmToolBox is developed by Tanguy Touzard at Javista, France. Blog

FetchXML Builder is developed by Jonas Rapp at Cinteros, Sweden. Blog Twitter

Both tools are open source maintained at http://github.com.

Both tools have contributing developers spread globally.

Downloads

Both tools can be downloaded and used for free to any extent.

XrmToolBox: http://www.xrmtoolbox.com

FetchXML Builder: http://fxb.xrmtoolbox.com

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to 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