Filter out items by date range

[wwmn]

I'm building a scheduler to sign out items. Right now a customer can select a range of dates, those dates get stored to varStartDate and varEndDate respectively. The next page will show available items during that date range, and should not show items already scheduled. My current code works for the most part, except I found it can double book under the right circumstances.

I followed April Dunnams video Here and tailored it for my needs. She did post a follow-up 

video not long ago about fixing the double booking, however she did not explain how and I can not download her app to compare.

Double booking example:

I have Item #1 reserved for 5/8/2023 through 5/12/2023

When selecting dates 5/8/2023 through 5/13/2023 Item #1 will not show up.
When selecting dates 5/9/2023 through 5/13/2023 Item #1 DOES show up.

Filter('mydatalist',Not(Title in Filter('mydatalist', 'Pickup Date' >= varStartDate && 'Return Date' <= varEndDate || 'Pickup Date' <= varEndDate && 'Return Date' >= varEndDate).'itemrequested'))

I've tried figuring out the >=< filters but I think at this point I've tried so many I'm just running in circles.

[cwebb365]

You're gonna want to put the 'Pickup Date' and 'Return Date' in the same && block like this.  And wrap them in () so they are their own blocks of condition. 

Filter('mydatalist',Not(Title in Filter('mydatalist', ('Pickup Date' >= varStartDate && 'Pickup Date' <= varEndDate) || ('Return Date' <= varEndDate && 'Return Date' >= varStartDate)).'itemrequested'))

*edited original post make sure to reread from e-mail 😛

[BCBuizer]

Hi @wwmn ,

In addition to what @cwebb365 suggested, I figured two further sets of conditions need to be added:

Filter(
	'mydatalist',
	Not(
		Title in Filter(
			'mydatalist', 
			('Pickup Date' >= varStartDate && 'Return Date' <= varEndDate) ||   //Within selected period
			('Pickup Date' <= varStartDate && 'Return Date' >= varEndDate) ||   //Complete overlap with selected period
			('Pickup Date' >= varStartDate && 'Return Date' >= varEndDate) ||   //Begin overlaps with selected period
			('Pickup Date' <= varStartDate && 'Return Date' <= varEndDate)      //End overlaps with selected period
		).'itemrequested'
	)
)

[wwmn]

Thank you! This has worked so far and I have not found any double-booking scenarios yet.

EDIT: not resolved. 

[wwmn]

I thought this had worked, however I found it was still allowing double booking. 

[wwmn]

I have found once something is reserved this solution will block that item out for the entire calendar, even outside of it's reservation dates.

[cwebb365]

How long are you reservations? if they can start and end  outside of the dates selected in the filter then you'll have to setup something to account for that as well. My filter was based on a start or end date appearing in the selected range.  I'm gonna have to think about this one, assuming that's your scenario here. 

[wwmn]

Reservations can be any length of time. 

Even BCBuizer solution, which makes sense on paper, doesn't work. It blocks out the entire calendar if something has a reservation, even it the dates searched are outside of the dates its reserved. It's almost as if it's not reading as a and/or statement and instead it's taking each individual one separately.

I did figure out a way to download and compare April Dunhams fixed app, but the way she resolved the double booking doesn't make sense to me yet. It looks as if she is using another field to show if it's active, but I dont see how she is filtering the dates out. This is what she has now;

Filter(Sort(col_Desks, Title), Not(Title in col_Reservations.DeskText) && Active=1 && Or(DeskSearchFilter.Text in Title, DeskSearchFilter.Text in Description))

[cwebb365 · Accepted solution]

This should work to list anything that falls in that selected date range even if the start and end are outside of the range. 

Filter('mydatalist',Not(Title in Filter('mydatalist', ('Pickup Date' < varEndDate && varStartDate  < 'Return Date)).'itemrequested'))

[cwebb365]

Which using 

a.Start < b.End && b.Start < a.End

Is how you find conflicts. You may have to add ='s to those maybe, depending on how that logic works, but that should check the items falling inside that range.