Eliminating double scheduling

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • In Progress
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
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?
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
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!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
Can you explain your tables and their relationships and what an example of Double scheduling means to you.?
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
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.
Photo of Neal

Neal

  • 70 Points
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

Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
This is one case where I agree.  The brute force native way is quite brutish.
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
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?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,502 Points 20k badge 2x thumb
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.


Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
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?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,272 Points 20k badge 2x thumb
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.
(Edited)
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
Will do, thanks Dan
Photo of Nikolaos Othonaios

Nikolaos Othonaios

  • 70 Points

Have same problem as above. Are there any suggestions?

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,502 Points 20k badge 2x thumb
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
(Edited)
Photo of Nikolaos Othonaios

Nikolaos Othonaios

  • 70 Points
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.