Use dynamic ODATA Filter values in a Microsoft Flow SharePoint GetItems Action

While working on a project for Microsoft recently I needed to use a Microsoft Flow to return all list items from a SharePoint list that were not approved in the last week.

To correctly return the list items, I needed to create this ODATA Filter expression:

Status ne 'Approved' and Date gt datetime'2017-06-08T18:21:58.4807681Z' and Date lt datetime'2017-06-15T18:21:58.5276427Z'

In the code that follows, I have replaced the datetime values with placeholders to demonstrate what portions of the ODATA Filter are dynamic.

Status ne 'Approved' and Date gt datetime'<START DATE>' and Date lt datetime'<END DATE>'

To implement it, first I added a Compose Action to my Flow to create a dynamic start date and time.  In this case, my start date is 7 days ago.

Next, I added another Compose Action to my Flow to create a dynamic end date and time.  In this case, my end date and time is the precise time the Flow executes.

Finally, I created the ODATA Filter expression in the SharePoint GetItems Action and I used the Output values from the previous Compose Actions.

Here’s what it looks like when you bring it all together.  Sing it, all together now…  “Let it Flow, let it Flow, let it Flow!”

Formatting Dates And Times

On a related note, if you wish to apply formatting to the date time values, here’s an example of formatting the start and end dates in Compose actions.

Start Date and Time

Expression: @adddays(utcnow(), -7, 'yyyy-MM-ddTHH:mm:ssZ')

Example output:  2017-06-15T17:28:20Z 2017-06-08T17:28:20Z

End Date and Time

Expression: @utcnow('yyyy-MM-ddTHH:mm:ssZ')

Example output:  2017-06-15T17:28:20Z

Here are some helpful links you can use to learn more about writing ODATA Filter expressions and how to format them.

Using Filter Expressions in OData URIs

https://msdn.microsoft.com/en-us/library/hh169248(v=nav.90).aspx

Workflow Definition Language schema for Azure Logic Apps

https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language

Custom Date and Time Format Strings

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings#the-dd-custom-format-specifier