FetchXML Builder for XrmToolBox

When Stunnware Tools was discontinued, I searched for a tool to create advanced FetchXML queries.

I didn’t find any.

So, I created it.

– Jonas Rapp, 2014

Build queries for Microsoft Common Data Service, CRM, XRM and the Power Platform. Investigate data. Get code. Empower yourself to achieve more.

FetchXML Builder – empowering since 2014

Example of the rich feature set in FetchXML Builder. Click to enlarge.

Features

For a full feature description – see FetchXML Builder – Features.

  • Outline for visual query construction
  • CDS/CRM/D365 metadata intellisense
  • New query from template
  • Open query (from CDS or file)
  • Save query (to CDS or file)
  • Clone query
  • Aggregate query support
  • Undo (CTRL+Z) and Redo (CTRL+Y) support
  • Integration from other XrmToolBox tools
  • Query to FetchXML
  • Query to code (C#/js/SQL/OData)
  • Query to Power Automate List Records parameters
  • Results in view
  • Results serialized (XML/JSON)
  • Query Repository
  • Abort query execution over multiple pages
  • Multiple options and settings
  • Fully configurable window layout

Installation

Like all tools for XrmToolBox, FetchXML Builder is installed from the Tool Library within XrmToolBox.

To install XrmToolBox, see these instructions.


Contributing

FetchXML Builder is freeware and open source hosted on GitHub.
Please submit any issues here.


Continued support

The development and support of FetchXML Builder has undoubtedly consumed a few hours over the years since the initial release in 2014.

If you (or your company) feel that this tool has helped you in your work, perhaps by saving you a few hours of work, donations for the continued support and development are accepted, appreciated, and will grant you a good feeling and perhaps a sticker if you like, and a spot on the FetchXML Builder Supporters Wall of Fame (unless you want to stay anonymous).

You may use the Donate link in the XrmToolBox menu (remember to select the FetchXML Builder option)

You can also use this direct link to the PayPal donations page.


Original documentation

The original documentation is still available, see: fetchxmlbuilder.com/origdocs

74 thoughts on “FetchXML Builder for XrmToolBox

  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

  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. Nice tool!
    I cannot however find this function:
    Generate OData query string from fetch xml
    Where do i find this function?

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

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

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

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

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

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

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

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

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

  15. 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!

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

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

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

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

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

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

    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.

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

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

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

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

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

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

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

Leave a Reply

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