1 Reply Latest reply on Apr 29, 2013 8:39 AM by philmodjunk

    Validating Schedule Times

    NickUlrich

      Title

      Validating Schedule Times

      Post

           Hey all,

           This forum has given me a ton of great advice in the past... hoping for a little bit more!

           I'm adding a section to an existing database that includes scheduling for our deliveries. The design has been relatively simple, but I can't think of a way to Validate the entries in a way that times are "blocked" such that you can't accidentally double-book a spot of time.

           Fields:

           Destination

           Departure Time

           Time Blocked

           End Time (calc: Departure Time + Time Blocked)

            

           Basically, if I schedule a delivery at 8am with 30 minutes blocked for it, the next availble time would be 8:30. I want to generate an error if someone tries to schedule a delivery between 8 and 8:30 or schedules a delivery with an end time between 8 and 8:30. This strikes me as needing some clever math more than anything.

           The database is already script heavy so it really doesn't matter whether this uses field validation or is scripted out. I'm open to any and all ideas.

            

           Thanks in advance!

        • 1. Re: Validating Schedule Times
          philmodjunk

               I'd set up a related table linked by date so that every record with a given date represents a reserved or "blocked" 30 minute interval (assuming that 30 minute intervals are the smallets period of time you want to block out). These records can also contain a foreign key match to a delivery record so you can tell which deliveries blocked out one or more such time intervals and so that you can easliy "unblock" those times if a delivery is cancelled or rescheduled.

               You can then check for any existing records in a range of values for a given date to see if a needed block of time is free. You can even use a portal to see whether or not the time interval in question is free when setting up your delivery record.

               Note that relationships can use inequalities so that a relationship like this could be used to check for reserved blocks of time:

               Deliveries::Date = TimeBlocks::Date And
               Deliveries::Departure Time < TimeBlocks::Time And
               Deliveries::End Time > TimeBlocks::Time

               And to reserve blocks of time, you'd use a script to create the needed records in a loop that starts creating records with Departure time and increments by 30 minutes until End Time is reached.