Recently I have been helping clients migrate workflows from on-premise SharePoint sites to Office 365. Quite often I find myself re-building these workflows in Microsoft Flow, the successor in many ways to SharePoint Designer. The tool itself has been a great solution for many types of workflows, particularly for re-creating Site workflows that need to query multiple items in a list and perform actions on them
In order to do this, you use the Get Files (Properties Only) or Get Items actions quite frequently, which means you have to instruct Flow what to grab from the list.
The last thing you want to do is retrieve every item in a SharePoint list or library, especially if those lists and libraries are quite large and you only need to grab a few items with specific metadata conditions. In order to do this, you will need to use ODATA filters in your query (not the most friendly way for business users but something we’ll just have to live with for now).
Below is a chart of ODATA commands that you can use to help build your queries with the Get Items or Get Files actions.
|Equals||eq||Department eq ‘Human Resources’|
|Not Equals||ne||Department ne ‘Accounting’|
|Less Than||lt||ItemStock lt ‘500’|
|Greater Than||gt||ReserveFund gt ‘10000’|
|Less Than or Equal To||le||Due_x00200_Date le ‘2019-03-01’|
|Greater Than or Equal To||ge||WidgetCount ge ’50’|
|And (ensuring both values have to be true)||and||Department eq ‘Human Resources’ and |
Country ne ‘Canada’
|Or (either value could be true)||or||Director/EMail eq ‘email@example.com’ or|
Accountant/Email eq ‘firstname.lastname@example.org’
Note – you can use multiple and and or conditions in the same query.
The below filters can be used to find and/or adjust values (strings) in a column. While not a complete list, these are commonly-used operators I have used in the past.
– this produces a result that returns any item where the Department field has a value that starts with “A”.
– this produces a result that returns any item where the Display Name value of a People Picker field named “Accountant” has a value that ends with “Jones”.
|Contains (also known as substring)|| substringof(column,’string’)|
– this produces a result of getting any items where “Test” is somewhere in the ProjectPhase field of the item.
Note: You can also be more specific in where the string lies using targeted positions. See section 126.96.36.199.7 of the ODATA URL Conventions to find out more about index positions.
|Index Of||indexof(column,’string’) operator ‘value’|
Example: indexof(Title, ‘Red’) eq 0
– this produces a result to grab items in the Title field that contain the value “red” and look at the first position in the string (the beginning). If you set the eq to 1, it will find “ed” from the “Red” value.
|Concatenate||concat(concat(Column1,’, ‘), Column2)|
Example: concat(concat(LastName,’,’), FirstName)
– this produces “Chomik, Andrew”.
Note: You can use any character as you see fit to concatenate with (I happen to use the comma in the above example).
Dates and Time
|Second||second(‘Column’) eq value||day(‘Created’) eq 12|
|Minute||minute(‘Column’) eq value||minute(‘Modified’) eq 31|
|Hour||hour(‘Column’) eq value||hour(‘ApprovedDate’) eq 3|
|Day||day(‘Column’) eq value||day(‘ReviewDate’) eq 12|
|Month||month(‘Column’) eq value||month(‘SubmissionDate’) eq 8|
|Year||year(‘Column’) eq value||year(‘Birthday’) eq 2019|
Note: Numeric (integer) values do not require any delimiting punctuation (e.g. the single quotations). And you must use the military time format.
Additionally, you can use the full string provided in a Date/Time column (depending on the format of your Date and Time column in SharePoint). You can just use the format provided in the basic operators section (e.g. DateField eq ‘2018-03-02’).
Sometimes you may need to do a bit of math on a SharePoint column. The Math operators can help you find specific values. Combine the following operators with your required result using the basic operators.
|Addition||add||WidgetCount add 10 eq 50|
|Subtraction||sub||WidgetCount sub 15 lt 30|
|Multiplication||mul||Price mul 2 gt 5.50|
|Division||div||WidgetCount div 4 gt 5|
|Modulo (use this to specific the remainder amounts you may want). The example shows when you want to divide a column value by 5 and have no remainder left over.||mod||WidgetCount mod 5 eq 0|
Other ODATA Filter Things to Know when using Flow
- Flow will only recognize the Internal column names to be used in the ODATA Filters. You might have a Column with the Display Name called “Department” but the Internal name may have been created as “Company Department”. You will have to use the latter value, and ensure that your reference is encoded correctly when using it because of the space. Best thing to do is go to the SharePoint list settings, click on the column you want to use, and grab the Internal Name from the end of the URL.
- Use the “null” value if you want to operate with items have an empty field. For example, you could input “Department eq null”. This is retrievable command from the Expressions area in Dynamic Content.
- If you have already fetched items, you can use Dynamic Content in between the single quotes for your filter values (see picture below)
- When returning some values, there may be more than one value you need in the item. For example, filtering on a People column will mean you need to target a particular piece of the profile. In this case, use the “column/value”. If you want the email of an Accountant, you would create the filter “Accountant/EMail eq ‘email@example.com”.
In the future, Ion Works will show you how to use common expressions in your Get Items actions that can be useful to get the data through the workflow and give you targeted results for your data (which also applies for usage across Flow actions, including Compose, Variables, Conditions, etc.)
4 replies on “Common Filters for Microsoft Flow: A Reference Chart for Users of all Kinds”
Hi great article, thanks!
Would you possibly know how to use the filename of a fiel stored in SharePoint as the value to filter on?
e.g. If I try to filter with Startswith(Name,’abc’) I get an error saying the column Name does not exist (even if it is in the document library by default.
Using: Startswith(Title,’abc’) will work, but I really need to use the Name column.
This is the list of internal names for sharepoint fields :
I’ve been trying to filter on a Person or Group field and kept getting a “Exception from HRESULT” error. I’ve literally been spending hours trying to find a solution to that vague error and stumbled across your article. As specified in your “Other ODATA Filter Things to Know when using Flow” section, I ended up using “ColumnName/Name” vs just the column name in my Filter Query and it works perfectly now. Thank you SO much!!
You’re welcome Vishnu! Glad the reference chart helped you out.