Advertisements

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 in Power Automate

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 the settings 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

example

startswith(MyField, 'test')

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

Get Items action showing startswith(Title, 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

35 thoughts on “Microsoft Flow Filter queries in SharePoint Get items

  1. commented on November 14, 2018 by Jonas Björkander

    Super!

  2. commented on November 29, 2018 by UB

    Thanks for sharing, this is really helpful.

  3. commented on January 10, 2019 by Philip

    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

    • The following line should work:

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

      • commented on May 14, 2019 by Nikhil Patel

        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.

      • commented on May 14, 2019 by Pieter Veenstra

        have you used ‘and’ in between can you give the full expression?

  4. commented on February 19, 2019 by Fernando

    Seems like you are missing EQ In the llist of valid operations available listed in the article.

  5. commented on March 18, 2019 by Cliff

    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?

    • Hi Cliff,
      Does this help?
      https://veenstra.me.uk/2019/03/18/microsoft-flow-how-to-find-your-overdue-tasks/

      • commented on March 20, 2019 by Cliff

        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)

  6. commented on April 2, 2019 by Jenny Ward

    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?

    • commented on April 2, 2019 by Pieter Veenstra

      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 ( ‘ )

      • commented on April 2, 2019 by Jenny Ward

        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.

  7. commented on April 25, 2019 by Philip

    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?

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

  8. commented on May 20, 2019 by Ashwin Johari

    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.

  9. commented on August 16, 2019 by Vera

    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

    • commented on August 16, 2019 by Vera

      Please ignore my question, I have figured it out:)

  10. commented on November 21, 2019 by Camilo Echavarria

    Hello Pieter,

    I found a way to deal with dates in the filtering query:
    You can use some part hard coded in the query, and combine it with an expression. The result will be something like ReqDate lt datetime’@{addDays(utcNow(), -365)}’.

    In the user interface for “Get items” just after writing ReqDate lt datetime’ I inserted the expression addDays(utcNow(), -365).

    So we can do this in two ways:

    1. Write in the query directly using the dynamic part inside @{}.

    OR

    2. Write part of the code in the query and combine it with an expression using the user interface.

    I hope this will be useful!

    Best regards,

    Camilo Echavarria.

  11. commented on January 8, 2020 by Dhara

    I have a huge dataset which is approx 0.5 million where I am trying to put a condition on datetime column (stream_load_datetime) which I have in a table to limit it only for a day of data in the filter query while getting rows in flows from Azure SQl data warehouse. Tried to add the following filters but didn’t work:

    1. stream_load_datetime gt ‘2020-01-07’
    2. filter=year(stream_load_datetime) eq 2020
    3. stream_load_datetime gt ‘addDays(utcnow(‘yyyy-MM-ddTHH:mm:ssZ’),-1)’

    Can anyone please help me in finding the correct filter for the same.

    Thanks
    Dhara

  12. commented on January 8, 2020 by Dhara

    Tried using this syntax: stream_load_datetime gt ‘2020-01-07’

    Got the following error:

    Syntax error: character ‘‘’ is not valid at position 24 in ‘stream_load_datetime gt ‘2020-01-07’’.
    inner exception: Syntax error: character ‘‘’ is not valid at position 24 in ‘stream_load_datetime gt ‘2020-01-07’’.
    clientRequestId: 4909c1a3-e205-4fc4-9403-93409771530f

  13. commented on January 8, 2020 by Dhara

    I ran the following syntax: stream_load_Datetime gt ‘2020-01-07’
    Now the error is: Http request failed: the content was not a valid JSON.

    • Can you double check the internal name of that date field in the SharePoint list settings. You can get it from the url when you click on modify column settings.

    • I just tried this in my environment and

      Modified gt ‘2018-10-01’

      did work.

      When I renamed used Modified_123 (this doesn’t exist) instead of Modified

      {
      “status”: 400,
      “message”: “Column ‘Modified_123’ does not exist. It may have been deleted by another user.\r\nclientRequestId: 633139a7-5e2e-46e4-96bc-80d503dce313\r\nserviceRequestId: 633139a7-5e2e-46e4-96bc-80d503dce313”
      }

      So then I tried the following:

      Modified gt ‘2020-01-07’

      and this worked again.

      So it looks like it has something to do with your field configuration.

      Can you create a new date field call it something simple as MyDate (Anything without spaces) and then try again.

  14. commented on January 8, 2020 by Dhara

    Can you please help me in resolving this issue?

Leave a Reply to Philip Cancel reply

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

%d bloggers like this: