cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdamD
Helper III
Helper III

Get Items with date column older than one year.

Hello,

 

I'm trying to craft a flow that gets items in a sharepoint list where a DateColumn returns as older than one year and the column IsActive returns as true. So far I've tried variations on the following OData filter query in Get Items:

month(DateColumn) eq @{formatDateTime(utcNow(),'mm')} and year(DateColumn) lt @{formatDateTime(utcNow(),'yyyy')} and IsActive eq true

I know that my date functions crafted in Flow are returning correctly, but the OData query syntax and functions must be off. I tried following the syntax listed here in section 4.5 under the date functions, but I'm a newbie and must be making a mistake somewhere. Any help would be most appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
tom_riha
Super User
Super User

Hello @AdamD ,

you can't use any expression on the SP column when using in the OData query, you must use it as it is, all operations must be done on right side of the condition in the 'value' part.

To get everything older than one year I'd compare the DateColumn with today's date - 1 year:

DateColumn lt '@{addToTime(utcNow(),-1,'Year')}'

and add the IsActive filter, I guess it's a Yes/No column so you should compare it with 1/0 value:

Date lt '@{addToTime(utcNow(),-1,'Year')}' and IsActive eq 1

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

2 REPLIES 2
tom_riha
Super User
Super User

Hello @AdamD ,

you can't use any expression on the SP column when using in the OData query, you must use it as it is, all operations must be done on right side of the condition in the 'value' part.

To get everything older than one year I'd compare the DateColumn with today's date - 1 year:

DateColumn lt '@{addToTime(utcNow(),-1,'Year')}'

and add the IsActive filter, I guess it's a Yes/No column so you should compare it with 1/0 value:

Date lt '@{addToTime(utcNow(),-1,'Year')}' and IsActive eq 1

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

Yes! Thank you so much @tom_riha !

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (3,002)