Unanswered

Filtering date-range in SharePoint List gallery

(0) Share

Report

Posted on by mgudites

I have a form that is attached to a SharePoint Online list. In the form, two of the fields the user is being asked to fill out are "Start Date" and "End Date," and these get populated back to SharePoint when submitting the form. This part is working.

I added a gallery connected to the SharePoint list itself in the form, and I'm trying to filter this to show line items where the Start Date and/OR End Date being specified in the form fall between the start/end dates of existing entries in the SharePoint list. In other words, I'm trying to show people conflicts. "Pick another date because there is some kind of overlap with these existing line-items in the SharePoint list." So for example, if there's an existing entry in SharePoint where Start Date = 8/1/22 and End Date = 8/5/22, and I go to submit a new form with Start Date 8/3/22 and end date 8/7/22, that existing entry in SharePoint should show up in the gallery since there's overlap (not exactly the same, but there's some overlap). However, nothing I do seems to work. It's filtering, data shows up, but it's filtering incompletely. I've tried every logic combo I can think of any nothing seems to work. I need it to show all items from the SharePoint list where ANY PORTION of the date-range being requested on the form conflicts with any entries already existing in SharePoint.

Here's the logic that to me, should work but isn't. Maybe it's something obvious because I've been staring at this for a couple of days and my eyes are starting to go blurry.

Filter
(Reservations,
(
(DateValue(NewResStartDate.SelectedDate) >= DateValue('Reservation Start') && DateValue(NewResStartDate.SelectedDate) < DateValue('Reservation End'))
Or
(DateValue(NewResEndDate.SelectedDate) > DateValue('Reservation Start') && DateValue(NewResEndDate.SelectedDate) <= DateValue('Reservation End'))
)
)

The "NewRes..." ones are the form fields and the "Reservation..." ones are the SharePoint fields.

Basically the logic as I see it is:

Show entries where ("form start date" is gt= to SharePoint.Start and lt SharePoint.End) OR ("form end date" is gt to SharePoint.Start and lt= SharePoint.End)

Categories:

Building Power Apps

I have the same question (0)

All responses (16)

Answers (0)

MVP-Phipps 3,508 Super User 2024 Season 1 on at

Like (0)

Report
What is the error or the output? I notice you have some brackets in the wrong place. Try this:

Filter (Reservations, DateValue(NewResStartDate.SelectedDate) >= DateValue('Reservation Start') && DateValue(NewResStartDate.SelectedDate) < DateValue('Reservation End') Or DateValue(NewResEndDate.SelectedDate) > DateValue('Reservation Start') && DateValue(NewResEndDate.SelectedDate) <= DateValue('Reservation End') )
Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.
LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Twitter: https://twitter.com/phipps0218

Was this reply helpful? Yes No
mgudites 76 on at

Like (0)

Report

What's happening is I'll pick a set of dates, and on the readout there will be a few lines, and it looks ok. Then I'll change the "End Date" on the form to something further out (let's say a couple weeks), and some of those original line items disappear even though they shouldn't. Expanding the date range should mean MORE lines in the readout, not fewer. It's like my logic is wrong but I can't figure out what part. I tried the code you provided; no difference unfortunately.

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (1)

Report
@mgudites ,

Assuming the fields in SharePoint are Date fields, you do not need DateValue() - that converts Text into a Date - also you had way too many brackets (as @phipps0218 alluded to)

Filter( Reservations, ( NewResStartDate.SelectedDate >= 'Reservation Start' && NewResStartDate.SelectedDate < 'Reservation End' ) || ( NewResEndDate.SelectedDate > 'Reservation Start' && NewResEndDate.SelectedDate <= 'Reservation End' ) )

Was this reply helpful? Yes No
mgudites 76 on at

Like (0)

Report

Thanks but unfortunately with/without extra brackets, with/without "DateValue," still doesn't work. When I expand the date range, the gallery is removing entries instead of adding more in.

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (0)

Report

@mgudites ,

Can you please explain exactly what you want to see in the gallery as that code will show any records with start or end dates between the date picket dates

Was this reply helpful? Yes No
mgudites 76 on at

Like (0)

Report

What you said is exactly what I want to happen, but that's not what's happening.

Take a look at these two screenshots of the app. In the first one, I made the date-range July 13 - July 29. The gallery (bottom right) is showing two entries. In the second screenshot, I change the end of the date-range to July 31 (so, I added two days). One of the entries drops off, even though it still falls within that range...all I did was EXPAND the range, so nothing that was on the original 13th - 29th range should have dropped off of the 13th - 31st range.

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (0)

Report

@mgudites ,

Firstly I have to assume the fields 'Reservation Start' and 'Reservation End' are Date only fields in SharePoint (I am curious why you are using DateValue as this would indicate they are Text). Also the code you have is finding items inside existing bookings (so you cannot make another booking) - is this what you are trying to achieve ?

Was this reply helpful? Yes No
mgudites 76 on at

Like (0)

Report

I tried "DateValue" because it was one of a hundred different things I found while Googling a solution; I've tried both with and without. Correct, "Reservation Start" and "Reservation End" are Date Only fields in SharePoint. What I'm wanting this to do is show the user what reservations are already out there, so they'll know the conflicts and not try to book something that's already booked.

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (0)

Report
@mgudites ,

This being the case then

Filter( Reservations, ( NewResStartDate.SelectedDate >= 'Reservation Start' && NewResStartDate.SelectedDate < 'Reservation End' ) || ( NewResEndDate.SelectedDate > 'Reservation Start' && NewResEndDate.SelectedDate <= 'Reservation End' ) )

will return a list of reservations where either the start or end dates are between the two dates in the date pickers.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
mgudites 76 on at

Like (0)

Report

Yep, your logic looks correct to me and that's what I want it to do, but it's not! I just substituted your code and it's still doing the same thing.

Here's an example from today. Screenshot #1 is July 9 - July 29, Screenshot #2 is July 5 - July 29. When I expand the range, it's taking out entries that should still be in there instead of adding more in!

For what it's worth, in screenshot #1, "Steve Stevenson's" dates in SharePoint are July 7 - July 10, and "Laura Customer's" are July 8 - July 15. These two should absolutely still show up when I expand the date range (screenshot #2), but it's taking them out even though they still apply!

Was this reply helpful? Yes No