{"id":1307,"date":"2017-06-19T10:10:49","date_gmt":"2017-06-19T16:10:49","guid":{"rendered":"https:\/\/toddbaginski.com\/blog\/?p=1307"},"modified":"2017-06-19T10:10:49","modified_gmt":"2017-06-19T16:10:49","slug":"use-dynamic-odata-filter-values-in-a-microsoft-flow-sharepoint-getitems-action","status":"publish","type":"post","link":"https:\/\/toddbaginski.com\/blog\/use-dynamic-odata-filter-values-in-a-microsoft-flow-sharepoint-getitems-action\/","title":{"rendered":"Use dynamic ODATA Filter values in a Microsoft Flow SharePoint GetItems Action"},"content":{"rendered":"

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.<\/p>\n

To correctly return the list items, I needed to create this ODATA Filter expression:<\/p>\n

Status ne 'Approved' and Date gt datetime'2017-06-08T18:21:58.4807681Z' and Date lt datetime'2017-06-15T18:21:58.5276427Z'<\/pre>\n
In the code that follows, I have replaced the datetime values with placeholders to demonstrate what portions of the ODATA Filter are dynamic.<\/p>\n
Status ne 'Approved' and Date gt datetime'<START DATE>' and Date lt datetime'<END DATE>'<\/pre>\n
To implement it, first I added a Compose Action to my Flow to create a dynamic start date and time.\u00a0 In this case, my start date is 7 days ago.<\/p>\n
\"\"<\/a><\/p>\n
Next, I added another Compose Action to my Flow to create a dynamic end date and time.\u00a0 In this case, my end date and time is the precise time the Flow executes.<\/p>\n
\"\"<\/a><\/p>\n
Finally, I created the ODATA Filter expression in the SharePoint GetItems Action and I used the Output values from the previous Compose Actions.<\/p>\n
\"\"<\/a><\/p>\n
Here\u2019s what it looks like when you bring it all together.\u00a0 Sing it, all together now\u2026\u00a0 \u201cLet it Flow, let it Flow, let it Flow!\u201d<\/p>\n
\"\"<\/a><\/p>\n
Formatting Dates And Times<\/strong><\/p>\n
On a related note, if you wish to apply formatting to the date time values, here\u2019s an example of formatting the start and end dates in Compose actions.<\/p>\n
Start Date and Time<\/strong><\/p>\n
Expression: @adddays(utcnow(), -7, 'yyyy-MM-ddTHH:mm:ssZ')<\/code><\/p>\n
Example output:\u00a0 2017-06-15T17:28:20Z 2017-06-08T17:28:20Z<\/code><\/p>\n
End Date and Time<\/strong><\/p>\n
Expression: @utcnow('yyyy-MM-ddTHH:mm:ssZ')<\/code><\/p>\n
Example output:\u00a0 2017-06-15T17:28:20Z<\/code><\/p>\n
Here are some helpful links you can use to learn more about writing ODATA Filter expressions and how to format them.<\/p>\n
Using Filter Expressions in OData URIs<\/p>\n
https:\/\/msdn.microsoft.com\/en-us\/library\/hh169248(v=nav.90).aspx<\/a><\/p>\n
Workflow Definition Language schema for Azure Logic Apps<\/p>\n
https:\/\/docs.microsoft.com\/en-us\/azure\/logic-apps\/logic-apps-workflow-definition-language<\/a><\/p>\n
Custom Date and Time Format Strings<\/p>\n
https:\/\/docs.microsoft.com\/en-us\/dotnet\/standard\/base-types\/custom-date-and-time-format-strings#the-dd-custom-format-specifier<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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 [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":1321,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[103,120,104,97],"tags":[42,113,5,6,45,7,43],"class_list":["post-1307","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cloud","category-flow","category-office-365","category-sharepoint","tag-cloud","tag-flow","tag-helpful-code","tag-how-to-sharepoint","tag-o365","tag-sharepoint","tag-sharepoint-online"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/posts\/1307","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/comments?post=1307"}],"version-history":[{"count":3,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/posts\/1307\/revisions"}],"predecessor-version":[{"id":1320,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/posts\/1307\/revisions\/1320"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/media\/1321"}],"wp:attachment":[{"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/media?parent=1307"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/categories?post=1307"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/tags?post=1307"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}