7 Replies Latest reply on Mar 30, 2012 7:16 AM by woozie

    Scheduling Solution

      I am in the process of developing a FMP solution for my University's new student orientation program. It primarily stores team member information and allows for assigning them to various assignments with varying start and finish times as well as dates throughout the weekend. At this point when the user wishes to assign team members to a particular assignment a list appears with arrows to click. When the user clicks on the arrow that team member is scheduled to the assignment and disappears from the list of possible team members to prevent double scheduling with the same assignment.


      Unfortunatly there are many different assignments, many which conflict time wise with each other. What I would like to do is restrict the list of available team members to those who do not have any assignments within the time range of the current assignment on the screen.


      Team members (hereafter people) are scheduled to assignments using a join table (records that store Assignment ID and Person ID). All the time data is stored solely in the Assignment record although I'm wondering if it is possible to have a calculation that evaluates time ranges of unavailability and be able to filter out those who are not available in the portal. Originally I thought of making specific time ranges but things got too complicated for that and the solution we have not is the best option. I do have a workaround (opening another window to see a list of that team member's assignments) but it isn't ideal and leaves too much room for error.


      If anyone has any ideas they would be greatly appreciated! Thanks!!

        • 1. Re: Scheduling Solution

          if you define a self-join of Assignments as:


          Assignments::Date = Assignments 2::Date


          Assignments::StartTime < Assignments 2::EndTime


          Assignments::EndTime > Assignments 2::StartTime


          Assignments::AssignmentID ≠ Assignments 2::AssignmentID


          and attach a second TO of the join table to Assignments 2, you can then get a list of people who already have conflicting engagements by =


          List ( JoinTable 2::PersonID )


          and use this to exclude them from the portal's relationship.

          • 2. Re: Scheduling Solution

            Thank you so much! It works like a charm!


            God Bless!

            • 3. Re: Scheduling Solution

              Hi Michael, I believe I have a similar problem to manage, but am not sure I understand your proposal to Mrcrister.

              A colleague has given me a fairly straightward FMpro11 calendar model I can use. I'm envisioning using 2 calendars.

              On one, I will keep track of a list of trainers' availability for the month (broken down by AM, PM, Evening - blocks of time, on certain dates). Then on another calendar, I'll have the view of all the training sessions that are booked and with which trainers, organizations, etc.

              When a trainer is assigned to a session, I would like their availability calendar to reflect that they are booked on certain dates and for the specified blocks of time.

              Can't get my head around the relationships I would need to build. Any advice would be greatly appreciated. Thanks!

              • 4. Re: Scheduling Solution

                I am afraid I don't quite follow: where does the "list of trainers' availability" come from? It sounds like you have two separate constrains to check: availability (positive) and prior bookings (negative)?

                • 5. Re: Scheduling Solution

                  Hi Michael,


                  At the present moment, what I have is this:


                  A SESSIONS table which has a date field, and a beginning and end time for the event.


                  Then I have a Trainers table and a table called AVAILABILITY  linking the SESSIONS & TRAINERS tables.


                  Availability, for the moment, only specifies year, month, days (not specific dates), and AM PM EVENING (see my example below, in French).


                  If an event date is entered into SESSIONS table, it matches the year, month, day and time of day, to all the trainers who have that  availability (in the below diagram). I’m hoping to put this into a calendar format so that trainers can specify actual dates. But this is it for now.




                  In the SESSIONS table, I have a field with a drop down list of all trainers, the user simply selects the one who they have ascertained is available for that session.


                  So I would like to have the trainer’s availability show that they are now booked for a certain date and time.




                  In sum, what I ultimately would like is this:


                  One calendar with all trainers availability (this will of course exclude their bookings)


                  One related calendar with all sessions booked showing with which trainers




                  Does that make more sense?


                  Thanks so much,










                  De : Michael Horak noreply@filemaker.com

                  Envoyé : 27 mars 2012 10:54

                  À : woozie

                  Objet : Re: Scheduling Solution









                  created by Michael Horak <https://fmdev.filemaker.com/people/comment>  in General - View the full discussion <https://fmdev.filemaker.com/message/75160#75160

                  • 6. Re: Scheduling Solution



                    I am afraid I still don't get i. Looking at your picture, I would expect the Sessions table to have these fields:






                    Timeslot (AM/PM/Soir)



                    so  a session record would say that session X is held during the month of March, 2012 on Monday evenings, by trainer Y.


                    Assuming each session is handled by one trainer only, I don't see why you need another table to link between Session and Trainers.

                    • 7. Re: Scheduling Solution

                      Hi Michael,


                      Forgive me, I only now noticed your email in my Junk Mail folder. I figured out what I need for the moment.


                      A colleague sent me a Calendar model he developed that I was able to adapt. Eventually, I’d like to adapt the calendar to manage more complex things like – availability and bookings in the same model. But that will take much more work.


                      I appreciate you taking the time to write back to me and share your knowledge.


                      Thanks and be well,






                      De : Michael Horak noreply@filemaker.com

                      Envoyé : 27 mars 2012 12:35

                      À : woozie

                      Objet : Re: Scheduling Solution









                      created by Michael Horak <https://fmdev.filemaker.com/people/comment>  in General - View the full discussion <https://fmdev.filemaker.com/message/75181#75181