Tag: ODATA

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

Operator	Format	Example
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’

Joiners

Operator	Format	Examples
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 ‘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.

Function	Format
Starts With	startswith(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”.

Function	Format
Ends With	startswith(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”.

Function	Format
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.

Function	Format
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.

Function	Format
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

Function	Format	Example
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’).

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.

Function	Format	Example
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 ‘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!