Today I’m writing about the Filter queries in the List records action when you use the CDS connector in Power automate.
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 Common Data Services 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 Common Data Services there are also OData Filter functions available. The following functions are available within the query filters for the CDS:
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