Fetch Xml vs Filter Queries in Power Automate
When you query rows in Dataverse using Power Automate you have a few options. Fetch Xml and Filter queries are the two obvious options to go for. But when should you use which?
List Rows action
When we compare the Get Items ( from SharePoint) with List rows for Dataverse, there are a few differences
The Dataverse List rows and the SharePoint Get Items both have filter options, but only the Dataverse action has a Fetch Xml Query option.
Fetch Xml
Using Fetch Xml you could collect two field from for example the Accounts table just like this:
The same can also be done by specifying the Select columns in the list rows action.
Fetch Xml has many benefits however. You can filter the items that you want, select the columns but also collect data form multiple tables.
Joining tables
To create a join between two tables can be a real hasle but not when you use Dataverse with Fetch Xml. The following example shows how you can quickly connect the Accounts and the Users table and place some conditions on the way the data is joined up.
<fetch mapping='logical'>
<entity name="account">
<attribute name="accountid"/>
<attribute name="name"/>
<link-entity name="systemuser" to='owninguser'>
<filter type="and">
<condition attribute="lastname" operator="ne" value="Tester" />
</filter>
</link-entity>
</entity>
</fetch> Inner and Outer joins
Similar to how we saw earlier in this post you can also join multiple tables with either inner or outer joins
<fetch version='1.0' mapping='logical' distinct="false">
<entity name="entitymap">
<attribute name="sourceentityname"/>
<attribute name="targetentityname"/>
<link-entity name="attributemap" alias="attributemap" to='entitymapid' from='entitymapid' link-type="inner">
<attribute name="sourceattributename"/>
<attribute name="targetattributename"/>
</link-entity>
</entity>
</fetch>Creating these type of joins isn’t something that is easy to do with flows after you have collected the data form two tables and it is definitely not going to be fast. But when you use Fetch Xml you will get your data back as fast as possible.
Use Fetch Xml or Filter/Select
So when would you use Fetch Xml and when would you use filter queries, and select columns.
First of al, if you are lucky enough that Dataverse is an option (yes it is premium), then Fetch Xml should be the preferred option. If you use SharePoint as a data source then you haven’t got the option to use Fetch Xml. If you are migrating a flow from a SharePoint to a Dataverse based solution you could try to take a short cut, however it is hardly worth it as writing the Fetch Xml has been made very easy with some of the tools available in the Xrm Toolbox.
Especially as the complexity of your queries might increase as you develop your solution, it might be better to use the Fetch Xml as default.
Continue Reading Pieter Veenstra’s Article on their blog
Fetch Xml vs Filter Queries in Power Automate
When you query rows in Dataverse using Power Automate you have a few options. Fetch Xml and Filter queries are the two obvious options to go for. But when should you use which?
Blog Syndicated with Pieter Veenstra’s Permission
More About This Author
Array
Modern Workplace26 March 2024Receive the available storage within your SharePoint Online tenant- Modern Workplace20 March 2024Options for Documenting Your Power Apps: Comments, Code, and Controls
- Modern Workplace18 March 20242 ways to duplicate SharePoint Lists to support your Power Apps
- Modern Workplace14 March 2024Update a Hyperlink Column in SharePoint with Power Automate
