Microsoft Flow – Filter queries in the List records action in the Common Data Services connector

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.

ODataQueries in List Records

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”

 

Example Data in OData Filter

To just get this item returned a eq filter can be used as shown below:

basic eq filter

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 Common Data Services there are also OData Filter functions available. The following functions are available within the query filters for the CDS:

  • 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:

startswith CDS

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.

contains

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

“message”: “The \”length\” function isn’t supported.”

 

Advertisements

One thought on “Microsoft Flow – Filter queries in the List records action in the Common Data Services connector

Please leave a comment or feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.