FetchXML Builder

FetchXML Builder by Jonas RappEver 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.

42 thoughts on “FetchXML Builder

  1. Works great in CRM 2015, but Can't get it to use link entities in CRM 2011, e.g. trying to show accounts with out contacts. I get an error " 'Account' entity doesn't contain attribute with Name = 'contactid' ".
    What am I doing wrong?

  2. In my company we have CRM 2015 online. The following XML was derived from an Advanced Find in CRM. Your tool produces the expected results. However when I try to use it in VS 2012 I am getting the error:

    An error occurred while executing the query.
    The attribute type "PartyList" is not supported. Remove attribute "customers" from the query, and try again. (Microsoft SQL Server Report Designer)

    Please help!!!
    PS: I can't copy paste the XML, it leaves it blank. It's a basic advanced find to search in the service activity entity

  3. Hello Jonas. using XrmToolbox v 2015.7.31 with FetchXMLBuilder 1.2015.7.61.
    Why can I not see all entities in Open Views?
    I only see a small number of custom entities. Sometimes when I Open Views I have nothing to select at all.

  4. I am trying to generate fetchxml using this tool but when I execute the fetchxml it does not show the link-entity attribute as a column in result. Do you know why?

    1. If the attribute does not contain any data it will not be included in the result. And only columns available in the result will be displayed in the grid.
      Can you verify that the attributes really contains any data?

  5. Is it possible to do something like: get activities where owner does not equal modified by? I'm a newbie to a bunch of this stuff.

  6. Hi Jonas. Thanks for providing this awesome tool! It really makes life a lot easier.

    I created a fetch that will show all contacts in one marketing list that aren't in another marketing list. But when I save it back to CRM, all the filters and conditions are gone.
    Any idea what the issue is?

    1. Hi Robert, thank you! 🙂
      How do you use the query you created? I get the impression you try to use it for a dynamic marketing list, or to select members of a static list?
      I know there is a problem using the not-in syntax with dynamic ML, CRM just won't accept them. There is a Connect item for that, please up-vote if you think it should be fixed 🙂 https://connect.microsoft.com/dynamicssuggestions/feedback/details/1694631/ (links are not clickable in comments, hope you can copy it).

  7. You're welcome.
    I am using the view to select members for a static list. I am joining contact to listmember and then to another listmember using an outer join to pull back the records, basically setting up a left outer join. I'm not using the not-in operator but I do wonder if the listmember entity or multiple joins is giving CRM problems because it works fine when executing the query directly.

    1. I don't mean using the "not-in" operator, but rather the left outer join method that will give you "all these except those" records.
      MS is definitely doing something fishy there, when selecting members of a marketing list.
      Is it possible to use the view in the main navigation of CRM to display the correct result?

    2. Yes, that is expected behaviour… CRM can use the view to present results, but it cannot present the definition of the query. And for some reason it appears it is not possible to use as selection for ML members.

  8. hi,
    am a crm newbie, i have this request. can your tool help create a view of active cases where the created by column (user) is displayed plus a column showing there current team? keeping my fingers crossed.

    Cheers
    Richard

    1. Hi Richard
      What do you mean by "their current team"? Have you added a lookup on User to Team? If so, this view can be created directly in CRM.
      /Jonas

    2. hi. thank you for replying.

      on the user profile you have a sub-grid of sorts where a list of all the teams the user is a member. So if Richard belongs to Team Beta, then Richard creates a case. on the active cases view (Service>Case) on the column created by = Richard then adjacent column Team Name = Beta. Hope that clarifies

    3. Hi, yes that clarifies. But as it is a one-to-many relationship and Richard technically could belong to 0, 1 or many teams, it is not possible to display "his" team. It is just not possible to determine which one (1) that would be, not even with FXB…
      You would need to either have a lookup on User specifying "Primary Team" or perform some aggregation of team names to a custom attribute on user to be able to display it.

    4. Hey,

      I am attempting to update a system view in CRM 2013 with some more attributes from a custom FetchXML. The Fetch executes fine but I cannot save the view back into CRM. The following error message presents "cannot save view, returned attributes must not be changed"

  9. Hi Wibbey B
    That is correct, FXB can only change the query behind the view, but not the LayoutXML that defines the design of the view.
    So if you first design the appearance of the view in CRM, you can then use FXB to modify the query that defines which records to display in the view.

  10. Is it still possible to retrieve an attribute from an entity further down the structure? For examples: Quote view where I want to retrieve an attribute from a custom entity by going through the opportunity?

    Quote
    Link to Opportunity
    Opportunity link to custom entity
    Required attribute

  11. Hi Jonas, your tool has assisted us tremendously.

    I have a requirement, is it possible to create an xml which can combine a team and join with the count on case assigned to the team?

    Richard

    1. Thanks 🙂
      That should be quite possible.
      (unfortunately this comment field is not very good for formatting code, but here it is)

      <fetch aggregate='true' >
      <entity name='team' >
      <attribute name='name' alias='team' groupby='true' />
      <link-entity name='incident' from='owningteam' to='teamid' link-type='outer' alias='case' >
      <attribute name='incidentid' alias='count' aggregate='count' />
      </link-entity>
      </entity>
      </fetch>

  12. Hello!
    Trying to make "or" filter for 2 outer joins, but afteri i save and publish my new view i connot find it in crm Advanced View and on form grid, but i can see it in customization. Is it possible to make such filters?
    How can i post Fetch here? i dont see in my comment.

    1. Here xml.
      fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"
      entity name="test_contact_info"
      attribute name="test_name" /
      filter type="and"
      condition attribute="statecode" operator="eq" value="0" /
      filter type="or"
      condition entityname="Contact" attribute="fullname" operator="not-null" /
      condition entityname="Agreement" attribute="test_name" operator="not-null" /
      /filter
      /filter
      link-entity name="contact" from="contactid" to="test_ref_contact" link-type="outer" alias="Contact"
      link-entity name="test_debtor" from="test_ref_contact" to="contactid" link-type="outer" alias="ab"
      filter type="and"
      condition attribute="test_ref_debtname" operator="like" value="%test%" /
      /filter
      /link-entity
      /link-entity
      link-entity name="test_agreement" from="test_agreementid" to="test_ref_agreement" link-type="outer" alias="Agreement"
      filter type="and"
      condition attribute="test_ref_debtname" operator="like" value="%test%" /
      /filter
      /link-entity
      /entity
      /fetch

    2. Hi Kirill (unfortunately Blogger's comment parser is not very intelligent)
      I think the fetch looks ok, so I can't really understand why the view is not displayed in your CRM.
      Does it display properly after being created in CRM, but after updating the query using FXB, it is not displayed anymore?

      PS – I am closing comments here, please continue the thread at Gitter

  13. hi. trying to fetch more than 5000 results. Is it possible from fetchxml builder? I know there is something called paging cookies.. Please let me know if it is possible.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.