How to Show an OptionSet Subset in Dropdown List

antony.hodgson · 2020-05-22T20:35:45.789Z

I’m trying to show a filtered set of options in a dropdown based on a selection elsewhere on the page. More specifically, I have a map showing a number of Businesses, and I’ve set it up so that the user selects one of these businesses. I then want the user to be able to pick an Appointment slot from a dropdown based which slots are still available.

The Business entity (data type) contains a list of existing Appointments, and I have an optionset called Slots which enumerates all possible Slots (basically, all 15-minute intervals that can be booked). Note that the Appointment data type includes a Slot field to describe when the Appointment occurs.

In the dropdown, I want to show all Slots currently available for the selected Business. I’m using the “Choices source” option for the dropdown, and have successfully set it to filter for Slots that are within the Business’s opening hours (eg, “This Slot’s Index > Map.Businesses’s current marker’s Opening’s Index”).

But I’m having trouble figuring out how to filter out Slots that are no longer available. I thought of checking to see if any of the existing Business.Appointments have the same Index as This Slot, and tried coding that as the condition “Map.Businesses’s current marker’s Appointments:filtered:count = 0”, where the filter is set as “Slot = This Slot”, but “This Slot” is not available for selection.

I would greatly appreciate any suggestions about how to handle this situation.

PS: I did earlier try a different approach that worked, but took a long time (~30 seconds) to load. For that, I created Appointments with two data fields - the Business and the User - but stored them independently of the Business with which they were associated. To build the dynamic dropdown list, I did a search for all Appointments with the selected Business and then added the constraint that This Slot should not be in this search result.

As I say, this worked, but took about 30 seconds to load the result. Looking ahead to scale, I realized that it would likely be more efficient to keep the Appointments explicitly joined to each Business, as this would avoid needing to search the much larger database of all Appointments each time in order to first extract those associated with a particular Business.

Nonetheless, I was puzzled that it took 30s to execute the query using my first approach. In my development database, I only had 3 Businesses and 1 Appointment. Admittedly, there are 96 Slots, but I would have thought that it would have been possible to run these 96 comparisons in a small fraction of a second. Again, any ideas why this step seems to have taken so long?

nocodeventure · 2020-05-23T00:00:06.206Z

You need to make the tables relational. Avoid doing a search for data. Rather, target Current Page’s Appointment’s Business > Data.

antony.hodgson · 2020-05-23T06:23:17.329Z

Thanks for replying. I did try an alternative to searching for data. As described in the OP, I saved the appointments under the appropriate Business (Business.Appointments (list)). My problem lies in how to only show Slots in the dropdown that satisfy the condition that they don’t show up in any of the Business.Appointments.Slot (ie, each Business has an Appointment list, and each Appointment has a time Slot associated with it - ie, how do I add a condition that says “This Slot is not the same as the Slot in any of the Appointments associated with the Business associated with the current marker selected on the map?”).

ambroisedlg · 2020-05-23T07:02:12.919Z

@antony.hodgson I built the same kind of app in the past, I admit booking management can get tricky. I also had a situation with 96 different available slots for each day.

antony.hodgson:

realized that it would likely be more efficient to keep the Appointments explicitly joined to each Business, as this would avoid needing to search the much larger database of all Appointments each time in order to first extract those associated with a particular Business.

That’s the right way to go. I’m guessing your user picks a date from a date picker first, then you show all available slots for that day? I used the Search for Appointments as well, it should take approximately 2sec to load at most

antony.hodgson:

My problem lies in how to only show Slots in the dropdown that satisfy the condition that they don’t show up in any of the Business.Appointments.Slot

I’m not sure this is do-able with a built-in dropdown to be honest. The way I did it was through a custom dropdown with the following:

input field
focus group (with a condition to be only be shown when the input field is focused)
RG within the focus group, where you would show all slots for the day

Considering your Appointment’s Slot field is of type date range, you could then have a condition in the RG’s cell, where Search for Appointments:count > 0 (constrained with Business=Business and Current cell’s date range overlaps with Current’s cell’s Appointment’s Slot’s date range) → make this cell not clickable, therefore the user wouldn’t be able to book this slot

Hope that helps

antony.hodgson · 2020-05-23T17:29:03.219Z

@ambroisedlg - thanks very much for these suggestions. Indeed, I have 96 possible Slots in a day (though practically most Businesses are only open a fraction of the time).

Thanks for confirming that it’s reasonable to keep the Appointments as a list in Businesses. Yes - the user picks a date first, and then I want to show available slots.

I’m going to think more about your suggestion about using a focus group and repeating group. I haven’t done much with dates yet in Bubble, so wasn’t aware of the idea of looking for a date range overlap - will investigate this in more detail.

I’m still puzzled about why This Slot doesn’t seem to be available for use in a filter (when it is available for use in other conditions) as that would seem to be the easiest way to do what I’m trying to do - hoping for insight about this.