FMP 12 and FMS 12 on OS X
I have an Appointments table with Date, StartTime, EndTime, InstructorID, ApptType, ApptTitle which is used to store multiple appointment records of varying length for 30 or so instructors.
Each instructor also has 1 appointment record per date specifying their availability, which contains the start time and end time of their work day.
The users who maintain the system would like to display a daily schedule for any given instructor on any given date, but it needs to display the open slots. For example, InstructorA works from 9:00 AM to 5:00 PM on a specific date, but may only have 3 appointments that day:
Appt1: 9:15 AM to 9:45 AM
Appt2: 10:30 AM to 11:00 AM
Appt3: 2:00 PM to 3:00 PM
So there are 4 records for that instructor for that date (counting the Availability record), which I want to display as:
9:00 AM - 9:15 AM - open
9:15 AM to 9:45 AM - Appt1
9:45 AM to 10:30 AM - open
10:30 AM to 11:00 AM - Appt2
11:00 AM to 2:00 PM - open
2:00 PM to 3:00 PM - Appt3
3:00 PM to 5:00 PM - open
It would be nice to display that info as records in a portal or list view so the user can click any entry to either view an existing appointment or schedule a new appointment in the open slots.
So far I have a layout with 2 globals to select the instructor and the date, and I can easily display the 3 appointments, but I can't fathom how to display the open times. Can anyone help?