cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maheshkhisteLiv
Frequent Visitor

Flow get items ODATA expression to filter based on date difference

I would like to build a flow and part of which is a step where i would like to get all of the items from Sharepoint doc lib (its a document sets) , all of the doc sets is having a date column expiry date. Flow will run daily get all of the items where expiry minus today date less of equal to  180.  Once i get these items , i will need to check each item if expiry condition has been meet something like expiry minus today date equlas 30 days then send notifications.

 

From overall i think it should look like  below but its giving invalid expression error

addays(formatDateTime(item()?['expiry_x0020_date'], 'M/D/YYYY'),-(formatDateTime(utcNow(), 'M/D/YYYY ')),'180')

 

Appreciate any help!

 

1 REPLY 1
v-xida-msft
Community Support
Community Support

Hi @maheshkhisteLiv,

 

Could you please share a screenshot of your flow's configuration?

Do you want to get all items where expiry date minus today's date less or equal to 180?

Further, do you want to use OData query to filter these items?

 

I think there is something wrong with the formula that you provided. It is no simple to calculate the difference between two dates in Microsoft Flow currently. If you want to use OData query within "Get items" action to filter these items, I have made a test on my side, please take a try with the following workaround:

  • Add a "Recurrence" trigger, Interval set to 1 and Frequency set to Day.
  • Add a "Get items" action, specify Site Address and List Name (Library Name). Within Filter Query field, type the following formula:
expiry_x0020_date le 'formatDateTime(...)​'

The formatDateTime() expression read as below:

formatDateTime(addDays(utcNow(),180),'yyyy-MM-dd')

Note: The formatDateTime(...) expression is wrapped with single quotes.

 

Image reference:24.JPG

The flow works successfully as beliow:25.JPG

 

The whole flow's configuration that you want to achieve as below:26.JPG

Within Condition box, type the following formula:

@equals(formatDateTime(addDays(utcNow(), 30), 'yyyy-MM-dd'), items('Apply_to_each')?['expiry_x0020_date'])

 

 

More details about OData query in SharePoint, please check the following article:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-...

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,737)