12 Replies Latest reply on May 17, 2017 1:54 PM by DavidJondreau

    Detecting scheduling conflicts

    PaulH.Krombholz

      Title

      Detecting scheduling conflicts

      Post

      I have the task of scheduling room usage for a small college using filemaker pro 7.  I input course, section, credits, start time, end time, days, room, instructor.  Typical records might look like this:

       

      Dept  No. sect.  name                             Cr.   start         end          days    room    instructor

      Bil.   111  01     Intro Bio lab                    0     9:30 AM    12:20 PM     Tu       K-121   Krombholz

      Bio   111  01    Intro to Bio Lecture           4    9:00 AM     9:50 Am     mwf     K-127    Garner

       

      I would like to know if Filemaker Pro 7 can detect room conflicts (Two people trying to use the same room at the same time).  There are a large number of records, and the only way I know to detect room conflicts is to sort by start time >room >days and then look for conflicts.  This is time consuming, and I sometimes miss conflicts where the start times are different, but there is an overlap in the time periods from start to end. 

       

      Is there a way to use the application to find scheduling conflicts and highlight them?  It would also be nice to be able to catch other kinds of conflicts, such as the same person having to be in two places at the same time.  It would be useful if the application could indicate conflicts at the time of data entry.   If Pro 7 can't do these things, can a later version? 

       

      I am a biologist, not a computer geek, and I have to do this in addition to teaching 15 contact hours a week. 

       

      Thanks, 

      Paul Krombholz

        • 2. Re: Detecting scheduling conflicts
          PaulH.Krombholz

          Thanks for your answer  (sigh!)  Cry

           

          I was hoping to do it without learning scripting, but, if that is the only way, then I guess I have to learn scripting.  Is Filemaker's help guide on the computer adequate for learning? 

          I just took a look at the instructions for scripting and it starts with planning your script.  I don't know anything about scripting, so how can i plan the script?   This assumes I already understand how scripting works.  It looks like a long slog learning scripting. 

          • 3. Re: Detecting scheduling conflicts
            Sorbsbuster

            That's maybe a little harsh, as the page 'Planning a script' does actually walk throught the steps to planning a script.  You have almost planned your first script, because you said, "There are a large number of records, and the only way I know to detect room conflicts is to sort by start time >room >days and then look for conflicts.  This is time consuming, and I sometimes miss conflicts where the start times are different, but there is an overlap in the time periods from start to end. " - you have worked out what the manual steps are that you want Filemaker to repetitively perform for you.  Almsot certainly if you can define step-by-step what you would do manually you will be able to string those steps together into a script.

            There are whole books devoted to scripting in Filemaker because it is such a versatile toolkit - it almost makes the calculations available pale into insignificance when you realise what can be done by stringing script steps together.

            • 4. Re: Detecting scheduling conflicts
              philmodjunk

              It's also possible to check for conflicts strictly with relationships--something I didn't figure out when I first started helping others with this issue.

              As specified in the links Sorbsbuster helpfully provided, conflicts occur when the date range of previous reserveration records for the same resource item overlap in one one of three ways. A script can check on all three criteria, but a calculation can also check three relationships each based on one of the same three criteria.

              Start date of new reservation falls within existing reservation date range:

              Table::ResourceID = StartCheck::ResourceID AND
              Table::ReservationID ≠ StartCheck::ReservationID AND
              Table::StartDate > StartCheck::StartDate AND
              Table::StartDate < Startcheck::EndDate

              End date of new reservation falls within existing reservation date range:

              Table::ResourceID = EndCheck::ResourceID AND
              Table::ReservationID ≠ EndCheck::ReservationID AND
              Table::EndDate > EndCheck::StartDate AND
              Table::EndDate < EndCheck::EndDate

              Date Range of New reservation encloses an existing reservation date range:

              Table::ResourceID = EncloseCheck::ResourceID AND
              Table::ReservationID ≠ EncloseCheck::ReservationID AND
              Table::StartDate < EncloseCheck::StartDate AND
              Table::EndDate > EncloseCheck::EndDate

              Then a calculation such as:

              StartCheck::ResourceID or EndCheck::ResourceID or EncloseCheck::ResourceID

              will evaluate as True if there is a schedule conflict between the current reservation record and another reservation record in the table.

              Note that Table, StartCheck, EndCheck and EncloseCheck are all occurrences of the same table. You can also use global fields for the "Table" side of each relationship to make it possible to enter all the needed data into global fields to check for conflicts before using the data in the global fields to create a new reservation record. With that approach, the second field pair--that matches ReservationID's is not needed. (It is included to keep a record from matching to itself here.)

              1 of 1 people found this helpful
              • 5. Re: Detecting scheduling conflicts
                dgroyal

                     Thanks so much for this!  I'm building a scheduling database for a university program, and I need it to detect time conflicts in student schedules.  I did a modified version of what you suggested above.

                     First, I related the table containing each reservation to itself, where day = day, student ID = student ID, and reservation ID ≠ reservation ID.  That way, I could leave these three fields out of the calculation.  Then I did a calculation for each of the 3 cases described above, using start time and end time instead of start date and end date.  Thanks again!  This made my life much easier.

                • 6. Re: Detecting scheduling conflicts
                  Sorbsbuster

                       Your initial post said, "I would like to know if Filemaker Pro 7 can detect room conflicts (Two people trying to use the same room at the same time)."  But your last post said, "I need it to detect time conflicts in student schedules".

                       Maybe I have misunderstood what you are doing, but from memory I think Phil's tips were about a college or hotel booking rooms, and being concerned that a room was booked twice.  Your calculations look like they are checking if a Student has been booked to be two places at once.  (A perfectly valid, but very different, check.)

                  • 7. Re: Detecting scheduling conflicts
                    dgroyal

                         You're absolutely right, sorry for the confusion.  I'm not the original poster.  I just used this approach to handle a different problem and wanted to say thanks.

                    • 8. Re: Detecting scheduling conflicts
                      Sorbsbuster

                           Whoops!  Sorry - hadn't spotted the change of names!  You're right, of course - Phil's concept is perfectly adaptable to your 'Student-oriented' scenario.

                           Sorry.

                      • 9. Re: Detecting scheduling conflicts
                        DavidJondreau

                        This is an old thread, but related to a recent post, so I'll comment by saying one relationship with these criteria should do it all:

                         

                        Table::ResourceID = Check::ResourceID

                        Table::ReservationID ≠ Check::ReservationID

                        Table::StartDate < Check::EndDate

                        Table::EndDate > Check::StartDate

                        1 of 1 people found this helpful
                        • 10. Re: Detecting scheduling conflicts
                          philmodjunk

                          Dave, I completely agree.

                           

                          It's an old post and there are better, simpler ways to do this.

                          • 11. Re: Detecting scheduling conflicts
                            hectorfernandez

                            David, your relationship solution sounds like just like what I need to manage our rental inventory of furniture, but I can't for the life of me figure out how to make it work. I have not been able to find any more detailed posts but that does not mean they are not out there.

                             

                            I have 3 tables:

                            JOBS (records of the events we ship furniture to which include a start date and an end date)

                            INVENTORY (records of all the furniture we stock)

                            TRANSACTIONS (records of all the transactions related to the inventory items - Reserved, Shipped, etc).

                             

                            The INVENTORY and TRANSACTIONS table are related through the primary and foreign keys "InventoryItemID".

                            The TRANSACTIONS and JOBS tables are related through the primary and foreign keys "JobID".

                             

                            When as item is reserved for a job a transaction is created with the start and end date of the job and the type of transaction is set as "Reserved". When the item is shipped the transaction type to changed to "Shipped". And finally then the items is received in the warehouse the transaction type is changed to "Received".

                             

                            Currently the only way I have of determining if an item is available is by using a calculation field that totals up the "Reserved" and "Shipped" transactions and subtracts it from the quantity of items owned. The problem is this is too broad. Items can be reserved several months in advance. All the reserved items reduce the quantity available even though they will be returned an ready to ship out again by the time a future job starts.

                             

                            I view the status of an inventory items in a layout based on the INVENTORY table. There I can see all the transactions related to the item in a portal based on the TRANSACTIONS table.

                             

                            After reading your post I have set up the following relationships between table occurrences of the TRANSACTION table (I believe this is correct):

                            Transactions::_fk_ItemID = TransactionsCheck::_fk_ItemID

                            Transactions::_pk_TransactionID ≠ TransactionsCheck::_pk_TransactionID

                            Transaction::JobShipDate ≤ TransactionCheck::JobReceiveDate

                            Transactions::JobReceiveDate ≥ TransactionsCheck::JobStartDate

                            Screen Shot 2017-05-17 at 2.19.44 PM.png

                             

                            So now that I have these relationships, how do I view the resulting data? I related the INVENTORY (Inventory Availability T.O.) table to the TRANSACTION (Inv Transactions T.O.) table through the primary and foreign key "ItemID" fields and created a layout based on the INVENTORY table. But the portal based on the TRANSACTION table does not show any transactions.

                             

                            I must admit creating multiple relationships between 2 identical table occurrences with differing inequality symbols is a bit of a mystery to me. I assume when all those criteria are met then a value is generated...

                             

                            Any help would be greatly appreciated.

                            • 12. Re: Detecting scheduling conflicts
                              DavidJondreau

                              The trick is you're trying to find the determine the availability of a piece of inventory on a specific day and you're viewing that from the Inventory table.

                               

                              I think you want to actually make that relationship between Inventory Availability and Inv Transactions. You'll need an extra field in Inventory for you to enter in the day you're checking. A field with global storage is appropriate for this.

                               

                              If you just want to check a single day, your relationship will be

                              Inventory::_pkItemID = TransactionsCheck::_fkItemID

                              Inventory::JobGlobal ≥ TransactionsCheck::JobStartDate

                              Inventory::JobGlobal ≤ TransactionCheck::JobReceiveDate

                               

                              If you just want to check a date range, you'll need two globals. One for Ship and one for Receive.

                              Inventory::_pkItemID = TransactionsCheck::_fkItemID

                              Inventory::JobReceiveGlobal ≥ TransactionsCheck::JobStartDate

                              Inventory::JobShipGlobal ≤ TransactionCheck::JobReceiveDate

                               

                              Then you have a calculation field in Inventory that subtracts the quantities from TransactionCheck from the total quantity in Inventory. Enter your start and end dates and that field will update with the quantity available.

                              1 of 1 people found this helpful