5 Replies Latest reply on Aug 1, 2013 4:58 AM by Mike_Mitchell

    display daily schedule with open slots


      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?





      Steve Moore

      Cumberland, Maine

        • 1. Re: display daily schedule with open slots

          Steve -


          Essentially, you'll need to have a record for each slot. A separate table can be used for this, or you can use a Virtual List technique. Each of those records can then be associated back to a related appointment record.


          If you want to see this done in a commercial application, I suggest you take a look at the SeedCode Calendar app (http://www.seedcode.com/filemaker-calendar/).



          1 of 1 people found this helpful
          • 2. Re: display daily schedule with open slots

            There are so many ways to do this that it is not funny.

            The advantage of the Seedcode stuff is that it will save you time if you need something similar. It is pretty comprehensive and using their zinc plugin you can synch your database calendar with iCal and google calendar for sharing. Updating one calendar can update the others.


            I've done this with relationships... one for each 15 min timeslot... to the appointment data which Mikes suggests.

            If one appointment goes for 45 minutes, then the timeslot (combined with date) will fill in 3 x 15 minute slots... determined by the time slots it matches.

            You can use separate fields or repeating fields for the LHS of the match and on the RHS you want the start time to be the same or before the start time on the LHS and the same or after the end time (ie the next start time) to match.

            Simply having no matching data makes the first slot available.


            If a slot is always blocked out because she takes off at 2pm on Fridays... then an appointment could be added which has many dates in a list instead of just one so you don't end up with too many records. (eg. I have a system which figures out every 2nd and 4th Thursdays at one hospital and every 1st, 3rd and 5th Thursday at another... using a similar method... but with a table with the settings)




            - Lyndsay

            1 of 1 people found this helpful
            • 3. Re: display daily schedule with open slots

              Thanks for the link to SeedCode Calendar - I had forgotten how impressive their stuff is, and the FM 12 version even includes iPad layouts. Seems like it'll be worth the $300 investment for such a great head start.

              Do you know if they have a Developer version?

              • 4. Re: display daily schedule with open slots

                Thanks Lyndsay. It wasn't until I read your message for a second time that I caught on to the idea of the multi-date link for "repeating" events. That's a clever idea for setting the Instructor availability (Mondays 9 - 5; Tuesdays 11 - 7; ...).

                I'll be taking a closer look at SeedCode Calendar - the possibilities of linking to Instructors' calendars is very compelling.

                • 5. Re: display daily schedule with open slots

                  I believe so. But check with them to be sure.