FetchXML Builder

FetchXML Builder

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! 


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

 

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



 
 

Download


FetchXML Builder is freeware and open source hosted on github, and available for download at:

fxb.xrmtoolbox.com


 

Questions, Comments and Discussions

Questions and general feedback is welcome in the Gitter discussion.
Festure requests, bug reports etc are submitted as GitHub issues.
All you need to use GitHub and Gitter is a free GitHub account.


The concept of FetchXML Builder

FXB-outline1Design query xml in a comprehendible UI

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:
  1. Click the New button to start with an empty designer
  2. Click the Edit button to paste xml copied from e.g. Advanced Find
  3. Click Open – File to open xml from a file containing fetch xml
  4. Click Open – View to get the query from a system or user view from CRM


FXB-outline-context1 Updating the query

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.


FXB-attributes1 Changing the attributes of a node

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.


FXB-xml-window1 Getting the XML

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.


FXB-LiveUpdate1 See the XML grow as you work

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!


FXB-OpenView1Update views in CRM

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.

FXB-SaveView1


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.


FXB-Aggregate1 Create aggregating queries

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.
FXB-Aggregate2
That is basically it. If something is not configured correctly, executing the query will show a dialog informing the user of what needs to be changed.



 

FXB-Execute2 Executing the query

To execute a query, simply hit the F5 key or press the Execute button.
How the results are displayed is determined by the Output Type setting under Options.FXB-Output1
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.



Questions and general feedback is welcome in the Gitter discussion.
Festure requests, bug reports etc are submitted as GitHub issues.
All you need to use GitHub and Gitter is a free GitHub account.