Discussions

Expand all | Collapse all

Eliminating double scheduling

Hans Hamm06-12-2017 17:47

  • 1.  Eliminating double scheduling

    Posted 06-12-2017 17:47


  • 2.  RE: Eliminating double scheduling

    Posted 06-12-2017 18:25
    I assume that you are scheduling resources (people). Are they scheduled by date and time or just date or a range of dates ....   how are they scheduled?


  • 3.  RE: Eliminating double scheduling

    Posted 06-12-2017 18:45
    Mark,
    Long time, no talk to!
    I do not know what happened... the post saved before I finished...

    Need this functionality on the Travel Form:
    On the save - check the following:
    Employee ID (on form) - listed on Travel Table
    Do the Start Date & End Dates (on form) - correspond to dates on the table

    If these are "true", the record cannot be saved with a message like this "The associate is already scheduled on a project in this time frame"
    If "false" the record can be saved.

    I am just not sure where to start on this...

    Thanks for your help!


  • 4.  RE: Eliminating double scheduling

    Posted 06-12-2017 18:50
    Can you explain your tables and their relationships and what an example of Double scheduling means to you.?


  • 5.  RE: Eliminating double scheduling

    Posted 06-12-2017 19:14
    1) Resets are recorded in the reset table, the ResetID is a text formula field and is used as the reference proxy to the travel table.
    2) Associates are recorded in the associate table

    What is happening a manager after scheduling the reset will schedule associates to work the project. Say at store 123, Joe is working 6/12 thru 6/24. Then the manager will schedule Joe again at store 456, from 6/19 thru 6/24. So, there is an overlap or double scheduling happening that I need to eliminate.


  • 6.  RE: Eliminating double scheduling

    Posted 06-12-2017 19:28
    This is not easy to do natively.  There are some somewhat brute force solutions I have implemented effectively for clients with similar resource scheduling clients, but that would take a one on one discussion and demo.  Basically it involved setting up summary fields for each day out for say a 30 window to count the number of bookings that employee has for each of the next 30 days.  Then I translated the numbers to pretty icons to highlight the double bookings.


  • 7.  RE: Eliminating double scheduling

    Posted 06-13-2017 15:35
    I developed custom app for resource booking in the past. It becomes necessary to compare timing of the proposed booking with the previous booked resources . Link for booking i.e. Book_It will not show up if there is any conflict with previous bookings.

    Neal
    NealPatil @ gmx.com



  • 8.  RE: Eliminating double scheduling

    Top
    Contributor
    Posted 06-14-2017 03:19
    In all honesty, the best, cleanest, fastest way to accomplish this is with some script.  You can even have a nice UI to select the available Resources.

    If you try the native and multiple relationships without success, let me know.


  • 9.  RE: Eliminating double scheduling

    Posted 06-14-2017 03:20
    This is one case where I agree.  The brute force native way is quite brutish.


  • 10.  RE: Eliminating double scheduling

    Posted 06-22-2017 12:18
    Under the gun to get some project reporting completed for the bosses... However, I am interested in your comment Matthew, but I am not familiar with script how to implement etc... can you point me in the right direction?


  • 11.  RE: Eliminating double scheduling

    Top
    Contributor
    Posted 06-22-2017 14:04
    This is actually a very simple problem if you understand "Allen's Interval Algebra":

    Allen's Interval Algebra
    https://en.wikipedia.org/wiki/Allen%27s_interval_algebra

    Years ago I posted a solution to this but it was about 3 forum versions ago and I don't know if my post survived into the current version of the forum.

    Let's assume you have no overlaps in your existing table and you have a start [s] and end [e] fields (and that in all cases [s] < [e]). Now consider adding a new record and lets refer to the values sitting in the form as "new start" ns and "new end" ne (and of course ns < ne). Note that there are only two fields we are referring to here [s] and [e]. The values ns and ne are just the proposed values a user is trying to enter into a new record if there is no overlap with existing records.

    You can't successfully submit the form with the new values ns and ne if there is an existing record for which following condition holds:

     [s] < ne AND ns <= [e]

    So all the script has to do is monitor changes in the form for ns and ne and perform an AJAX query to see if there is a conflict with an existing record using the above criteria. If there is a conflict your prevent the form from saving. If there is no conflict you save the form. It really is that simple.

    The only real thing you have to address is that you probably want to have some discrete time interval for your start and end fields such as 15 minutes, a day, a week etc.




  • 12.  RE: Eliminating double scheduling

    Posted 06-22-2017 18:18
    Dan... here is my big issue. I am not familiar with scripts. While I follow the explanation you provided on meeting the correct conditions, albeit at times in can get confusing, script is altogether different for me. How do I even begin to implement your suggestion?


  • 13.  RE: Eliminating double scheduling

    Top
    Contributor
    Posted 06-22-2017 18:22
    I am afraid I am out of time as I have to travel home shortly and my schedule is packed through the weekend. If I don't reply on my own by early next week ping me.


  • 14.  RE: Eliminating double scheduling

    Posted 06-22-2017 18:25
    Will do, thanks Dan


  • 15.  RE: Eliminating double scheduling

    Posted 02-07-2018 03:01

    Have same problem as above. Are there any suggestions?



  • 16.  RE: Eliminating double scheduling

    Top
    Contributor
    Posted 02-07-2018 03:29
    About a decade ago I implement in QuickBase something called Allen's Interval Algebra which is a generic approach towards modeling how to schedule and compare resources that can overlap in time. There are 13 possibilities of how two events can be related to each other.


    Yes each of these notations were used to write math equations such as "a proceeds b"

    Here Notation: a p b
    Allen Notation a < b
    Krokhin Notation: a p b

    These shorthand notations would be translated into various constraints on the start and end times of the two intervals a and b.

    Amazingly the basic querying for open time slots and overlap classification can be implemented natively in QuickBase. The major issue at that time was defining the desired  granularity - are you scheduling in terms week, days, hours of 15 minute intervals. Today there is a lot more freedom as we can use script.

    I don't have the application any longer but if you want to pursue a solution you can contact me off-world using the information in my profile:

    https://getsatisfaction.com/people/dandiebolt


  • 17.  RE: Eliminating double scheduling

    Posted 02-07-2018 03:43
    Thank for your reply. I am familiar with interval algebra, which was my initial idea of how to solve this problem. My issue is that I am not familiar with any language for scripting. I will have look myself and contact you if I get on a dead end.