When you develop flows for SharePoint in Microsoft Flow you will find yourself many times dealing with list items. Adding items, removing items, updating item it is all easy. One of the trickier things is the get items action.

Get Items Action

I will start by having a look at doing things the wrong way. Yes, I like the showing you the wrong way as much as I like showing you the right way.

The Wrong Way

When you get list items do you find that you are getting too many items back and that you need to use conditions or other options within Flow to select the right items?

You will probably find that your flow looks a bit like this. The general structure to look out for is a Condition as the first step inside an Apply to each control while one of the branches of the control is empty.

Get Items followed by an Apply to each with a condition in it

In general this means that you simply collected to many items and your now looping through too many items. This is not a good idea.

 

The Better Way

Within the Get items action there is a Filter Query available. This Filter Query can be used to select the right items.

The problem with this Filter query however is that it isn’t immediately clear what the syntax is. The Tooltip helps a little bit:

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq ‘string’ OR numberColumn lt 123).

For non-developers/citizen developer this might not immediately help.

I’m going to start by having a look at the syntax.

Syntax

The general syntax of a simply query is

fieldname operation value

 

The field names that are used are the internal field names as used by SharePoint. These internal field names can be found within column settings in SharePoint. Simply go to thesettings for the column and in the URL you will find the field name.

The operations can be lt, gt, ge, le, ne  (Less Than, Greater Than, Greater than or Equal to, Less than or Equal to, No Equal to).

This makes it easy to compare a field value to an actual value. however you might find that you need to query multiple field. The easiest approach is the use of the and or or operations.

Finally the value is the value that you are comparing the field name with. Please note that you will need to use single quotes (‘) around the values. For numbers Flow is happy to accept both with and without quotes, however for text values they are required. Therefore you might as well always use quotes.

Functions

Now that I’ve mentioned functions in the Query Filter it might be useful to have a look at the available options and potential ways of using them. The following functions are available within the query filters:

  • endswith
  • startswith
  • substringof
  • length
  • day
  • year
  • hour
  • minute
  • second

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

endswith

example

endswith(MyField, ‘test’)

The above example will select all items where MyField ends with test.

startswith

example

startswith(MyField, ‘test’)

The above example will select all items where MyField starts with test.

substringof

The substringof function is on that you might get wrong the first time you use it. Especially when you are familiar with the starts with or ends with functions. also the documentation link  that you might find when you google is wrong. The better document to look at is Use OData query operations in SharePoint REST requests, although that page doesn’t seem to list all; available functions.

note that you first have to supply the text you are looking for followed by the field value. Therefore the right example is:

substringof(‘test’,Title)

length

The length function give you the length of a text field.

example:

length(Title) gt 10

The above example will give you all items with a title longer than 10 characters.

day, year, hour, minute, second

When you work with dates querying by day can be useful. For example when you want to find all items that were modified on the first day of the month you should be able to use the day

function.

However I couldn’t get the date and time functions to work. The only way to filter by dates is the simpler option of comparing the date field with a specified date.

using something like

Created gt ‘2018-11-25’

 

 

 

 

 

 

 

Advertisements