I’m building an app that allows landscapers to manage their schedules/availability.
The landscapers need the ability to set their default available time slots by day of the week, ie.
- Mondays: 8 am (2 open slots), 10 am (3 open slots), etc.
I would also like the ability for the landscapers to do an override by specific day (like 4/25/2024). On Thursday, 4/25, they’d normally have 2 open slots at 8 am, but on that particular day, they only have 1 slot.
My question is:
- How would I structure the database to allow for specific day overrides?
- For example, will I need a “Availability - Day of Week” and “Availability - Date” tables? If so, would I use a workflow to populate “Availability - Date”, using the defaults specified in the Day of the Week table?
- Would I need to have every single date and time slot combination captured in the database? I want to be wary of performance.
There may be an easier approach that I’m overlooking. Appreciate the help!
Here’s what I’m going for on the frontend:
Day of Week:
Specific Date Override:
If you already have logic built out in the app regarding how you handle the weekly schedules, then it is hard to say what will be best for your app without having a strong understanding of it’s current state.
If you are starting out and open to how to structure all of this, you can checkout my example app and editor here, it shows how you can structure a scheduling app regarding the database and logic used. It uses both weekly patterns and specific date overrides. It specifically uses my Plugin as well to help with everything but a lot of the logic can be used without the plugin and in your own setup if needed.
App:
Editor: topshelf-elements | Bubble Editor
1 Like
Thank you! This has given me a lot to consider, particularly when structuring the DB.
Looking at the plugin…
For each booking window, I need to capture (# of spots available). For example, the [8 am-10 am] appointment slot is available twice, meaning I can accommodate two different customers.
Could your plugin handle this requirement?
There can be different ways to do this. One is if they are separate schedules (like a Barbers in a barbershop), then you would use multiple GetAvailableTime elements and get the available time slots independently.
Or if a Schedule will always have the same number of Max Spots Available (so always x number of spots for all time slots under a specific schedule) then something like the following can work:
- On the Schedule Data Type: add a ‘Max Capacity’ field (type = number)
- On the Schedule Data Type: add a ‘Booked Ranges’ field (type = list of date ranges)
- Add an Action ‘Make Changes to booked Schedule’ when a Booking is made, after the new booking’s Create a Booking action:
- Only run the action when this condition is true: Do a search for Bookings, where the Start Time (time slot) is equal to the New Booking’s Start Time (time slot) AND status is ‘Booked’ (or whatever you use to know a booked vs canceled appointment) :count is equal to or greater than New Booking’s Schedule’s Max Capacity
- When this is true you will change add the Booked Range of the Time Slot to the Booked Ranges field on the Schedule
- Now on the Schedule/Calendar page and in the TopCal ‘Get Available time slots’ element, instead of searching for bookings to input in the Booked Ranges field, just reference Parent’s Schedule’s Booked Ranges
You will then also need a similar lookup logic for when a Booking gets canceled if your app allows it to adjust the Booked Range field and also if the User adjusts the Max Capacity number
The other option is to create a Data Item for every Time Slot when the schedule gets set, and uses Scheduled Workflows to create future time slots, then it’s simple to attach a ‘Capacity’ (number) field to the specific time slot. This can be data intensive though and changes how you lookup available Time Slots but is pretty much necessary if every Time Slot can have a different amount of people book it
1 Like
I appreciate your in-depth response. It’s helped me to think more strategically about the approach. One other point worth clarifying (which I think should simplify everything) is that the booking window (ie. 10-am to 12 pm) cannot be broken up. That is only one time slot. (Customers can’t book 10:30 am or 11:00 am for example.) With this in mind, I don’t think I would need GetAvailableTime elements to pull back the different individuals times, right? I could just query the Schedule data type directly.
Combined with my need to have separate capacities for each time slot, it seems that creating a data item for every time slot should be cleaner and less data intensive. Most booking timeslots would be 2 hours, so that’s 4 or 5 per day. And then I could run a nightly backend workflow that cleans out the table, removing any past time slots.
