Filter queries in the List records action using the Microsoft Dataverse connector

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.

List records

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).

AVAILABLE FUNCTIONS

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:

• endswith
• startswith
• contains

Some of these are more obvious than others. Time to look at some examples.

endswith

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.

startswith

Similar to the ends with you can also use the startswith:

Note that both starts with and ends with are not case sensitive.

contains

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:

• length
• substring
• substringof
• indexof
• day
• month
• year
• seconds

When you use these functions you will get messages like