Answered

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

(0) Share

Report

Posted on by pgc-hawkins

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!

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (1)

Mira Ghaly 11,415 Moderator on at

Like (0)

Report
Copy link

Link copied!
@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')'

Was this reply helpful? Yes No
pgc-hawkins 12 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

pgc-hawkins 12 on at

Like (0)

Report
Copy link

Link copied!

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')
)
)

Was this reply helpful? Yes No