Today I’m writing about the Filter queries in the List records action when you use the Microsoft Dataverse connector in Power Automate.
Other Filter Queries in Power Automate
Note that the Common Data Service was recently renamed to Dataverse. Screenshots and text in this post and other posts may still refer to the old name
A while back I wrote a post about the OData filtering Get Items action in the SharePoint connector. This has been a very successful post and it is being found by a lot of my followers every day.
Today I’m going to have a look at the similar functionality within the Microsoft Dataverse connectors.
The Learn more link provided sends you to a general post about OData. The more useful link about OData filters can be found on docs.
For my list records action I’m going to have a look at filtering. When I run the list records on the Accounts entity, without any filtering configured, I’m getting a number of records back. One of the has a name of “Test Guy”
Filter queries in the list records action
To just get this item returned an eq filter can be used as shown below:
In a similar way you can also use eq, be lt, gt, ge, le, ne (Equal to, Less Than, Greater Than, Greater than or Equal to, Less than or Equal to, No Equal to).
Like with the SharePoint OData filtering , in the Microsoft Dataverse there are also OData Filter functions available. The following functions are available within the query filters for the Microsoft Dataverse:
Some of these are more obvious than others. Time to look at some examples.
The ends with function is easy to use and will help you filter by the end of the text the value of a field.
the syntax used is endswith(name, ‘text’). In my case the below example gives me my single record back.
Similar to the ends with you can also use the startswith:
Note that both starts with and ends with are not case sensitive.
As substring, substringof and indexof are all not supported even though they are listed in the OData documentation. I was starting to worry about the availability of a function to test if a test field contains any text somewhere in a field. As the contains function is not documented here, I decided to try it anyway.
And this worked! Sometimes you just have to try,something to make it work.
Not Available Functions
The following functions that you may expect are not supported:
When you use these functions you will get messages like
3 thoughts on “Filter queries in the List records action using the Microsoft Dataverse connector”
[…] Microsoft Flow – Filter queries in the List records action in the Common Data Services connector […]
I am trying to use an odata query for a field duedate subtracted by daystonotification (e.g. 3) = now. Suggestions?
i don’t think that you can do the calculations in the OData query directly. You would have to create an additional field that is used to calculate the date.