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?