I’m currently refining my flight scheduler using the new features glide has released over the last few months and wanted to create a place for my progress to live for everyone else working on projects like it. I will be adding images and video to this thread as I progress.
I have found a way to incorporate @Robert_Petitto’s methods with a few of my own to avoid double booking of pilots and aircraft.
Pilots - Profiles and booking choices
Flights - Booked Flights
Start Times - 1 column (1488 rows) of 30 min incremented “now()”'s (31 days out) concat with each pilot and aircraft.
Current Pilots: 38
Current Aircraft: 6
From Pilot’s sheet:
Creator of booking is “Pilot”
Choose ”Activity” (8) (purely informational)
Choose ”Copilot” (37 not including self)
Choose “Aircraft” (6)
Choose a start time, in 30 minute increments, up to 31 days out starting with NOW() (formatted to find the next 30 min occurrence)
Choose a duration, in 1 hour increments, from 2 to 24 hours.
Using @Robert_Petitto’s duration columns to compare potential length of flight against existing flight information, we are able to prevent booking if someone chooses a start time before a current flight and a duration that would book through the existing flight. I still don’t have a method to remove the start times that would not work for the duration they have chosen but that can always be added later.
Ex: If it’s currently 10:45am and pilot1 booked aircraft1 with pilot2 at 12 noon for 2 hours, I don’t want pilot3, who also wants aircraft1 and pilot2, to have an option of choosing 11am for pilot3’s start time if the min duration is 2 hours and puts pilot3’s flight ending in the middle of the flight that pilot1 already booked. Pilot3 should only see start times starting with 2pm. Disclaimer: pilots don’t see booked times in their start time choice component but I’m sure some of them will still choose a “bad” start time and see my warning message. I would like to use their duration to help populate possible start times instead of ALL free start times.
The goal isn’t to alert the pilot that his/her choices are not able to be booked by comparing a relation but to instead populate only the available "start time"s for their choices. To do this I display a filtered (pilot, copilot, and aircraft are empty) choice component (start time) assigned to their choice of copilot and aircraft.
"Start Time" Sheet:
A:A - “=ArrayFormula(ADD(FLOOR(NOW()-1/48, 1/48),ROW(INDIRECT(“A2:A”&1488))/48))”
B:G - “Aircraft”&" “&A:A
H:AS - “Pilot”&” "&A:A
Glide Data Editor:
1 relation column on the “start time” sheet per pilot and aircraft.
Each pilot could make up to 222 (37px6a) unique selections in which the “Start Time” choice component would need to filter out times that are already present on the “Flights” sheet for their current choices.
So instead of making 1 relation from “Flights” (already booked) to “Pilots” (booking choices) and rejecting the ability to book with a warning message, I need to build 1 relation column from each aircraft and student on the “Start Times” sheet to the “Flights” sheet then build 222 “start time” choice components that only appear and filter the pilot and aircraft that was selected. Wish me luck.
More to come soon! (pictures/video)