Dynamically Update List of Options (For "Choice")

I’ve got a big one fellas… I am building an app to create requests for a flight and manage the approved flight requests. I am trying to automate the process by removing certain variables if they are already “booked” so that my client will not have to constantly cross reference variables to make sure everything is a GO!

Say I want to book “plane 1 of 5” on “9/28/19 1:00:00 PM” and I select “Dave” as my student to ride along as I instruct… I can not think of an easy OR hard way to remove any of these variables, Myself, Dave, or Plane 1 of 5, from the lists they are a part of so that other users can not book them on “9/28/19 1:00:00 PM”.

I see, so you want a booking system, where people book times with instructors, and these slots fill up?

Yes! My current problem is that there are 3 planes, 5 instructors, 20 students, and they would like to book at any time. Making an empty event, that can be “claimed”, in the calendar for each plane per hour of every day is out of the question.

Flights also vary in duration so I would need to block those variables depending on that too.

I’ve been thinking about this for a few days, by creating a hierarchy of which items to choose first, second, third and so on. I created a sheets for each category (Instructors, Students, Planes, Dates, Timeslots), then I created dynamic sheets like InstructorDates which link to InstructorDatesStudents to InstructorDatesStudentsPlanes to InstructorDatesStudentsPlanesTimeslots. This allows for inline lists within inline lists within inline lists. The InstructorDatesStudentsPlanesTimeslots sheet would essentially contain every possible combination which I thought could later be filtered when a combination was used. When you drill down, then the final step would be to submit a form that contains all the values for the flight lesson. The problem I’m having is that there is an infinite number of scenarios and I’m not sure how this would all work.

This is extremely crude and not complete, but maybe it will give you some ideas. You can hit the Copy This App button to get a copy and play with it.