FetchXML Builder original documentation

The concept of FetchXML Builder

Design query xml with an intuitive UI

The query is designed using an outline (treeview) 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-context1Updating 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-attributes1Changing 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-window1Getting 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-LiveUpdate1See 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-Aggregate1Create 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-Execute2Executing 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.

74 thoughts on “FetchXML Builder original documentation”

  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?

    1. Hi David!
      Outer joins and aliased conditions were introduced in CRM 2013, so unfortunately this is not supported in CRM 2011.

  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

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

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

  7. Nice tool!
    I cannot however find this function:
    Generate OData query string from fetch xml
    Where do i find this function?

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

    1. Hi Unknown
      That type of filtering is not supported by the CRM query languages.
      It really should be, you would think…

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

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

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

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

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

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

    1. Unfortunately that is still not supported by CRM. If you find a way around it, I would love to hear about it πŸ™‚

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

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

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

  19. Hi Jonas,

    I first time use the FetchXML Builder. When I try to save the changes of my view, there is an error: “Cannot save the View, retrned attribute must not be changed” .
    What’s mean. I have added two attribute in the link-entity.
    Can you please give me instruction. Thanks.

    Alan

    1. Hi Alan,
      FXB only changes the query and not the LayoutXML of the view. This means that the columns returned by the query must not be changed, or the layout of the view will fail.
      If you are a member of D365UG, you can watch a webinar I did where I also used the View Designer in XrmToolBox to edit the column set of a view. It starts about 18 minutes into the video: https://www.crmug.com/crmug/viewdocument/use-fetchxml-builder-and-other-xrmt
      Good luck!

  20. Hi Jonas,

    Thanks for your valuable information. Unfortunately I’m not the member of the D365UG,so I cannot access the webinar to watch the video. But I have learn a fare of about FXB from your Youtube and MSDW post. Now I wanna use FXB in my project. Here below is simple introduction of it,

    I need create a view for one of the Entity-Product. Two of the columns named Retail Price and Wholesale Price which come from another relative Entity- Price. Product and Price have 1:N relationship. Price have setup a subgide in the Product form and have associate view (There are no “Retail Price” and “Wholesale Price” fields in the Product Field List).
    How can I use FXB to retrieve data and show in the Product View?
    Sorry for so long question. Can you direct me a right way? Appreciated for your time to answer it.

    Alan

    1. Hi, assuming I understand your question correctly, you cannot show information from a 1:N relationship in a view. Each record on the parent entity (Product) can then have many records in the child entity (Price), which would not be possible to represent in a view.
      The only was to present this information in a view would be to start from the Price entity, and include columns form the parent Product entity.

  21. Thanks Jonas!

    That’s why I cannot Save the compposed XML in FXB back to my view since I have been fail to add two attributes in the link-entity.
    Your last sentence above is means to creat the subgride of the Price in the Product entity to show the infornation, right? If it is, I have done.
    Also, any other ways you know,e.g. write a workflow or a plug-in to satify the requirement? Kindly offer a instruction.

    Thanks again, Jonas. FXB is an ownsome tool, I will keep eye on it.

    Kindly,

    Alan

    1. I suggest we continue the conversation over email, you can find my address in the About page on this site!

  23. Having trouble doing a aggregate View of Opportunities by owner with estimated revenue. Get error because of the opportunityid attribute that is automatically included in the fetch even if it is not a part of the returned results. Can’t delete it or group by it or nothing without errors. Any clue what’s going on?

    CRM 2011

    1. Hi Cory,
      Dynamics unfortunately does not support aggregate queries in views, so this is not possible – even with FXB ?
      You can create aggregate queries to use in code and reports though.

  24. After everything I tried, I was afraid that MS Dynamics CRM 2011 did not support aggregation of Views. I knew it did for Reports, but, these Excel users see a grid and think it should be simple! Thanks for the confirmation.

  25. How can I get sql query for the corresponding FetchXML

  26. Is it possible to create a view containing all contacts that do not have a subscription to “no-thank you letter” (and/or) ‘lost’? When someone has multiple subscriptions (to other letters) or no subscription at all they do not show in the results

    Kids regards

    Simone

  27. I love this! Something I heard it can do but am having difficulty with though is an OR statement over two conditions in different entities, is this possible?

    1. Absolutely possible!
      Try something like this: (sorry for lousy formatting here, paste it into the FetchXML window in FXB and it will look nice again πŸ™‚

      <fetch>
      <entity name=”account” >
      <filter type=”or” >
      <condition entityname=”C” attribute=”contactid” operator=”notnull” />
      <condition entityname=”O” attribute=”opportunityid” operator=”not-null” />
      </filter>
      <link-entity name=”contact” from=”parentcustomerid” to=”accountid” link-type=”outer” alias=”C” >
      <filter>
      <condition attribute=”emailaddress1″ operator=”ends-with” value=”.com” />
      </filter>
      </link-entity>
      <link-entity name=”opportunity” from=”customerid” to=”accountid” link-type=”outer” alias=”O” >
      <filter>
      <condition attribute=”statecode” operator=”eq” value=”1″ />
      </filter>
      </link-entity>
      </entity>
      </fetch>

  28. Hello

    Is the maximum fetch number of fetchxmlbuilder 5000?

    miyagoshi

    1. Hi Miyagoshi,
      Technically, it is the page size limit in the platform, not in FXB.
      You can decrease the page size using the “Page size” attribute on the fetch node, but you cannot exceed 5000.
      You can however tell FXB to always retrieve all pages to get all data, by checking “Retrieve all pages” in the options.
      See short how-to: https://www.screencast.com/t/uJH33LF9xX

      Jonas

      1. Tried this method per the video but it still does not return more than 5000 πŸ™

        1. Even after you checked the “Retrieve all pages” checkbox in the options?
          If so, could you please report the issue in the normal channels: In FetchXML Builder, choose menu Help – Feedback for FetchXML Builder.

  29. How do I create a new advanced filter by editing the xml?
    If I choose to upload from file, I have no option to publish to site and if I open from view online, I have the ability to edit the xml.

    1. Hi MOX,

      Since FXB does not generate everything needed to create a new view, you can only update existing views in the environment.
      If you open an existing view and edit the query you can save it or save as a new personal view.

  30. Hi Jonas,

    First I want to say how great it is to find a support topic from 2015 still getting active attention and replies from yourself!
    Because of that, I thought I might also ask for some help if I may. I am working with CRM 2015 (on-premises) and FXB 1.2019.9.1.

    I’m trying to create a fairly simple view to search for all opportunities that do not have any related open activities. My FetchXML looks like this:

    Which appears to work fine (I’ve kept it basic for now while making sure this bit works– I can add more filters and attribute columns in later). However I’m struggling to update a system view with this new query because the Opportunity entity has no option to add the “regardingobjectid” field from the related Activity entity. And without adding that column to the to-be-updated system view, I can’t update the query! I get the error:

    Cannot save view, returned attributes must not be changed:
    Expected attributes:
    name
    opportunityid

    Current attributes:
    name
    ab.regardingobjectid

    The latter field is the one I can’t find a way to include. Are you aware of any way forward?

    1. Hi JS – thanks for the praise πŸ™‚

      I think the solution to this is fairly simple – just remove the line with attribute name="regardingobjectid". This is probably not a field you want to return to the user, especially since you only want the records where this is null.
      You don’t need to include the attribute in the results to be able to filter by it.
      Since FXB only alters the query of a view and not the layout of it, you are not allowed to change the set of columns returned, since that would probably break the view.

      Good luck and feel free to reach out if it doesn’t work!

      Jonas

  31. Huh, apologies, it looks like my earlier comment didn’t accept the fetchxml query I’d pasted in. I’ll try again without the angled brackets…

    My query was:
    fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”
    entity name=”opportunity”
    attribute name=”name”
    link-entity name=”activitypointer” from=”regardingobjectid” to=”opportunityid” alias=”ab” link-type=”outer”
    attribute name=”regardingobjectid”
    /link-entity
    filter type=”and”
    condition entityname=”ab” attribute=”regardingobjectid” operator=”null”
    /filter
    /entity
    /fetch

  32. Is it possible to retrieve all records and not just 5000 per page by tweaking within the FetchXML itself?

    1. It is possible to set the Page size property on the fetch (root) node.
      Just tried setting it to a value higher than 5000, and it is accepted… I didn’t think it would be.
      But it does not matter, 5000 is max for page size, the query won’t return more records than that.

  33. Hi Jonas,

    I’m having an issues with not seeing the column changes in the result set. When I ran the query the first time it displayed correctly. But, when I change the columns, the result set always show the same columns that were in the result set the first time it was ran. Even when I remove those columns it’s always those same columns. The filtering works, but not the columns. Even when I uninstalled FetchXML Builder and reinstalled it, same columns. It’s like something is caught in a cache.

    Thanks,
    Chad

    1. Hi Chad,
      It sounds “impossible” what you are describing, there is no caching going on in the tool, queries are executed and results parsed for each request.
      Could you describe in more detail step by step your procedure?
      Feel free to reach out over email (find the address under Jonas – About in the menu above).

      Jonas

  34. Hi Jonas,

    I connected to my environment and tweaked the XML for a view to accommodate a ‘does not contain data constraint’.

    I executed it and it returned the expected results. After saving and publishing this view back to my environment, the view is visible in my solution but I am unable to see the view when designing subgrids or creating dashboards. Did I miss a trick?

    1. Hi Jayson,
      It seems to be a limitation in the platform that some “complex” queries are not possible to select for dashboards and charts.
      Not showing up for subgrids was news to me, but I suspect that could be the same issue.
      A workaround is to add the view to the dashboard etc before you tweak it using FXB.
      It seems like a shady way to do it, but according to Microsoft these queries should be supported, AFAIK.

  35. Version: 1.2019.12.1
    Does not parse values according to comma-separated when “in” operator is used.

    1. Hi Ferhat – this issue has been reported in the normal channels and a bugfix is on the way, see Issue #277 for a workaround until next release!

Comments are closed.