An OData filter query can optimize your flows in Power Automate dramatically.
First collecting all items and then use conditional logic to do what you want to do is just not good enough.
SharePoint Get Items action
Table of Contents
Before reading this post you might also want to have a look at the new still in preview version of filter queries.
Easier way to manage Filter Queries using the experimental features
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 SharePoint Get Items action. Quite quickly you will find that you need to understand OData Filter query option.
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.
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 with an OData filter query
Within the SharePoint 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. The other problem is that this syntax is actually wrong!
recently I tried the following and it didn’t work!
stringColumn eq 'string' AND numberColumn lt 123
It didn’t work until I changed it to the following filter query
(stringColumn eq 'string') and (numberColumn lt 123)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.
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 in the Filter Query 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 is not supported.
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 in Power Automate. The only way to filter by dates is the simpler option of comparing the date field with a specified date.
using something like the following as a query should work:
Created gt '2018-11-25'
But I have seen the above fail when the wrong format for the date is used. So be careful.
Then I created a new list and created a new list with dates and it worked as shown below. Both gt and eq worked for date time fields that included and the ones that didn’t include the time.
So the last example shows us how to filter by a date, but what if there are empty date fields. Can we filter those out?
Yes we can, please see my post on how to filter out empty dates.
Related Power Automate articles
Filter queries in the List records action in the Common Data Services connector
Super!
Thanks for sharing, this is really helpful.
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’]}
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.
have you used ‘and’ in between can you give the full expression?
Seems like you are missing EQ In the llist of valid operations available listed in the article.
Thanks, I added the EQ option.
[…] I was asked about OData filters in Microsoft Flow. In the past I wrote a post about OData filters. When you try to query with the startswith function it is possible to get all the items in a […]
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/
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)
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?
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 ( ‘ )
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.
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’
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.
The substringof is indeed not supported for the Excel connector.
https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#known-issues-and-limitations
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.
When you want to check that a Description field contains the text SharePoint you can use this:
contains(Description,’SharePoint’)
[…] for more info on O-Data check out this post from Pieter Veenstra – Flow MVP https://veenstra.me.uk/2018/11/12/microsoft-flow-filter-queries-in-sharepoint-get-items/ […]
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
Please ignore my question, I have figured it out:)
[…] for more info on O-Data check out this post from Pieter Veenstra – Flow MVP https://veenstra.me.uk/2018/11/12/microsoft-flow-filter-queries-in-sharepoint-get-items/ […]
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.
Thank you very much. That looks like a great solution.
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
Hi Dhara,
Can you try:
stream_load_datetime gt ‘2020-01-07’
The quotes in your option 1 are different
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
Hi Dhara,
It sounds like you are using back ticks rather than single quotes.
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.
Can you please help me in resolving this issue?
Hi Pieter,
I had to do a Filter Query using a calculated date expression which seems to be issue for one of the replies above. Full details here: https://powerusers.microsoft.com/t5/Building-Flows/Get-Items-Filter-Query-to-select-null-dates/m-p/456275#M54423
Tks
Nigel
Thank you for sharing
Hi Pieter, how would I use a field from an excel table to filter the share point list?
Do you mean a value in a specific cell in an Excel file? I would read the content of the excel as mentioned in this post:
https://sharepains.com/2018/10/17/microsoft-flow-read-large-excel-files-within-seconds-without-creating-tables-using-microsoft-graph/
Then once you’ve got the data use it to filter in a SharePoint connector action.
I have a column named “EventID” in a SharePoint list. But when in the OData filetr query I am using EventID eq ‘Id’ (note: this Id is the event Id in outlook event) I am getting below error
The expression \”EventID eq ‘AAMkAGQ0M2Y0YzhkLTAxMzktNGU3Ny04MjcxLTY0NDE1NGYyODc5MQBGAAAAAADlJ_DHdsrtS7Mu4R5VMJ9aBwDQYdHYDxEPT4DQiZSgsjDqAAAAAAENAADQYdHYDxEPT4DQiZSgsjDqAAAZqMmjAAA=\” is not valid. Creating query failed.\r\nclientRequestId: 2c53b966-f454-4672-ba72-a661db4c2518\r\nserviceRequestId: 2c53b966-f454-4672-ba72-a661db4c2518″
I have also tried with Initialize Variable but got the same error. But if I put the Id value like below way
EventID eq ‘AAMkAGQ0M2Y0YzhkLTAxMzktNGU3Ny04MjcxLTY0NDE1NGYyODc5MQBGAAAAAADlJ_DHdsrtS7Mu4R5VMJ9aBwDQYdHYDxEPT4DQiZSgsjDqAAAAAAENAADQYdHYDxEPT4DQiZSgsjDqAAAZqMmjAAA=’ then it is working fine. Can you please help me how I can solve this problem?
Can you check that you have single quotes around the value just after the eq?
Can you please tell me how to filter out anything that contains a certain substring. For example I am using Get Items from SharePoint Online and want to filter out any items where the Owner field contains ‘Error’
I see I could use substringof(‘Error’,Owner) to select them, but how do I select everything except them?
HI Kurtis,
Unfortunately there isn’t a nice answer for this.
I got as far as this in the past:
https://sharepains.com/2019/03/14/microsoft-flow-time-to-look-at-negative-queries-with-odata/
Hi, please be aware that the length filter query doesn’t work
I just noticed the same. I will remove that option from the post. Thank you for letting me know.
Hi Pieter, it’s always great to read your posts. Thank you :-). I wonder if you have any solution for the following:
I have a person or group column in a SPO list which can hold more than one person.
I need to either perform an ODATA filter in the Get Items action or in a later FILTER action on the column to determine if a specific person is included in the column and return the list item(s) if they are.
Any ideas? I’ve spent hours searching and testing various methods but am getting nowhere.
I tried using contains on a single person field too, but always seem to get an empty array back (tried email address and part of name; I even tried a ‘.’).
Thank you again 🙂
You could use a select action. Feed it with the peoplefield.
Now you will have an array of email addresses. Use the join function in a compose action to create a ; separated list. Now contains will work on that string.
Thanks for your article. I’m not sure I understand your suggestion for Cologne Claret’s issue. If I want to filter a SharePoint list by a multi person field that CONTAINS a specific person, among other people, how to do that with the ODATA filter? Your suggestion seems to be to look at each item individually. But that means I have to loop through all list items. I just want to return the list items where the person is contained in the multi person field. Is that possible?
There isn’t a simple ODATA filter for multi people fields. Hence the collect the data and then process it afterwards approach. It is indeed very disappointing that things like this are not available. But then the underlying REST API/Graph API fitlers don;’t support filtering on related items. When you see people as a related list/table that direct filtering is not possible.
I’m not getting the expected results when trying to filter by both an eq and ne as below. I know there is 1 result, but it returns empty.
Project_Status eq ‘Archived’ and Status ne ‘Archived’
I’ve also tried wrapping them in () with no effect.
(Project_Status eq ‘Archived’) and (Status ne ‘Archived’)
Is this not supported?
That should work. Can you enable the experimental preview features and then within the new UI you should be able to select the fields that you want. Then disable the preview and see what the query is.
See also
https://sharepains.com/2021/01/19/filter-query-get-items-power-automate/
Hi Pieter,
How can I makes multiple groups of conditions, like if (this and this) or (this and this)?
you can use this:
(true and false) or (false and true)
Notice that each and/or can only have two parameters hence the brackets are needed.
Hello! Great post. I am attempting to use Get Items – Filter Query to find part of a subject line. In the subject line I have a calculated field (UNIQUE ID) which brings in last name and created date.
UNIQUE ID: Bailey-2021-11-17-0735
Subject line (when it is received) : [External Email]Re: Bailey-2021-11-17-0735
I have tried many attempts but my logic is this.
substringof(‘SUBJECT’, UNIQUE_x0020_ID)
Just cannot get this thing to lay down. Help! 🙂 Thank you in advance.
Hi Jordan,
Your example of:
substringof(‘SUBJECT’, UNIQUE_x0020_ID)
Will test if the letters SUBJECT are found in the field with the internal name UNIQUE_x0020_ID. I’m suspecting that you are trying to query the content of the field UNIQUE_x0020_ID with the content of the field subject. That isn’t possible. You can only query by values not by fields.
Thank you Pieter. Yep I was trying to query the content of the dynamic expression ‘SUBJECT’ from my outlook subject line to the unique id in my sharepoint list. Bummer. Thank you so much for your quick response. You now have a dedicated supporter/follower.
-Jordan
I am glad that I managed to help you.
Hi.
Need to query on a SQL column
Column name is ‘Material’
It contains all the Material number information
Ex:
0031-675-983
9842-346-980
6547-346-765
If i want to do a partial search in SQL DB we use the LIKE operator. Can we have a similar search in Power Automate filter.
If i search for 346 it should return the second and third rows.
I have tried using “Contains” and substring(‘value’, Column) and both of them didn’t work. Can anyone suggest any alternative.
Thanks,
Anand
have you tried the function : substringof