Microsoft Flow – Filter queries in SharePoint Get items

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

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.

Available 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

Get Items action showing startswith(Title, test)

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.

Get Items showing substring of function

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

Get Items showing date check on created datefunction.

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'

 

 

Related articles

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

Advertisements

24 thoughts on “Microsoft Flow – Filter queries in SharePoint Get items

  1. how would we test multiple fields please?

    We need to filter on two columns called T-Code and BoxNo and have tried

    BoxNo eq @{triggerBody()?[‘BoxNo’]} && T-Code eq @{triggerBody()?[‘T_x002d_Code’]}

    but we get an error that the expression is not valid

    Like

      1. Hi It seems that it is not working in the Flow it throws an error like below, stating that only one eq s supported any ideas on it ?

        Error is as below:

        Invalid filter clause: unsupported operation. Only single ‘eq’, ‘ne’, ‘contains’, ‘startswith’ or ‘endswith’ is currently supported.

        Like

  2. This is very helpful. I am still a bit stuck however, trying filter items only older than today so I can update overdue entries.

    I have a Date column named DueDate. Trying to get items where the value of DueDate is older than today. I tried with no success:

    DueDate LT Today()

    Any suggestions?

    Like

      1. Brilliant, this worked perfectly!

        I was stuck trying to get one of my PowerApp galleries to only display SP list items older than today but kept running into delegation limitations. Needed the SP list to be filterable by a Single Line Text field. Now I can run a nightly flow that gets items in the list which are both marked “Active” and have a DueDate older than today = have Flow change the text field entry to “Old”, resulting with my PowerApp now being able to filter it’s gallery items to only display active items properly without delegation problems. (Bit of a crazy workaround, but MS just doesn’t seem to like PowerApps filtering SP lists on anything but the Single Line Text field for large lists)

        Like

  3. Hi! I’m copying from one SharePoint list to another, trying to filter based on a Manual Trigger in which they’re selecting a value of an existing field. So essentially I want the query filter to get the items where field “Budget Field” is equal to the value selected in the drop down of the Manual Flow Trigger. Is this possible?

    Like

    1. Hi Jenny,

      Yes that should be possible the query is probably something like

      Budget_x0020_Field eq ”

      Not that the field name is the internal name of the field ( in list settings click on the field and get this form the url)
      The calue that you are testing for has to be inside single quotes ( ‘ )

      Like

      1. Thank you! The trick was this: Budgeting_x0020_Phase eq ‘@{triggerBody()[‘text’]}’. Everything after the @ is inserted automatically when you select the text field from Dynamic Content – it just needs to be in single quotes.

        Liked by 1 person

  4. How can we test a choice field?

    In our list we have a field called Status, with possible values such as Approved/Pending/Expired etc

    But in our Flow we only see “Status Value” as an option, so when we do Status Value eq ‘Pending’ there is an error?

    Like

    1. Hi Philip,

      Do you have details on the error?

      I’m guessing that the space in Status Value is causing the problem.Status Value is likely the display name of the field rather than the internal name of the field.

      Can you go to the list settings and click on the name of the field. Then as part of the url you will see the internal name of the field.

      My guess is that this should be just Status
      So your query should be:
      Status eq ‘Pending’

      Like

  5. Hi,
    I am using an OData Filter on an excel document that I have stored in Sharepoint.
    When I use “substringof” filter, substringof(@{variables(‘entityValue’)},Description), where “Description” is the name of the lookup column in the excel sheet, i receive an error:

    An unknown function with name ‘substringof’ was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.

    Is “substringof” filter supported by excel online connector, or should I use “contains”?
    I tried using “contains”, but I think I am not using the right syntax.

    Can you please help.

    Like

      1. Thanks Pieter,

        Can you let me know the syntax of using “contains”.
        I searched a lot but not able to find an example where “contains” is used to filter.

        Thanks,
        Ashwin.

        Like

  6. Hi Pieter,

    My goal is to bulk update/create rows in a main Excel sheet which current has more than 15,000 rows, then I have another separate sheet – update to put all rows need to update or create.

    So I list the rows of Update first, then list the main sheet using Key eq ‘Key’, but it will auto apply to all, then I can’t apply the bulk update/create method from John Liu’s post. But I can’t find the rows to update at all if trying to list all rows of the main. Is there a better way? Many thanks, Vera

    Like

Leave a Reply to Pieter Veenstra Cancel reply

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.