5 Replies Latest reply on Oct 17, 2012 2:57 PM by steiner888

    Validation Script for non overlapping tasks

    steiner888

      I am trying to implement a simple scheduling function which writes tasks to a tasks database. However when a task is created it can be repeated for a number of weeks via a script. I would like to build in a validation step to make sure that the assignee does not have an overlapping task.

       

      ie. a typical task is between 1 and 5 hours long. Thus if they start at 1 pm and finish at 4, I would like the script to pause if there is another job booked in for this assignee for the same day which starts/ends at 3pm and show a warning and would force me to edit the timing of the event.

       

      I am not sure how best to go about this. I have tried it with related records and constraining found sets etc but I have not been able to make this implementation work. any ideas?

        • 1. Re: Validation Script for non overlapping tasks
          Mike_Mitchell

          Hello, steiner.

           

          I've never needed to do something exactly like this, but just off the top of my head, you might consider thinking in terms of time slots. Make up a table that contains your task record ID, your assignee ID, a date, a start time and an end time, but make each record consist of a fixed period of time - say, the minimum time a task might take. That might be 15 minutes, 30 minutes, an hour, whatever. So each record might have these fields:

           

          taskID

          personID

          date

          timeStart

          timeEnd

           

          When you create a new task, you'll populate this table with a number of records for that task, based on how long the task takes. Let's say the task is your example - starts at 1 PM and ends at 4 PM. Let's also say your minimum time is 1 hour. So your records might look like this:

           

          taskID            2

          personID         6

          date               10/12/2012

          timeStart        1:00 PM

          timeEnd         2:00 PM

           

          taskID            2

          personID         6

          date               10/12/2012

          date               10/12/2012

          timeStart        2:00 PM

          timeEnd         3:00 PM

           

          taskID            2

          personID         6

          date               10/12/2012

          date               10/12/2012

          timeStart        3:00 PM

          timeEnd         4:00 PM

           

          Okay, so what's the point? This structure does a couple of things for you:

           

          1) You can easily count up the number of hours by using Count or Sum from the parent Task and Person tables.

          2) You can easily detect conflicts by looking for a record in this table that has a timeStart value equal to any value equal to the start time of the new task's start time.

          3) You can join this back to your parent Task and Person tables to figure out the schedules easily enough.

           

          Of course, you do have a couple of disadvantages:

           

          1) You have a lot more records to keep track of.

          2) You have an increase in complexity in your database.

           

          This is just off the top of my head. Someone smarter than I am probably has a better idea.

           

          HTH

           

          Mike

          • 2. Re: Validation Script for non overlapping tasks
            comment

            steiner888 wrote:

             

            I would like the script to pause if there is another job booked in for this assignee for the same day which starts/ends at 3pm and show a warning

             

            If you are doing this by script, you could simply perform a find for overlapping tasks. Assuming you have variables holding the proposed task's attributes, try:

             

            Set Error Capture [ On ]

            Enter Find Mode [ ]

            Set Field [ Tasks::AssigneeID ; $assigneeID ]

            Set Field [ Tasks::TaskDate ; $taskDate ]

            Set Field [ Tasks::StartTime ; "<" & $endTime ]

            Set Field [ Tasks::EndTime ; ">" & $startTime ]

            Perform Find [ ]

             

            If no records are found, go ahead and create the proposed task.

            1 of 1 people found this helpful
            • 3. Re: Validation Script for non overlapping tasks
              steiner888

              It is sort of the other way round from what i had tried. I think this makes sense. I will implement that and see how that works... will report back.

              • 4. Re: Validation Script for non overlapping tasks
                steiner888

                Thanks for this. I think that this will work but it may be a little complicated as you pointed out. I wanted to work with 15 minute slots from the outset but decided against it as I am thinking of implementing the seedcode Calendar with the tasks at a later date.

                • 5. Re: Validation Script for non overlapping tasks
                  steiner888

                  I have actually now built this into the original task creation script.

                   

                  as follows:

                   

                  proposed date and time fields

                  Global::Date

                  Global::Start Time

                  Global::End Time

                   

                  Then I run a script to find all those assignees that are free at those time on that day as follows:

                   

                  (in the assignee table, List view, with related tasks portal)

                   

                  Enter find mode []

                  Set field [ Tasks::Date ; Global::Date ]

                  Set field [ Tasks::Start Time ; "≤" Global::Start Time ]

                  Set field [ Tasks::End Time ; "≥" Global::Start Time ]

                  Perform Find []

                  Enter Find mode []

                  Set field [ Tasks::Date ; Global::Date ]

                  Set field [ Tasks::Start Time ; "≤" Global::End Time ]

                  Set field [ Tasks::End Time ; "≥" Global::End Time ]

                  Extend Found Set []

                  Show omitted only.

                   

                  Note: I found it easier to think of finding those tasks which are booked and excluding those assignees, rather than the time which is free.

                   

                  I can then either select an assignee who I now know does not have a task scheduled or use my preset assignee who will be in the list if he is free. If he is not then an error message displays and the script pauses. This then goes on to setting the task.

                   

                  Edit: I guess I could make this also include the assignee ID but I decided to leave this open.

                   

                  Hope that this helps anyone else looking into this.