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
          comment

          if you define a self-join of Assignments as:

           

          Assignments::Date = Assignments 2::Date

          AND

          Assignments::StartTime < Assignments 2::EndTime

          AND

          Assignments::EndTime > Assignments 2::StartTime

          AND

          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
              woozie

              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
                comment

                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
                  woozie

                  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,

                   

                  Louise

                   

                   

                   

                   

                   

                   

                   

                  De : Michael Horak noreply@filemaker.com

                  Envoyé : 27 mars 2012 10:54

                  À : woozie

                  Objet : Re: Scheduling Solution

                   

                   

                   

                   

                   

                  <https://fmdev.filemaker.com/index.jspa>

                   

                   

                  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
                    comment

                    Louise,

                     

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

                     

                    SessionID

                    Year

                    Month

                    DayOfWeek

                    Timeslot (AM/PM/Soir)

                    TrainerID

                     

                    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
                      woozie

                      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,

                       

                      Louise

                       

                       

                       

                      De : Michael Horak noreply@filemaker.com

                      Envoyé : 27 mars 2012 12:35

                      À : woozie

                      Objet : Re: Scheduling Solution

                       

                       

                       

                       

                       

                      <https://fmdev.filemaker.com/index.jspa>

                       

                       

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