Ok a bit of a long title for this post but I genuinely couldn’t think of anything better, sometimes many words are needed! Here we go.
When using PowerAutomate and specifically the List Rows Dataverse action to find specific records, using FetchXML instead of (or in addition to!) an OData query in the Filter rows section can sometimes come in handy if you are looking to filter rows based on related tables. Let me explain :p
Imagine that you want to get all Account records in Dataverse that have active Contact records but specifically active Contact records that have an address in London, UK. This can be quite complex to create just using Filter rows and OData but there is an easy way to get what you need using FetchXML – and here is the good part: you don’t need to know anything about FetchXML to do it! You can use the familiar Advanced Find, yay! Here’s how it works…
In the Dataverse List Rows action there is an option to add a Fetch Xml query:
All you need to do is get the FetchXML and paste it in there, here’s how you get it. Firstly, select the Advanced Find icon and select the table you need to list rows for (in this example it would be the Accounts table):
You will then be presented with the Advanced Find filtering – add the filtering as required and then click on Download FetchXML:
A file will download with the FetchXML included in it – open the file and copy the whole FetchXML text:
Back in your cloud flow, paste it in the Fetch Xml query box, should look a bit like this:
That’s it! Your List Rows action will now give you only the accounts meeting the criteria you selected in the Advanced Find. Cool huh?
Happy PowerAutomate Dataverse Filtering 😀
Areti Iles 