In my last article I wrote about how you can ease your life with OData filter in Power Automate. Today we are going to discover how we can use FetchXML queries to advance our filtering in Dataverse and to make it easier as well if it is getting more complex.
What is FetchXML?
Microsoft writes that "FetchXML is a proprietary XML based query language of Microsoft Dataverse used to query data using either the Web API or the Organization service".
How can we use it?
We can use it in our Power Automate Flows while using Dataverse actions to narrow down the items we get from the actions. You can enter the FetchXML query into the defined field:
How to use FetchXML queries?
We could either build our queries manually by hand or we let Dataverse build the queries for us and adjust those as we need it. For this introductory article we let Dataverse build our queries.
How can Dataverse build our queries?
We have two options here which we are going to cover.
How can we build the FetchXML query with the advanced settings?
First, we can use the advanced settings of the Power Platform menu. For this we need to click on the settings wheel and afterwards on "Advanced settings". It does not matter if you do it in the maker menu or in the started app menu.
You will see a page similar to this one and probably as well a lovely warning that you should use the new UI for the Power Platform Admin Center. We opened the page of the old UI.
However, for our purpose we are exactly at the place where we want to be. On the top right you can see a filtering symbol. After clicking on it a new window should open itself.
Here you can see the filtering criteria you have and how you can download your query in the end. Let's see how it works.
You can look for entities/tables and afterwards select the fields you want to filter on. Here, the same OData filter logic applies that we had learned about in the last article. So, you can again use "contains","equals" and so on. It is also important to define the columns you want to show in your view because that are the only columns you can user afterwards in you flow as dynamics variables!
Here we, have an example:
I filtered the well known orders to show me only the ones that contained the word "important" in their names and the Primary Email of the user that created the requests should have "Matthias" in it. The output in the end looks like this:
You can click on "Results" to see you output. It is always a good idea to check if your query works as expected because otherwise you will get no data back in your flow.
As I said you need to add all the fields/columns you want to see and use as your data output! You can do this by clicking on "Edit Columns".
A pop up opens which allows you to add more columns:
Here you can select all the columns of the main entity you selected in the first step and all related entities such as the owning user or creator of the record.
After you added all the columns you need and adjusted your filter query you need to download your FetchXML code and paste it into your flow.
This will download a .xml file which you can open with any note app you want. The next step is to copy the complete code and paste it into our FetchXML Query field.
I needed to crop the screenshot because the image would be too big. Just make sure to copy the complete code into this field.
If you let your flow run you can see that the body of the output of this action will only contain the fields you selected beforehand but also the fields that were defined in the query that are not part of the main entity.
This was the way to get the FetchXML query with the advanced settings.
How can we build the FetchXML query with a model driven app?
The second way to build a FetchXML query we want to cover today is to use a model driven app.
Here, the process is similar and I assume that you already know how to use a model driven app and how you can filter your data to get records you need.
If you open the view of the records you want to filter on you find on the top right the possibility to filter the records and to edit/add columns.
After clicking on "Edit filters" you will see the following window in which you can adjust your filters and also download the filters as a FetchXML query which you afterwards paste into your Power Automate Flow again.
Summary
As we elaborated, we have two possibilities to get to our FetchXML query. First, with the help of advanced settings. Second, with a model driven app. You can prepare your filter with those possibilities and the use the query afterwards in your Power Automate Flow.