Solution to time slot/ booking calendar to select date & time input & availability

App Development
1

Hi all,

I am figuring out how to design the data table structure for my timeslot. The issue is that I prefer to do it TB and not by some external solution (like Calendly), but the thing is that untill now I haven’t found a way to set it up in such a way that it’s doing what I want.

So, the aim is to let users/ visitors make a booking (time slot)… They must select a date(1) and a timeslot (2). A time slot is a 45 min period, each day between Monday and Friday. Time Slots are predefined, so they are from 09:00-09:45 - 09:45-10.30 etc until 15:45-16.30
I want all visitors only be able to book in advance for a maximum of 3 weeks… Not longer.

Here are my choices, I think

Option )1, regarding the date, I can use the Calendar component and add some local sauce on it with the @Chem language options, to show it local language… That’s great.
However, when using this setup, it’s not possible to ‘load’ the timeslots… Because in this setup each user can select it’s own time… And that’s not good, since it can create double bookings and user don’t know how long the time is they should book etc…

Option 2) So, the next option is, watch the video from @tim.young, where he explains the booking calendar…
This is a great video, but there are 2 things.
→ The booking time selection shows a date and time in 1 field. This is good, but imagine that a user can select all the possible timeslots for each day for the next 3 weeks… The dropdown will be (too) long and presenting it like date (YYYY-MM-DD- HH-MM-SS) in a table is kind of overwhelming… Try to select a date on your tablet… That’s why I prefer the calendar component from Option 1.

Option 3)
So, the most likely option is create a table with Dates and Times Slots and work with dependent drop downs… The TimeSlots will then be Select (Option) Fields instead of Times. But that also means that filtering on Times is not possible.
Also, when someone actually makes a booking in this system, I cannot update it, since the TimeSlot which is selected cannot be used as a form value. E.g. when a booking is made, I want to update the Table and update the row with and (example) to ’ Unavailable’ …
Also, building this table will be very very time consuming.

Then there is option 4)
Use an external booking system like Calendly… It’s not the best option, since Calendly has to communicate again with TB regarding the availability and processing updates to availability.

So, the preference for me is Option 1, since the Calendar display (for the dates) is great, also with the local language sauce. But the time slot thing isn’t making this work.

I am hoping that it’s clear what I am trying to achieve and I am hoping I am missing some kind of step in my think process to get this thing to work :slight_smile:

2

Hi, maybe you can work with 3 tables, table A would be the daily slots template, containing all the possible slots as a string; table B would be the available slots, combining a date field and the link field to table A (slot), and a select field with options selected or free; table C would be the appointment table, connected to table B. So, I imagine this working like every day manually or using a script you can create all the slot time records for the same day (or several days in advance if using another table for days template) in table B copying the slot template in A. The created values for the select field would be free. A user uses a form in table C to add a new appointment, in the B linked field you can filter to the same day selected in table C and the free records in table B. Finally, when saving the record in the form, use a record rule to change the selected free slot to the value of selected, so it will be blocked.

3

@Aprada

Ok, so I tried to figure it out.
Table A " timeslots → 8 text fields with the time slots
Table B: Date + connection field (time slots) from Table A and a select Field with ’ Available and Unavailable"

Table C: The bookings calendar., which contains a connection field for Date (Table B) + Time slot (Table B).
For some reason I can’t make it work.

The aim is to let the user pick a date and time slot which is available. But the date input function is not available (I mean the calendar component). Also, the filter component (with the dependent drop downs) cannot work together with the calendar input component.
So in this setup I still have the (long) drop downs when letting the user select a date. What am I missing?

4

Hi, the point here is that I think the calendar component is only using date or time field types, and the connected fields from A & B are strings (ID). You should create also the date & time fields and make the table rules, when edit, copy the time slot & date value to the time & date fields. Maybe it is not so direct and you need some custom javascript or date pipes, in the end you need to transform a string into a date & time format in 1 field, so the calendar can take that to show it.
Regarding the dropdown if you use the record rule to hide the taken slots in B, you will see less options with time.

5

@Aprada,

Yes, I agree that to achieve this there needs to be a combination of pipes and forms, since it involves some interaction with each other.

Basically, this setup is doing the timeslots in field and the date field. It’s possible to create dependent drop downs with each other. Though this setup works to a certain level, the disadvantage of this is that the date input component (where the calendar pops up) cannot be used.

So I though about another setup, which involved generating a list with a pipe.

  1. Create a table (A) with date field, timeslot field and availability (field).
  2. Let the visitor select a date with the date input component (in that way I can arrange that only the next available 3 weeks can be selected.
  3. Run a pipe to pull the available timeslots from table A, with the input date selected by the visitor.
  4. Show the visitor a table item with the results (timeslots which are available)
  5. Let the visitor select a timeslot.
  6. Join a visitor with the date & timeslot.
  7. Mark the selected timeslot from table A as unavailable.

While this is a way to do, I haven’t found a solution for the next items:

  • How can I display list from step 4? It’s not good to create a list of records, since users will probably select multiple dates so see which timeslots are available. There needs to be some ‘refresh’ or delete function on the back-end, so that only the timeslots from the latest query are show. It works the same way as running queries on Booking.com for finding a holiday between particular dates.
  • How do I ensure that when the user has selected a particular timeslot and day, the timeslot becomes unavailable and it’s connected to the user?

So, I am still struggling with how to tie the components dates and times to something that can work and make it a little but user friendly :slight_smile:

6

Mmmm you do not need special things for points 3 & 4. Because you are using a link field to table A, you can setup in the form of adding a new record in table B, using the filter record and tab, that only shows time slot linked to the date AND only those records with availability ok. Also setting a record rule after saving the from, set value of availability to not available, will hide this time slot/date combination for the next client