Oh man, this is a complex topic and I’ve been writing you a very long reply… which isn’t totally done. I’m just sending you what I have now as I think it’s enough to answer your question!
I’m going to answer this in kind of general terms as there are a zillion ways that one might store one’s events. It looks to me as if my system (GRUPZ.com) has a similar data structure to yours. In my system a property is a “Listing”, a Listing has a Calendars List (which is a list of Calendars, duh ), and a Calendar is where we find various types of Events.
(For reasons that I will not belabor here, I have a couple of different datatypes that represent events, but I’ll keep this description simple-ish.)
The important thing about Events (which might have any number of fields in your system) is that they have a Start Date and an End Date. When they are created (however it is that you create them) or modified, you might consider also storing those ALSO as a date range field on the Event (this would keep you from having to do it programmatically later).
As you know, the date range can be constructed at any time as Start Date<- range ->End Date.
For an Event, let’s call the range from Start to End the “Event Range” (so I can refer to this concept later in the discussion).
As @simon noted here, some systems will store individual dates within the range. (Like an event that starts 1/1/19 and goes to 1/4/19 might have an array on it with with dates 1/1/19, 1/2/19, 1/3/19 and 1/4/19 in it.)
This concept IS NOT helpful in Bubble (go ahead and try to create such a list – there is no built-in way to do it). Fortunately, it is entirely unnecessary. Took me forever to figure that out. (Aside: Certain date-related operations are conceptually simpler and potentially more performant if they could be done on a list of dates. But Bubble does not make that easy – or even possible – for us, so we should just forget about that and forge ahead!)
BACK TO THE ACTION:
So, somewhere there is a list of Events that have Starts and Ends and now we have a User who wants to stay somewhere checking in on some Start Date and checking out on some End Date.
How can we know which Listings are available over those dates?
Conceptually, it’s like this:
If the User’s selected date range (User’s selected Start Date ← range → User’s selected End Date) overlaps with any Event Range of any Event that is associated with a Listing… that listing is not available.
How do we express this in Bubble terms?
FIRST STEP: Let’s get ALL Listings.
It is easiest to sort of “back in” to the solution. I would encourage you to do the exercise I’m going to talk about as the way to create our search page.
Let’s create a repeating group. The source for this repeating group will be ALL Listings. Let’s also slap down our date pickers of choice for the User’s selected Start and End dates. All that matters with those is that we get a Start and End so we can make a range from them. The UI does not matter for purposes of this discussion. Something like this:
And the repeating group’s source is just “every Listing that makes any sense” (some of my Listings may not have calendars yet, etc. so I’m just doing a basic filter on it). Also, I’ve set it up so we could dynamically sort the list by various fields should we like. But basically, this is just “every Listing”:
SECOND STEP: Let’s get each cell to tell us if that cell’s listing is available or not, when the User has selected a date range.
Let’s not start by filtering. Let’s start by just differentiating. Put a text element in the cell where we will report whether this cell’s listing is available or not:
Conceptually, what this text needs to do is the following:
Look at this Listing’s Events (wherever the hell they are stored). If any of those Event’s Starts ← range → Ends overlap with the User’s selected dates, this Listing is “booked” over those dates and is not available. So we filter that list of Events by that criteria. If the resulting list IS NOT EMPTY, this listing is booked.
So, whatever your Events list is, let’s filter EV using the “Advanced” filter like this:
This Event’s date range overlaps with the User’s selected date range
In my system, it looks like this (look at the blue expression):
In my system the Events (iCalFromURL Event) do not already have ranges. So I construct them in the expression. “RepeatingGroup’s Listing’s Selected Range” is just where I’ve shoved User’s selected Start ← range → User’s selected End, OK?
So what you’re seeing in the yellow expression above is conceptually the following:
“Is This cell’s Listing’s Events, filtered by ‘does the event overlap with the User’s selection’ empty?”
If that is true, the listing is available. If that is false, the listing is booked. So I report that by formatting that boolean expression as text:
If we’ve done this right, in run mode, when the user selects a range, they will see a list of ALL Listings, but some of them will be marked as booked. Like this… The user has selected:
And somewhere in our list, we will see both available and booked listings (if indeed some are booked over those dates). Like here:
AWESOME! Now we know how to differentiate between booked and available listings.
STEP THREE: Let’s filter those booked events.
Now we can filter, yeah? So the way I like to do this is to put a condition on the repeating group (it’s conceptually the most straightforward way). So, when the user has selected a range (start and end are defined and we can make a range), we change the DATA SOURCE for the RG to a filtered list:
This gets a little hairy… See how there’s two levels of filtering?
We’ve got a top-level filter (Listings:filtered) and then there’s a filter underneath that (Events:filtered). “Holy shit, bro!” I know. Mind blown. Stay with me…
The first-level filter condition is this:
Search for essentially all listings, sort them the way we want, and then do an Advanced filter:
“Is some filtered version of this Listing’s Events empty?”
But what is the filtered version of that list? (What is the second-level filter?) It’s just the list version of what we created with that text element:
Let’s drill down. It’s this:
See? That’s just like what we did in the text element. We are just detecting if any Event Range overlaps with the User’s selected range.
(Aside: the “Advanced” filter is one of the only LOOPING operations available in Bubble. We are obviously iterating [looping] over the list of all Events [in this case] to detect if any of them meet the given condition.)
In run mode, when we select a range for our search, we will reduce the list size by any booked events.
… I could write more but I think OVERLAPS WITH is all you’re missing.