Have you ever wondered what thismysteriousFetchXML Query feature of DataversenList rowsis?
If you come from the world of Dynamics 365, you are probably familiar with this.Otherwise, you’ve probably never heard of FetchXML.
If you work with Dataverse (or Dataverse for Teams), you should.Namely, FetchXML is an easy and powerful way to make complex queries to Data.
Let’s get to know it with an example.
Example – Books and their loans
As an example let’s use the borrowing of booksfamiliar to us from a previous post.
Our data model is simple.We have bookswithanauthorand 0-nbookings.
Let’s get started.
Searching for a field value behind a relation
Let’s imagine a situation where we use Flow to deal with books added last year (Books).We get the books first and then we do something for them.This time, we need (for a reason I haven’t figured out yet) the Date of Birth of the authorofthebook.The value is located in another table (Booking) to which there is a relation from the Bookstable.
This is probably the most common solution.
The problem, of course, is that we make an extra Power Platform service request (Get a row by ID – Author) for each book.
We can solve this by using theExpand Queryfeature (see example), but the easiest way to do this is by using Fetch XML.
Let’s open a model-driven app and use it to examine the books and their bookings.The Advanced Findfunctioncan be found at the top right.
Let’s create a search that returns the books added last year.
Add the desired fields (Edit Columns) to the search result.
In the dialog that opens, selectAdd Columns.
Change the table (Record Type) toAuthorand extract theDate of Birthfield.
Press Resultsto test the query.
Now we have a search that returns both the name of the book and the date of birth of its author.
Of course, you can add all the fields you need in the search result.
However, the real magic lies in the last icon in the search editor.You can download the query you created in FetchXML format!
The downloaded query looks like this.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="tp_book"> <attribute name="tp_bookid" /> <attribute name="tp_name" /> <attribute name="createdon" /> <order attribute="tp_name" descending="false" /> <filter type="and"> <condition attribute="statecode" operator="eq" value="0" /> <condition attribute="createdon" operator="last-year" /> </filter> <link-entity name="tp_author" from="tp_authorid" to="tp_auhtor" visible="false" link-type="outer" alias="a_e8cd14ea7dfbeb1194ef000d3ade7d82"> <attribute name="tp_dateofbirth" /> </link-entity> </entity></fetch>The syntax is simple.It’s easy to add missing fields yourself, edit filter criteria, and more.
And best of all, you can use this in a Flow’sList rowsaction!
This way, we have the date of birth of the author we need available on Flow without any additional search.
Very effective!
But this will only get better…
Calculation of sums and figures (aggregation)
We know the number of pages in each book (tp_pages).How do we determine the average of the pages in a set of books?
The traditional approach would be as follows.
- List all books
- Go through the books and add the number of pages for each book to the variable (page count total)
- Finally, calculate the average of the pages (page count totaldivided by the number of books)
Obviously this does work, but it’s also unnecessarily slow for larger groups and it uses a lot ofPower Platform service requests.
It’s smarter to perform the calculations in Dataverse.That too is possible withFetchXML.This time we have to write the xml by hand.
- add to the top: aggregate = “true”
- define an attribute with a computational value (tp_pages)
- define the type of calculation to perform (sum, avg, min, max, count)
The required snippet looks like this.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true"> <entity name="tp_book"> <filter type="and"> <condition attribute="statecode" operator="eq" value="0" /> </filter> <attribute name="tp_pages" aggregate="avg" alias="AveragePageCount"/> </entity></fetch>And the whole thing is handled in Flow with one function.
And it works!
Rows without any relation
Lastly, let’s go through an example that I wouldn’t even try to do without FetchXML.
Our Bookingtable has aLookupfield to theBooktable.It tells you which book the booking is related to.So how do we get to know all the books that have never been borrowed?That is, books that do not have any rows in theBookingstable.
We’ll use the Advanced Find functionto createa query that returns all books that do not have aBookingsrow associated with them.
The downloaded FetchXML looks like this.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"> <entity name="tp_book"> <attribute name="tp_bookid" /> <attribute name="tp_name" /> <order attribute="tp_name" descending="false" /> <link-entity name="tp_booking" from="tp_book" to="tp_bookid" link-type="outer" alias="ak" /> <filter type="and"> <condition entityname="ak" attribute="tp_book" operator="null" /> </filter> </entity></fetch>This can be used as in the previous examples in Flow.
Similarly, for example, you can search for all Accounts that do not have any contacts associated with them.
Summary
FetchXML is a great tool for working with Dataverse and Flow.For most queries, you can quickly create the body usingthe Advanced Findfunction.After that, you just edit the search criteria inside the Fetch XML to your liking in the Flow.
Aggregation sentences have to be written by themselves, but they also get to the grain quickly.
The end result is often a significant improvement in performance.Fewer functions, fewer Power Platform requests, and faster Flow execution.
However, keep in mind that when using FetchXML, the Flow function returns a maximum of 5000 lines.If you need more, you have to build the paging by yourself.
