What you might not know about FetchXML Builder

Recently MVP Jonas Rapp was invited by MVP Victor Dantas to the Zero to Hero show. The FetchXML Builder creator held the session FetchXML Builder from Zero to Hero. I listened in and afterwards I felt inspired to write this post to share a few tips and tricks that I had not used before.

I have been missing out and perhaps you have too?!

Background

How did I first meet with this tool? To me it has been known as THE tool that Jonas Rapp built for the community. He had already built lots of useful features to be used by people at the company where he, as well as myself, then worked at. Great times, but enough about that.

I have known of FetchXML Builder probably for as long as it has lived, which is since the 21^st of November 2014. If you like history, you can find the full history here. Though I have not really used it a lot other than trying it out from time to time. Advanced Find together with writing queries by hand without any tool has been my way of working creating these FetchXML queries. Efficient work? Well, I should probably have looked more at this tool at an early stage.

Anyhow, lately I have done some migration job for a customer and part of verifying the results has been to count rows. For this I have used FetchXML Builder, together with the tool FetchXML – View Record Counter. FetchXML Builder was also handy while putting together queries to be used for migration.

When I heard that the Zero to Hero show had invented a new Super Hero (or rather given an existing Super Hero a new outfit), I immediately thought I needed to attend and probably I would learn a thing or two. Supporting the tool creator by being on the show was a bonus!

Now to the features I learned during the session. The features of FetchXML Builder are not hard to understand, in fact this tool is very intuitive, I have just never used these features. So what were those features I had been missing out on? Here we go! No specific order here, just 10 things that I had not payed attention to earlier.

Use comments in your queries

As a writer creates texts some parts might need to be temporarily highlighted as the writer decides what to include in the story and what not to include in order to get that perfect piece of written art. As a pro developer writes code some parts can be temporarily commented out when working towards a fully functional piece of useful functionality.

As a Power Platform maker builds FetchXML queries, the queries might need some tweaks and be worked with in different steps, testing the query, updating it, and also temporarily commenting out certain parts of it before that final and excellent query has been composed.

When creating queries with FetchXML Builder, you can use comments in your queries!

Comments can be seen as having different purposes too by the way. You can use comments to temporarily “remove” stuff or you can use comments to highlight for others and your future self to easier remember why something exists. In FetchXML Builder you can either add a comment below a table, an attribute, filter etc. Or you can “comment out” certain parts of a query.

This is how the Query Builder looks when using comments.

This is how the FetchXML looks when using comments.

I can see that I have been missing out for a long time, comments have existed for quite some time! Handle comments in fetchxml #59.

Use friendly names in queries

If you have worked with the platform for a long time or if you sometimes work with configuration in Dataverse, then you are probably familiar with and comfortable with seeing and using logical names. See this table for an explanation what Dataverse logical names means.

What if you work with Power Apps, Power Automate or Dynamics 365 and you are using FetchXML Builder and you are NOT familiar with the logical names or what if you have talked so much with your customer about the different parts of your solution that the so called “friendly names” are easier to work with.

There is a FetchXML Builder feature to your rescue!

You can easily toggle between using logical names and “friendly names” when building your query! (See my last tip too, which is related to this one!)

Use friendly names in the result view

Even though you might not be afraid of working with and looking at GUIDs, you might want to spend your time on other things than trying to find out the name of a row for a certain GUID. After all, usually a name says more than a GUID when looking at it.

You can easily switch to “friendly names” also for your query result!

This by clicking on Appearance in the bottom of the result view. For instance, very handy if you quickly want to know the owner of a row or some other lookup value. With this feature you can easily see the value in the name column of the row instead.

Aggregate

There are aggregate functions we can make user of in our queries!

I knew this existed for FetchXML, but I have not use frequently and especially not in FetchXML Builder. Good with a reminder! Read more in MS Docs Use FetchXML aggregation about how and when to use it.

Arrow takes us to MS Docs

I 💙 MS Docs. It is like a box of (sometimes a bit hidden) treasures, but if you know where to took you can find lots of useful information.

FetchXML Builder can take me to MS Docs!

Examples below are how an arrow can take you to the MS Docs pages describing how to improve FetchXML request performance (LateMaterialize), use FetchXML aggregation and page large result sets with FetchXML. These are just some examples for where you find the arrows, there are more places too, e.g. under Options.

Filter on custom tables and columns

If I know that I am working with custom tables exclusively, I can filter away all OOB tables so it is easier for me to build my queries.

There are lots of settings to be done, i.e. to select what tables and columns are shown when you build your query.

It is today not possible to say “only use tables and columns in a specific solution”. However there is a new idea for this tool, check it out and give it your vote if you like it! https://github.com/rappen/FetchXMLBuilder/issues/705

Show me some metadata!

I can reach metadata from FetchXML Builder!

For instance, by right-clicking on a table in my query, I reach Show Metadata, with the nice Dataverse icon and all! That means when you are building your queries you can reach the all the interesting details and settings for your tables and your columns 🤓

Examples of details for tables are the schema name, logical name, display name, the columns, relationships to other tables, if activity, if it has notes, if it has activities, can have documents or not and much more!

You can reach metadata for columns when you are viewing metadata for a certain table or if you right-click on a column in the query builder. Examples of details are what type it is (e.g. Integer, String etc.), the description, if it is audit enabled, is managed, is required, the schema name, the logical name, the display name, the localized values etc.

This is an example, here you can see metadata for the Attachment table.

Send error report

You can send an error report to the tool creator from within the tool! This feature makes it easier for us to create an issue on GitHub and by that report strange behavior AND it will contain good info about the error to be read by the tool creator (who can create a fix to the tool if needed).

The button takes me to GitHub and an issue is created. I need to submit it too of course.

Always update and get the latest version

I knew this. Sometimes I am a bit lazy though and just click later when I see a message telling me there is a new version. Not to be recommended! During the demo the tool creator bravely clicked update and that triggered the thought in me that I should always do that too. So the day after when I opened the tool, it was obvious to me what I needed to do. This is applicable both for updates of FetchXML Builder and when there is an update for XrmToolBox itself!

Use shortcuts and become more efficient

I have not thought about that before but there are shortcuts to be used in FetchXML Builder (e.g. CTRL + F to switch between Friendly names in query and not).

More you might have missed

Now a few words about things I knew about this tool, but perhaps YOU have been missing out? I cannot leave any information unsaid, can I?

Classic Advanced Find has been great, but I will just say it, FetchXML Builder is more clever. One example is if you want to use not-in and filter on the child table. Another example is when you need to combine several tables in an outer join and use filters. A third example is using Aggregate. Others have written about this. You can e.g. read more about what FetchXML Builder can do which is not supported in Advanced Find in this old post from CRM Tip of the Day, Tip #1211: Mixing entities to build complex views and in a post from Mark Carrington, FetchXML multiple links.

We got to see a demo of the tool creator using his other tool Bulk Data Updater combined with FetchXML Builder. From a query result, where you might see data missing, you can use Bulk Data Updater, get the FetchXML query you have created with FetchXML Builder and from that query update data and then go back to FetchXML Builder and see the result, by executing the query again. Convenient for looking up errors and then fixing the data!

One very useful feature (or rather several), is the possibility to get help from FetchXML Builder when you are in the middle of creating a cloud flow or e.g. developing a plugin. Here we have the possibility to get help with how to write our OData filter expressions for our Power Automate cloud flows and we can get some code generated for us.

I know we are all very fond of release waves and release plans, but that is not what I am talking about here. I am talking about release notes for Fetch XML Builder. You can reach these from the tool! You can find all release notes here.

What does the future hold? You might be aware of the fact that there is a new way to search for rows, Modern Advanced Find. It was recently announced and Jukka Niiranen gave it a test drive, Modern advanced find test drive. Since you are limited to the tables of the current model-driven app (good for the user working in the app, bad for those who wants to search more freely) and there is no “Download FetchXML” option, FetchXML Builder probably will become an even more important tool in the future.

A new useful feature!

Now to something that was NOT included in the Zero to Hero session. It usually takes some time when I write a post. I start, then put it on the shelf, then continue another day. I simply write when I feel inspired and are in the mood for writing. This time, a new release of FetchXML Builder came out in between me starting to write this post and as of now at the time writing, Some Brand New Features – and more! 1.2022.4.

I cannot write a blog post about FetchXML Builder and not mention a certain very useful new feature. MVP Tanguy TOUZARD made it available in XrmToolBox, Jonas Rapp made it possible to utilize it from within FetchXML Builder. So what is it then? From a result view in FetchXML Builder you can open the row. You can also open related rows (lookups). What’s new and a real time save?

You can relate a certain XrmToolBox connection to a certain browser/profile!

No more “sign in” when you need to look at/open rows and you’re working with different user credentials/different customers. Simply make sure you are running the latest version of FetchXML Builder and start setting browser/profile for your connections!

Missed the session? Don’t worry!

This became an historical post for me. This is the first time in over a year that I asked the tool creator to read a blog post of mine before I publish it. So he did and that is a big step for him, see Stroke // Jonas Rapp. I got some great feedback and he even found some misspellings!

If you missed the session you still have the possibility to watch it on-demand, at a time of your own choice, do it!

Reading list

Here is a good reading list if you want to read some more about this topic.

https://fetchxmlbuilder.com the GoTo page of course!
https://twitter.com/fetchxmlbuilder Give FXB a follow!
https://jonasr.app/category/fetch/ posts by the tool creator himself
FetchXML Builder in XrmToolBox docs in XrmToolBox
rappen/FetchXMLBuilder GitHub repository
FetchXML Builder posts by Mark Carrington
10 features in FetchXML Builder you need to be using by Carl de Souza
Tip #1211: Mixing entities to build complex views by CRM Tip of the Day
Two Most Ignored features of FetchXml by Alex Shlega
Do you use FetchXML Builder? Please consider helping a friend out by Nick Doalman
Jonas, FetchXml Builder and Kingswaysoft by Gustaf Westerlund

Or, even better, you can of course just open the XrmToolBox, download it first if you are not already a frequent user, and start building your own queries using FetchXML Builder!

Fun fact! Did you know that FetchXML Builder is mentioned in MS Docs? 😎 On the page about Developer tools as well as on Community tools and in a green box as a great tip here.