Stuck on Searching for Holiday Listing Available Dates as a Date Range

Hi Guys,

I have a holiday rentals site that needs to allow a visitor to search for a holiday listing and the availability as a range e.g. is this holiday listing available from x date to y (the dates will need to be optional though). The Listings are displayed on a search page in a repeating group.

I’m stuck on having the date from (arrival) and date to (departure) show the correct holiday listings based on a date range.

image

One of my concerns is for instance a scenorio where a user first sets a date range to available .e.g Oct 1st 2018 to Oct 14th 2018 and then later in time sets the same date range to booked. There would be the same data ranges in the database, so need to make sure that it finds that last/latest entry to ensure it shows the right results.

I am using the custom calendar to allow a user to create a date range and make the date as either available or booked - Custom Built Calendars

Basically when a user clicks a date from then clicks again to set the date to, it sets a custom state and then the user can make the date as either booked or available, workflow and screenshot of calendar below:


(when booked button is clicked, only change is booked = yes)

The data types are setup as the following:

And of course the holiday listing has the associated data type:
image

If I’ve missed any key info please give me a shout. Any guidance or tips would be much appreciated!

Thanks,
Luke.

Hi Luke

This is actually a tricky problem, most property apps I have come across keep each day and it’s status to make it simple, rather than trying to do lots of calculations is this date in this range. It’s made harder by am/pm arrival departure days.

So if it’s 1st Oct - 3rd booked it would change the entry for each of those dates to booked

1st - booked = yes
2nd booked = yes

You would do similar for rates for the days, so each day has its own chargeable rate.

Thanks
Simon

Hi Simon,

Appreciate the reply.

Yes sure, understandable working with dates and combining searches with constraints is tricky :wink:.

Luckily with my site, the search and display of dates is simply for viewing purposes, so the visitor on the site wont be actually booking anything or paying - its simply there to let them know if the holiday listing is available at that time and then they can get in touch with the owner.

I’m still a bit puzzled as to how I can filter the date range to only show available dates when there could be multiple entries with the same date ranges, if that makes sense. And also how to have the date search optional, so all listings will show if there is not date from and date to parameters in the search.

Cheers,

The closest I’ve got is with this condition using a filter after the initial search on the repeating group:

image

image

This works in finding the EXACT date range that is marked on the calendar as available, but makes the date from and to search required as well, otherwise no results show - ideally needs to be optional for users.

The 2 issues with this method and my current setup of the calendar is this scenario example:

  • User accesses their calendar for their holiday listing and marks 1st October to 31 October 2018 as available. The problem with this is that at the moment a user would have to search for a listing from the exact start and end date in order for the holiday listing to show, rather than within this range e.g. 5th October to 10th October - would should then display the issue

  • User again marks the range of the calendar 1st October 2018 to 31st October 2018 available. Later the user then comes back to the calendar and marks the 1st October to 10th October as booked. This interferes with a search such as checking if the availability is free on 11th October to 15th October for instance, as it cant calculate the differences between the booked dates and the available dates. This applies to any marked date range that covers the initial available period.

I would be most grateful for any help on how to get round these 2 issues.

Hi @luke2:

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 :wink: ), 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.

3 Likes

IBTW: there are some potentially serious performance/scalability problems with this that I’ve not really experimented with yet.

Most (all? It’s unclear) :filter operations are performed client side. So theoretically, what is going on here is that required data about ALL listings is being downloaded to the browser, just so we can eliminate listings that are not available.

The “Advanced” search criteria is only available in :filter. So we are atuck with this EXCEPT in API workflows where there is also a :filter operator that also has “Advanced” as a method.

THAT filtering would of course happen entirely on the server.

The problem with THAT is that “API Workflows” called from Bubble do not behave like API workflows called as external endpoints.

Set up an API Workflow that does nothing but “Return Data from API” action. Name it something like “ReturnFive”. Make it do nothing but return the value 5.

If you call this from some external system, Bubble will return a “5” to you.

When you call it inside of Bubble (e.g., Schedule API workflow… ReturnFive… right now.), the workflow runs, but the return value is not accessible in Bubble.

Where the fudge does the “5” go? It doesn’t get returned as the results of that API Workflow step. This is so supremely dumb that, well, I can’t even.

Why would we want to do this? Well, what we really wanna do is move this search and filter operation for listing availability up into the server level right?

At the moment, it may not be possible to do this. The workaround would be to set up THE API CONNECTOR to call our Bubble API Workflow from within Bubble.

Does Bubble allow this? I don’t know and I’m afraid to try as I’ll bet dollars to donuts that Bubble will balk and when it does I will want to throw my very expensive dev laptop out the window.

tl;dr: This method of identifying available listings seems to be performant enough for dozens of listings. I do not know how it scales to hundreds of dozens or thousands of dozens of listings. Limitations of Bubble’s API Workflow capabilities are noted. ;/

1 Like

WOW thanks - I’m most grateful for your reply and detailed explanation, you’ve really gone the extra mile with your insights so this is much appreciated @keith

There is a lot here, so I will digest, re-read and have a crack at this tonight (and report back)!

Cheers

1 Like

It’s actually a very simple idea, but if I just plopped the expression down without explaining the logic behind it, the expression would make no sense.

BTW, the concept described in Step 2 is how my booking widget and calendars show which dates are booked vs available. In that case, we’re just looking at a single Listing’s Events.

A calendar date is booked if: Any of the Listing’s “Event Ranges” contains point that calendar date.

In this way, one does not have to maintain a list of individual booked dates. All one needs is the start and end dates for reservations.

(These “range-wise” comparisons ARE slightly more compute intensive than comparing if a list of booked dates contains a certain calendar date.

But again, good luck constructing a list of booked dates in Bubble without very slow server side recursion or using an external API.)

Hey @keith

Just an update - followed along with the mini guide which helped paint a much clearer view with using a fresh Repeating Group using the ‘Overlaps with’ condition and all working with a bit of tweaking to my own setup. Thanks again for the pointers and explanation as to how the logic works with it.

Gained two key pointers from your reply - 1 is the use & logic of the ‘Overlays with’, very handy, something I’ve seen before in the Bubble references but never quite gauged its potential or usage without an example attached to it.
The 2nd being the condition > filter > filter approach, takes some thinking as to whats happening in the background but holds a lot of power, especially with the ‘Advanced’ modifier.

One issue I’m faced with however is trying to add on a few days window each way (both ‘Date from’ and ‘Date to’) to extend the searches a little. The reason for this is due to how the calendars are setup for owners to assign the booked and available dates plus it also allows a little more flexibility for owners listings to be seen on searches.

As from the screenshot below, it seems I can only set this on the ‘Date from’ value and don’t have the option on the ‘Date to’ - any suggestions or workarounds?
image

Cheers

Yes, I allow the same thing (allow “prep time” before and after each booking). This complicates the search and filtering somewhat, as you’ve discovered.

For me, it comes up in the calendar/booking tool logic (I do not yet have a public-facing search page like you are building, as I’m not yet sure that I’ll ever deploy my app as an Airbnb-like OTA/booking engine).

What you’re specifically running into is the limits of expression construction / order-of-operations in Bubble (which are infuriatingly limited… “please give us parentheses!” right?)

In the case of an individual calendar, it can be solved (basically, there are multiple conditions on the individual calendar dates – one essentially moves the start date back in time, the other moves the end date forward in time).

However, in your case, it’s problematic. All you want to do is extend the user’s search range by the listing’s prep time on each end (as you’re doing. (Note that your +/1 could be variables.

Honestly, I am NOT sure of the most efficient way to do this. I think it would be like this:

  1. Construct standard range
  2. Construct extended range(s)
  3. Do the filtering expression on widest range first then filter by the same filter again but with narrower and narrower ranges.

It just sucks that one can’t dynamically modify both ends of a range algorithmically at once…

But I have an idea…

Here’s the idea:

First, your Listings need to have a “prep time” like 0, 1, 2 or whatever.

Construct the required date ranges and put them in an array (list) of date range type (let’s call it UserRanges) where list items are:

#1: the original UserRange
#2: UserRange expanded by one day
#3: UserRange expanded by 2 days
Etc as needed.

(That must be done in multiple steps in a workflow as we’ve discovered.)

Then the filtering expression is the same as you have now EXCEPT wherever you reference the original UserRange (userStart<- range ->userEnd) you replace it with:

UserRanges:item #Listing’s PrepTime-1

So a listing with no prep time is evaluated against the first range. A listing with 1 day prep time is evaluated against second range, etc.

Should work unless you hit another dumb expression constructor restriction!

I need to try this…

1 Like

BTW, if we DO hit a problem with “Listing’s Prep Time+1”, what we do is have prep time that go 1, 2, 3 (instead if 0, 1, 2).

Because that might be unintuitive and trip you up later, one might also store Prep Time as accurate but then also store “Prep Index” as Prep Time minus 1.

Hi @keith

Ah great suggestion there with storing more than just the original date range - this sounds like the best method/workaround and will get the job done :wink:

I am going to give this a shot tomorrow and test out, but from a logic point of view it makes sense. I’m in agreement, parentheses on expressions is much needed, luckily there are a few workarounds and hacks that can be pulled off, but come on Bubble.

Cheers,

This topic was automatically closed after 70 days. New replies are no longer allowed.