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

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
MattWeston365
Memorable Member
Memorable Member

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.

RobElliott
Super User
Super User

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

 

 

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

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

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

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

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

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

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

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

 

Would it be;

flow.PNG

 

and Status = "Open"?

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'

Helpful resources

Announcements

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Super User of the Month | Drew Poggemann

As part of a new monthly feature in the Community, we are excited to share that Drew Poggemann is our featured Super User for the month of February 2024. If you've been in the Community for a while, we're sure Drew's name is familiar to you, as he is one of our most active contributors--he's been a Super User for five consecutive seasons!   Since authoring his first reply 5 years ago to his 514th solution authored, Drew has helped countless Community members with his insights and expertise. In addition to being a Super User, Drew is also a User Group leader and a Microsoft MVP. His contributions to our Super User sessions and to the new SUIT program are always welcome--as well as his sense of humor and fun-loving way of sharing what he knows with others.   When Drew is not solving problems and authoring solutions, he's busy overseeing the Solution Architecture team at HBS, specializing in application architecture and business solution strategy--something he's been doing for over 30 years. We are grateful for Drew and the amazing way he has used his talent and skills to help so many others in the Community. If you are part of the SUIT program, you got to hear some great tips from Drew at the first SUIT session--and we know he still has much more to share!You can find him in the Community and on LinkedIn. Thank you for all you do, Drew!

Super Users 2024 Season One is Here!

   We are excited to announce the first season of our 2024 Super Users is here! Our kickoff to the new year welcomes many returning Super Users and several new faces, and it's always exciting to see the impact these incredible individuals will have on the Community in 2024! We are so grateful for the daily difference they make in the Community already and know they will keep staying engaged and excited for all that will happen this year.   How to Spot a Super User in the Community:Have you ever written a post or asked for help in the Community and had it answered by a user with the Super User icon next to their name? It means you have found the actual, real-life superheroes of the Power Platform Community! Super Users are our heroes because of the way they consistently make a difference in the Community. Our amazing Super Users help keep the Community a safe place by flagging spam and letting the Community Managers know about issues. They also make the Community a great place to find answers, because they are often the first to offer solutions and get clarity on questions. Finally, Super Users share valuable insights on ways to keep the Community growing, engaging, and looking ahead!We are honored to reveal the new badges for this season of Super Users! Congratulations to all the new and returning Super Users!     To better answer the question "What is a Super User?" please check out this article: Power Apps: What is A Super User? - Power Platform CommunityPower Automate: What is A Super User? - Power Platform Community Copilot Studio: What is A Super User? - Power Platform Community Power Pages: What is A Super User? - Power Platform Community

Microsoft Power Platform | 2024 Release Wave 1 Plan

Check out the latest Microsoft Power Platform release plans for 2024!   We have a whole host of exciting new features to help you be more productive, enhance delegation, run automated testing, build responsive pages, and so much more.    Click the links below to see not only our forthcoming releases, but to also try out some of the new features that have recently been released to market across:     Power Apps  Power Automate  Copilot Studio   We can’t wait to share with you all the upcoming releases that will help take your Power Platform experience to the next level!    Check out the entire Release Wave: Power Platform Complete Release Planner 

Users online (4,234)