cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markthompson3
Level: Powered On

SharePoint Flow Find Items with Date Less than or Equal to today

Hello,

I have a SharePoint list with a column "Next Review" which is a date column, and I need to get items that have a next review date that is previous to or equal to todays date.

 

Can anyone help with the filter query I would need please? 

 

I currently have a SharePoint Get Items step - and am attempting to use the Filter Query parameter. 


Thanks

 

 

11 REPLIES 11
Super User
Super User

Re: SharePoint Flow Find Items with Date Less than or Equal to today

Hi @markthompson3

 

Your odata query should look something like this:

 

<Field> LE <Expression:utcNow()> as illustrated in my screenshot below. I have used Created in my example, at the point of running it will return everything that was created before the current datetime.

 

Replace the <Field> with the internal name of your field which can be found by going to list settings and then looking at the edit column url. If your field name has spaces in it when its created, you will find your field name will look something like Review_x0020_Date.

 

LE is the operator, i.e. less than or equal to.

 

The in single quotes ' ' create an expression with the forumula utcNow(). You'll see this appear in the intellisense, and this will take the exact date time stamp of when the Flow action is executed. 

 

FlowAnswer-3.PNG

I hope that helps, please shout if there's anything else.





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Super User
Super User

Re: SharePoint Flow Find Items with Date Less than or Equal to today

The filter query would be:

 

Next_x0020_Review le 'utcnow()'

where the utcnow between the apostrophes has to be done as an expression from the expressions tab over on the right.

 

(Edit: sorry, I posted without having refreshed the screen so hadn't seen the previous reply from Matt)
Rob
Los Gallardos

filterquery.png

 

 

markthompson3
Level: Powered On

Re: SharePoint Flow Find Items with Date Less than or Equal to today

Thanks @MattWeston365 and @RobElliott 

 

I've never actually used flows before, and have just realised my next issue - how I loop through these items as I can't find a foreach action (for each entry in this list that matches the query we just completed, I want to be able to email that user with details of that item)

 

Thanks

Super User
Super User

Re: SharePoint Flow Find Items with Date Less than or Equal to today

How are you storing the details of the user? In a column in the SharePoint list?

But you need to add an "Apply to each" action, not a foreach,  and select "value" from the dynamic content on the right.

Rob
Los Gallardos

PhantomSoul
Level: Powered On

Re: SharePoint Flow Find Items with Date Less than or Equal to today

hello @RobElliott , What will be the query for utcnow is greater than or equal to StartDate And less than or equal to EndDate?  TIA

markthompson3
Level: Powered On

Re: SharePoint Flow Find Items with Date Less than or Equal to today

Thanks @RobElliott , I was going to get them from the Sharepoint list item, there is a Field called Owner

Highlighted
Super User
Super User

Re: SharePoint Flow Find Items with Date Less than or Equal to today

hi @PhantomSoul it should be

 

StartDate le '@{utcNow()}' and EndDate ge '@{utcNow()}' i.e. start date is is the past AND the end date is in the future





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

markthompson3
Level: Powered On

Re: SharePoint Flow Find Items with Date Less than or Equal to today

Thanks @RobElliott 

 

Just tested my flow and getting;

{
"status": 400,
"message": "The expression \"Next_x0020_Review LE 2019-03-29T07:18:47.9643857Z\" is not valid.\r\nclientRequestId: fxxxxxxxxxxxxxxxxxxxxxxxx"
}
 
It's a date only field, is there any adjustments  required?
 
Thanks
Super User
Super User

Re: SharePoint Flow Find Items with Date Less than or Equal to today

Hi @markthompson3 just surround your date expression with single quotes ' ' as in my screenshotFlowDate.PNG





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

markthompson3
Level: Powered On

Re: SharePoint Flow Find Items with Date Less than or Equal to today

Thanks @MattWeston365 that worked - if I wanted to include a check for another field

 

Would it be;

flow.PNG

 

and Status = "Open"?

Super User
Super User

Re: SharePoint Flow Find Items with Date Less than or Equal to today

Hi @markthompson3 glad you're getting there 🙂

 

You're almost right with what you put, with odata queries operators are always textual:

 

= eq

> gt

< lt

>= ge

<= le

Full list here in section 5.1.2: https://www.odata.org/documentation/odata-version-3-0/url-conventions/

 

So your next part would be: and Status eq 'Open'





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Helpful resources

Announcements
firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,824)