Hi All!
I have been dabbling in no-code for the last couple of years (largely Bubble and Airtable) and think I have landed on Glide. @JackVaughan’s content, @Robert_Petitto’s youtube videos and all the help found here really helped with locking in my decision to have a serious crack at Glide. This is my first post in what seems to be a great community forum I can’t believe the username “james” was available!
I am using Glide Pages (and Glide Tables as data source) to build an internal tool for my company, which at its core is a booking system. Basically, we have Project Managers that will be booking in our field staff (there are approx 12 field staff total, but growing) to complete tasks for projects. After this, I would like to add a Glide App for the field staff to use that links to the relevant data. For now, I am looking for some help with the booking system logic.
The current data structure is:
- projects at the top
- each project contains jobs (we have a fixed set of job types that we work on)
- each job contains tasks (tasks are usually created automatically with the job because jobs require only certain tasks)
- each task contains bookings (the field staff may need to attend multiple times and it could be a different person attending each time)
I have set up the relations between these and am reasonably happy with the way bookings are currently displayed (other than the limit to show 20 groups at a time per collection). See screenshot, I’m open to suggestions. Shout out to @Robert_Petitto for his video Glide: Dynamically Filter your Data by TIMEFRAMES (Part 1) - YouTube
There are some considerations:
-
The roster of each field staff varies, so field managers should be able to input rosters with a start and end date that are applied to the booking availability. We only operate Mon-Fri, some staff work only a few days a week, some are full time. Each have a different combination of field/office days. I’m guessing I’ll need to leverage the Math column’s “weekday” ability. The system should also cater for time off (i.e. input future annual leave).
-
Each field staff has different capabilities in terms of the jobs they are able to work on - they should only be available to book for the job types they are capable of working on. Would this be input as a multi choice component that stores on the users table (and converts to an array)…similar to a user having multiple roles?
-
When creating a new booking for a task, project managers should be able to view the availability of each field staff (and ideally see at a glance the next available timeslot on each field staff’s card). Project Managers make a tentative booking, and then Field Managers check and approve it. Maybe something similar to the template screenshots in comment below (but for Glide Pages).
-
Our bookings are day/date based only (no need to consider time). Field staff can be booked in for multiple jobs per day, however, I am thinking that in the booking system the Project Managers can only see dates with 0 bookings - for now, any extra bookings will probably be added manually by Field Managers closer to the day.
One of my main conundrums is coming up with a pool of dates that are available to book. I want Project Managers to be able to book for at least the next 12 months. I’m not sure how to create the initial list of dates. Is it possible to avoid having all dates stored as rows in a table and then only displaying rows that aren’t yet booked - it feels laborious. Is my best option to have something like zapier/integromat create the next month’s rows once a month to ensure I always have 12 months of dates available? Is there a better way to look at the problem?
Apologies as this probably isn’t a quick and easy answer. I’m hoping to get some advice on the structure and logic required to achieve this in Glide and how the different considerations can ultimately link back to booking availability.
Thanks, James (Australia)