Ever since Stunnware GmbH discontinued their Stunnware Tools I have been yearning for a tool to be able to create advanced FetchXML queries.
— and no, I don’t mean advanced as in “Advanced Find”. I mean Advanced!
Since Microsoft Dynamics CRM 2011, it is possible to download generated FetchXML from Advanced Find. This helped a lot. But unfortunately, Advanced Find cannot create aggregate queries, outer joins etc, so there is still a big gap to be filled.
And since CRM 2011, the functionality has not been improved for CRM 2013 or CRM 2015.
So I started working on a project called FetchXML Builder for XrmToolBox.
This project aims to combine the good parts from Advanced Find and View Editor in Microsoft Dynamics CRM, FetchXml Tester and View Layout Replicator in XrmToolbox, and the retired Stunnware Tools to create a tool that will let you do all those things that otherwise have to be done manually.
FetchXML Builder is freeware and open source hosted on github, and available for download at:
Questions, Comments and Discussions
The concept of FetchXML Builder
The query is designed using an outline representing the actual query xml. The hierarchy of the xml document is represented by the nodes and sub-nodes in the outline.
To start designing
A query can be initiated in four ways:
- Click the New button to start with an empty designer
- Click the Edit button to paste xml copied from e.g. Advanced Find
- Click Open – File to open xml from a file containing fetch xml
- Click Open – View to get the query from a system or user view from CRM
In the outline, right-click any node to see the options available for that specific node in the hierarchy.
Note that all actions have keyboard shortcuts, the INS key will pop-up the Add submenu, DEL will prompt to delete current node, CTRL+UP/DOWN will move the selected node up or down.
Select the node to update.
In the properties window to the right, attributes available for current node are displayed with their current value filled in.
In this example, the properties of the link-entity node are displayed. Depending on the type of the selected node, different attributes will be displayed.
Whenever possible, FetchXML Builder will try to help you by presenting a set of available options (link-type is either inner or outer, so it is not possible to type anything else here), and also introduces shortcuts to filling the fields with information. In this example, it is possible to select a relation in CRM to use for the link-entity node. Selecting a relation will populate the attributes for the node, but the relation itself is not part of the node information according to the fetch xml schema.
At any point it is possible to click the Edit button to inspect the current fetch xml representation of the designed outline.
Note that you can also manually edit the XML, and if you click OK, the outline will be updated with the changes.
By using the option Live XML Update you can see the generated XML change as you design the query in the outline. This is a handy method to learn a bit more about FetchXML, how it is composed and what opportunities it exposes.
The Live XML Update works both ways – as you manually type the XML in the live window, the outline will be updated!
FetchXML Builder can be used to alter existing queries in CRM. This means you can have views in CRM using queries not possible to specify using the CRM UI.
Open an existing view by selecting Open – View, select entity and pick from the existing system and personal views.
The query from the view can then be updated using FXB functionality, and then saved back into the view in CRM.
For a detailed description and example, see article Create advanced views in Microsoft Dynamics CRM using FetchXML Builder on MSDynamicsWorld.com.
To create an aggregate query, start by selecting the fetch node, and check the Aggregate check box.
When that is done, attributes included in the query must all have a specified Alias. They shall also be marked with either Group by or have an Aggregate method defined.
Available aggregating methods are: Count, Count Column, Min, Max, Average, Sum.
To execute a query, simply hit the F5 key or press the Execute button.
Executing the query with one of the first three output types will convert the FetchXML to a QueryExpression, and call the RetrieveMultiple method on the CRM service.
Executing the query with output type ExecuteFetch will call the Execute method of the CRM service with an ExecuteFetchRequest.
Note that aggregate queries are not supported by the QueryExpression class, these will then be executed as FetchExpression.