Microsoft Flow: OData filter query

Why?

In Microsoft Flow (Power Automate) actions like Get items or Get files, can be filtered using the URI Convention of OData. A syntax in flow using OData filter can be quite challenging for a first time 🤓

What?

I’ll be sharing some of my real life examples, hoping that these examples can be a good reference for anyone in need:

Please note that values from dynamic content or variables need to be placed between single quotes ‘[dynamic content]’.

Also please note that for SharePoint Online connections, you need the internal column name as explained here: https://knowhere365.space/sharepoint-get-the-column-internal-name/.

How?

Sorting Results

Sorting is pretty straight forward because you can use a field you want followed by the ascending or descending abbrevation that you need:

LinkFilename asc

Sorting by multiple fields need to be separated by a comma:

Created desc, LinkFilename asc

Filtering Choices

Example: I want to get files from a SharePoint Online Document Library containing a lot of documents, but only the files where a single value choice field named FlowType is equal to the selected FlowType choice from the triggering item of the Flow. The Filter Query:

FlowType eq '@{triggerBody()?['FlowType']?['Value']}'

Filtering Dates

Example: I want to get the items from a SharePoint Online List that have been modified in the last week. I convert this to the business rule of items where the Modified date and time column is greater than now minus 7 days. The Filter Query:

Modified gt '@{addDays(utcNow(),-7)}'

Filtering Contains

Example: I want to get files from a SharePoint Online Document Library containing a lot of documents, but only the files where a single line text field named SchaalTextSingle contains the selected value of the single value choice field named Schaal combined with the AND operator of a single value choice field named Communicatie taal (internal column name Communicatie_x0020_taal) contains the selected value of the single value choice field named Communicatietaal. The Filter Query:

substringof('@{triggerBody()?['Schaal']?['Value']}',SchaalTextSingle) and substringof('@{triggerBody()?['Communicatie_x0020_taal']?['Value']}',Communicatietaal)

Filtering Multiple Conditions

Example: I want to get content from a SharePoint Online Document Library which contains multiple content types, but only two types of content types and only those of which a date column named AlertDate is equal to today. The Filter Query:

(ContentType eq 'Sub Contract' or ContentType eq 'Contract') and AlertDate eq '@{formatDateTime(utcNow(),'yyyy-MM-dd')}'

Example: I want to get the items from a SharePoint Online List where a single line text field named FormApplicantEmpNumber is equal to the text of the single line text field named FormApplicantEmpNumber combined with the OR operator of a single line text field named FormApplicantText contains the single line text field named Title.
The Filter Query:

FormApplicantEmpNumber eq @{triggerBody()?['FormApplicantEmpNumber']} or substringof('Attachment',Title)

Filtering HTTP Request in Uri

Example: I want to use the Send an HTTP request to SharePoint and simultaneously filter items in the same request:
The Filter Query in the Uri of the HTTP request:

_api/web/lists/getbytitle('@{variables('varDocLibTitle')}')/Items?$Filter=PDtype eq '@{outputs('Compose_-_DocTypeFilterString')}'&$select=ID,PDtype,PDstatus,Created,Modified,FieldValuesAsText/FileRef&$expand=FieldValuesAsText

The part after &select is to make sure the results of the HTTP requests are limited to the desired columns (ID, PDtype, PDstatus et cetera) and even the File name (FileRef) is returned as one of the properties in the results.

Other usefull resources:

• Microsoft Flow blog post on OData = https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Every-Power-Automate-MS-Flow-Filter-Query-You-Ever-Wanted-To/ba-p/410220