Build a Booking App with Dynamic Time Selection
I have been working on a booking app and need dynamic time slotting, something similar to Calendly, where the availability of starting times is dynamic according to user preferences such as minimum time between appointments etc. and changes based on duration of appointments. I have finally uncovered a way to accomplish this and thought I would pass this along to those needing a flexible, dynamic solution. Sorry this is long, but I want to fully document this so I never forget how.
Tl;dr
Have a single Time Table that includes a whole number field expressing each time as a number of minutes value (1-1,425) entered directly, not by formula for this particular field.
Create a schedule table and appointment table with DURATION and setup time parameters expressed (this time by formula) as number of minutes to calculate whole numbers representing Start time, duration, next available and last possible times. You can then pull the numbers through and use mathematical filtering. The trick here is to make sure the mathematical value representing the times available is a number value. If that number is by formula, you will be locked into filtering on CHARACTERS instead of numerical values!
Here’s the magic filter formula:
(PAGE FILTER)ALL OF
IF {TIMEASNUMBER} IS ≥ [
CurrentRecord] {NEXTAVAILABLE_TIMEASNUMBER}
AND {TIMEASNUMBER} IS ≤ [CurrentRecord] {LASTAVAILABLE_TIMEASNUMBER}
Create a single time table with possible Start Times, and include a whole number column expressing the time as a number of minutes (not a formula, just enter the military minutes as a whole number) and link it to a schedule table and an appointments table with a duration of appointment. Figure your Next Available (Start Time + Duration + Minimum Between Apt Time = Next Available) and set a time for Last Possible Start time. Now convert these times by formula to number of minutes as a number and pull them through to your time table. You can now list filter by comparing the numeric value of the Start time, to show only those that are within the numeric range of the next available number and the last possible number. When you add the user ID filter, every user will see the available times for their individual provider.
Here’s a detailed instruction. FYI it’s all AirTable except the very last.
Inventory Approach Didn’t Work
I am using AirTable and originally tried creating an inventory of possible appointment start times for each provider, but quickly realized this would mean creating thousands of unique records with each record representing a time slot for a single provider. This also did not account for accommodating appointment times of varying duration.
Linked Record Approach Didn’t Work
I needed a dynamic schedule that creates appointment records based on the starting times selected, with a single, linked table of starting times for a 24 hour period, but as you probably know, if you allow users to select from a linked record field, you cannot limit which linked records they can choose from. Here’s how I got around that limitation.
What Worked
1.) Created a single Time Table representing 24 hours, in 15 minute increments (96 time slots)
2.) Created a linked, Schedule Table. Each record represents the schedule for one day, for one service provider. Every schedule record is linked to all 96 time slots. Each scheduling record contains the Schedule Date, the and three, time entries, First Available Starting Time (STARTA), Next Available Time (NEXTA) and Last Possible Starting Time (LASTA).
3.) Created an Appointments Table,linked to the Schedule Table. I pulled the Duration in the Appointment Table through to the Schedule Table (duration as minutes) added the number of booked minutes (0 until something is booked) and the number or required minutes between appointments to get NEXTA.
4.) Converted times to number of minutes. In the Time Table (this is key) created a whole number, numeric field (TIMEASNUMBER) representing each time value as number of minutes. (12:00 AM = 0, 12:15 AM = 15, 6:30 PM = 1,100 …) Please note, formulaic conversion will not work for this particular field. Just enter the numbers 0 to 1,425. Conversely, in the Schedule Table; you WILL convert the value by formula.
5.) In the Schedule Table convert the two time values to the number of minutes. Here’s the formula I used to convert, yours may differ: (HOUR(DATETIME_PARSE({Time24}, ‘h:mm’)) * 60) + MINUTE(DATETIME_PARSE({Time24}, ‘h:mm’))
6.) Pull the minute values for the times through to the Times Table by Lookup. For my use case, I also pulled through the record IDs for the schedule record, an organization ID and the Provider ID (person to book with).
7.) Add a Detail Page in Softr: For my use case, I have tied the Schedule Table to a Calendar Block showing the provider and their start and end times. Clicking on the calendar item takes you to a List Details Page, with details of the schedule record, the provider bla bla.
8.) On the same page under the detail record, I added a Table Block connected to the Times Table. The critical point here is filtering this List Block. I used the normal user based filtering detail, and here’s the KEY:
(PAGE FILTER)ALL OF
IF {TIMEASNUMBER} IS ≥ [CurrentRecord] {NEXTA}
AND {TIMEASNUMBER} IS ≤ [CurrentRecord] {LASTA}
9.) Now you can put an item button to create the appointment based on the selected time!
