Categories
Flow Office 365 SharePoint Workflows

Common Filters for Microsoft Flow: A Reference Chart for Users of all Kinds

When you need to grab items from SharePoint using Microsoft Flow, sometimes you don’t need EVERY item. Here are a few filters you can apply to get just what you’re looking for.

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.

Basic Operators

OperatorFormatExample
EqualseqDepartment eq ‘Human Resources’
Not EqualsneDepartment ne ‘Accounting’
Less ThanltItemStock lt ‘500’
Greater ThangtReserveFund gt ‘10000’
Less Than or Equal Tole Due_x00200_Date le ‘2019-03-01’
Greater Than or Equal TogeWidgetCount ge ’50’

Joiners

OperatorFormatExamples
And (ensuring both values have to be true)andDepartment eq ‘Human Resources’ and
Country ne ‘Canada’
Or (either value could be true)orDirector/EMail eq ‘joe.smith@123.com’ or
Accountant/Email eq ‘bob.brown@123.com’

Note – you can use multiple and and or conditions in the same query.

String Adjustments

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.

FunctionFormat
Starts Withstartswith(column,’string’)

Example: startswith(Department,’A’)
– this produces a result that returns any item where the Department field has a value that starts with “A”.

FunctionFormat
Ends Withstartswith(column,’string’)

Example: endswith(Accountant/DislayName,’Jones’)
– 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”.

FunctionFormat
Contains (also known as substring) substringof(column,’string’)

Example: substringof(ProjectPhase,’Test’)
– 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 5.1.2.4.7 of the ODATA URL Conventions to find out more about index positions.

FunctionFormat
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.

FunctionFormat
Concatenateconcat(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

FunctionFormatExample
Secondsecond(‘Column’) eq valueday(‘Created’) eq 12
Minuteminute(‘Column’) eq value minute(‘Modified’) eq 31
Hourhour(‘Column’) eq value hour(‘ApprovedDate’) eq 3
Dayday(‘Column’) eq value day(‘ReviewDate’) eq 12
Monthmonth(‘Column’) eq value month(‘SubmissionDate’) eq 8
Yearyear(‘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’).

Math

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.

FunctionFormatExample
AdditionaddWidgetCount add 10 eq 50
SubtractionsubWidgetCount sub 15 lt 30
MultiplicationmulPrice mul 2 gt 5.50
DivisiondivWidgetCount 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.modWidgetCount 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 ‘bob.smith@company123.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.)

Stay tuned!

By Andrew Chomik

I'm an Alberta-based tech guy and writer. I do things with Office 365 and help companies and people understand and use technology. I also love pro wrestling, let's just get that out there.

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.

Like

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!!

Like

Leave a 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