cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pgc-hawkins
Regular Visitor

Filter Sharepoint:GetItems based on if current date falls between two dates in list

Objective : Use a scheduled flow to send an email with list of all current date " banquet events".  (includes multiple day events that started before current date but end after current date)

 

Data : Sharepoint Online Event List (Banquet Event Calendar)

 

Current Issue : 

 

Filter Array Object : 

  • From Sharepoint Get Items
  • @And(greaterOrEquals(startOfDay(utcNow()), item()?['EventDate']),@lessOrEquals(startOfDay(utcNow()), items('Apply_to_each')?['EndDate']))

Obviously , that will not work.

 

Is there a way to filter all list items and return only items where the current date (startOfDay(utcNow()) falls between the Event List's EventDate and EndDate?

 

Thank you greatly for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
pgc-hawkins
Regular Visitor

I was definitely over thinking the filter on this one!

 

@And(

lessOrEquals(

item()?['EventDate']

,

formatDateTime(utcNow(),'yyyy-MM-ddT00:00:00Z')

)

,

greaterOrEquals(

item()?['EndDate']

,

formatDateTime(utcNow(),'yyyy-MM-ddT23:59:00Z')

)

)

View solution in original post

3 REPLIES 3
Mira_Ghaly
Dual Super User
Dual Super User

@pgc-hawkins 

on your Get Items Filter

Try the below:

le less than or equal

ge greater than or equal

 

StartDate ge 'utcNow('yyyy-MM-dd')' and EndDate le 'utcNow('yyyy-MM-dd')' 

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here


That works beautifully as long as the StartDate is the utcNow().
The situation i come across is if the StartDate is one to two days before utcNow() and the EndDate is ge utcNow()

 

Maybe I should throw in an OR ?


or(

And(

greaterOrEquals(item()?['EventDate'],utcNow('yyyy-MM-dd)),

lessOrEquals(item()?['EndDate'],utcNow('yyyy-MM-dd))

)

,

And(

lessOrEquals(item()?['EventDate'],utcNow('yyyy-MM-dd)),

greaterOrEqualsitem()?['EndDate'],utcNow('yyyy-MM-dd)

)

)

 

I have the feeling I am missing some basic logic in my grey-matter.

pgc-hawkins
Regular Visitor

I was definitely over thinking the filter on this one!

 

@And(

lessOrEquals(

item()?['EventDate']

,

formatDateTime(utcNow(),'yyyy-MM-ddT00:00:00Z')

)

,

greaterOrEquals(

item()?['EndDate']

,

formatDateTime(utcNow(),'yyyy-MM-ddT23:59:00Z')

)

)

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (2,618)