FetchXML Builder - A Power Platform Maker's Best Friend

Intro

Let’s talk about FetchXML Builder, the XrmToolBox tool that’s king of querying Microsoft Dataverse. It's a total game-changer for anyone who needs to work with data in Dataverse. Users of all experience levels can greatly benefit whether you are a full time Power Platform consultant, an end-user, or a hobby maker. If you have worked with me, you know how big of an advocate I am for this tool. Here’s why you need it in your toolbox.

What is FetchXML Builder?

It’s a tool in XrmToolBox that makes creating FetchXML queries incredibly easy. No more relying on Views for querying data in Dataverse or generating your FetchXML for use in Power Automate. Sure, there is a time and a place to use out-of-the-box Views, but when you need to get serious about querying data or exporting datasets, FetchXML Builder is the way to go. FetchXML Builder offers an interface that is easy to use. It allows you to build and execute complex queries, convert them into other formats if desired, export datasets to Microsoft Excel, and save your commonly used queries to a file or repository.

Why It’s Awesome

FetchXML Builder can save time on developing reports, flows, data cleanup, data migration, and more.

• Build queries with aggregates, outer joins, “not-in” clauses, multi-level linked entities, and more
• Need C#? WebAPI? Power Automate parameters? SQL or JavaScript? FetchXML Builder has you covered and can convert your query into code snippets to get you started
• Resizable windows, intellisense, and column friendly names make query building easy
• Execute queries to see results instantly
• Export your dataset to Microsoft Excel for easy transformation

For a full list of features, visit the FetchXML Builder site

How can I use it?

Prerequisites

Well, first thing is first, we have to install XrmToolBox

See this post for how to install XrmToolBox and setup your first Connection

Once XrmToolBox is installed and your connection is setup, lets ensure FetchXML Builder is installed. Upon running XrmToolBox, the home page should have a button labeled "Open Tool Library" under the Quick Actions section:

Type FetchXML Builder in the search box

Select the row for FetchXML Builder

An information pane is presented along the right side of the screen. If you have the tool installed, the Install button should be disabled, if it is not, click the Install button.

Once installed, Select the Tools tab

Find FetchXML Builder and select it, XrmToolBox will ask if you want to connect to an organization first. Connect to your organization and open the tool.

Create a Query

Let's build a query to show Opportunity Products related to a particular Opportunity. The tool should open with a clean slate ready for you to build your first query:

Click the second item in the Query Builder tree view, it should have a yellow triangle (similar to a warning sign) and have the label "(entity)"

Once selected, we can pick the entity we want to query. In this instance type or pick opportunityproduct in the Node Properties section

Now that you have selected the entity, you can right click the entity and click Select Attributes to only query for certain attributes of the opportunityproduct entity or you can leave it as is and it will return all attributes of the opportunityproduct entity

Let's add a link-entity to the parent Opportunity

Right click the opportunityproduct entity in the tree view, hover over Add, then select link-entity

Select the Relationship drop down in the Node Properties section and select the relationship from opportunityproduct.opportunityid to opportunity.opportunityid

Entity Name, From, and To fields will fill in automatically

Right click the opportunity M:1 linked entity in the tree view, hover over Add, then select Filter

A filter node and condition node have been added in the tree view

In the Attribute drop down select opportunityid, for Operator select Equal if it is not selected by default, and for Record click the magnifying glass button to select an existing Opportunity in your system

Now that our first query is built, all that is left to do is execute it to view the results. You can execute the query by clicking the blue and yellow Execute button in the ribbon - it looks like a play button

After execution, our results are presented

If we want to view the actual FetchXML we can select the FetchXML view

Users who are experienced with FetchXML can modify this existing query and re-run the query after they make changes

Convert the Query into Other Formats

The query can be converted into the following formats:

• SQL
• C#
• JavaScript
• OData 4.0 (WebAPI)
• Power Automate Parameters
• Power Platform Command Line Interface commands

My personal favorite feature is being able to generate Power Automate parameters. We can generate those parameters from our query by clicking the Convert button in the ribbon and selecting Power Automate Parameters.

The resulting pop-up provides links that when clicked will copy the value to our clipboard. No more typos, no more incorrect filters, no more incorrect expands. We can paste these values right into Power Automate with ease.

Pretty sweet, right?!

Conclusion

As a full-time Power Platform consultant, I could not do my job without FetchXML Builder. I regularly use it to perform data cleanup, export datasets for migration, generate Power Automate parameters, and general data validation. If you are not using this tool already, I would encourage you to do so as it will make your life infinitely easier.

This tool is invaluable - you can thank the individual who developed it, Jonas Rapp, and consider supporting his work here