0

Is there a way to find overlapping appointments?

I would like to create a formula that shows / checks if an appointment is overlapping with another appointment.

Is this possible?

Thanks!

13 replies

null
    • Jorg
    • 5 yrs ago
    • Reported - view

    Hi Andre,

    You could use the following formula to check that and show a coloured warning:

     

    let mystart := start(Appointment);
    let myend := endof(Appointment);
    let over := cnt(select myAppointments where start(Appointment) < mystart and endof(Appointment) > mystart) + cnt(select myAppointments where start(Appointment) < myend and endof(Appointment) > myend) + cnt(select myAppointments where start(Appointment) >= mystart and endof(Appointment) <= myend);
    if over > 1 then
    styled("Overlapping", "orange", "warn")
    end

     

    Please adjust the table and field name to your database.

    Best, Jörg

      • Kruna
      • 2 mths ago
      • Reported - view

       Great!! Even though this post is 5yrs old, its exactly what I've been looking for. 

      Except one issue I would like to add. Lets say I have different employees - so I added

      let mystart := start(Appointment);
      let myend := endof(Appointment);

      let myempl := EmployeeName;

      let over := cnt(select myAppointments where myempl and start(Appointment) < mystart and endof(Appointment) > mystart) + cnt(select myAppointments where myempl and start(Appointment) < myend and endof(Appointment) > myend) + cnt(select myAppointments where myempl and start(Appointment) >= mystart and endof(Appointment) <= myend);
      if over > 1 then
      styled("Overlapping", "orange", "warn")
      end

      Even though its a different employee same appointment time - it shows the overlapping warning. Do you have an idea what i might be missing?

      Thnxs

      Kruna

    • Jorg
    • 2 mths ago
    • Reported - view

    Hi Kruna, 

    You do not compare myempl,  but you only check if it is != null - so you will get always a true

    It should be like this ... where myempl = EmployeeName and... 

    Does that help? 

    Best

    Jörg

      • Kruna
      • 2 mths ago
      • Reported - view

       thank you Jörg! Well not much - I changed to where myempl = EmployeeName and but still the overlapping message shows up. EmployeeName is a textfield, maybe this could be the error?

      thnx

      Kruna

       

      EDIT - I changed in a wrong field! Everything is perfect now - you were right! Thank you very much for help.

    • Alan_Cooke
    • 2 mths ago
    • Reported - view

    A long time ago you created a code script for me that checks to see if an assigned person had a conflicting assignment and opens a dialog to confirm.  Essentially it warns if a conflict has occurred and one can either cancel or accept the new assignment.  I am more than happy to post it here if it will help.  Obviously, it will require adapting.

      • Jorg
      • 2 mths ago
      • Reported - view

       Thanks for your help. Yes, please feel free to post it here ;) 

      Best, Jörg

      • Alan_Cooke
      • 2 mths ago
      • Reported - view

       

      let myStart := 'ASSIGNED FROM';
      let myEnd := 'ASSIGNED TO';
      let myEngineer := 'ROLE PLAYER';
      let myRole := 'ASSIGNED ROLE';
      let myDayRate := 'DAY RATE';
      let thisRecord := this;
      let result := "";
      function CheckAllAssignments() do
          let RP := 'ROLE PLAYER';
          let SD := 'ASSIGNED FROM';
          let ED := 'ASSIGNED TO';
          let CP := 'LINKED PROJECT';
          let c := count(select Assignments
                      where 'ROLE PLAYER' = RP and
                          (start('PERIOD BOOKED') >= SD and start('PERIOD BOOKED') <= ED or
                              endof('PERIOD BOOKED') >= SD and endof('PERIOD BOOKED') <= ED) and
                      TASK.'LINKED PROJECT' != CP);
          if c > 0 then
              " POSSIBLE CONFLICT!  This person is assigned to other Projects."
          else
              "NO CONFLICT.  This person is not assigned to other Projects."
          end
      end;
      let xOverlap := null;
      xOverlap := count(select Assignments
                  where TASK = thisRecord and 'ROLE PLAYER' = myEngineer and
                                  date(start('PERIOD BOOKED')) <= myStart and
                              date(endof('PERIOD BOOKED')) >= myEnd or
                          TASK = thisRecord and 'ROLE PLAYER' = myEngineer and
                                  date(start('PERIOD BOOKED')) >= myStart and
                              date(endof('PERIOD BOOKED')) <= myEnd or
                      TASK = thisRecord and 'ROLE PLAYER' = myEngineer and
                              date(start('PERIOD BOOKED')) <= myStart and
                          date(endof('PERIOD BOOKED')) <= myEnd or
                  TASK = thisRecord and 'ROLE PLAYER' = myEngineer and
                          date(start('PERIOD BOOKED')) >= myStart and
                      date(endof('PERIOD BOOKED')) >= myEnd);
      if xOverlap > 0 then
          let xDblBook := dialog("DOUBLE BOOKING!", 'ROLE PLAYER'.'FULL NAME WITH COMPANY' + " is booked for " + 'TASK TITLE' +
              " already. To Double Book over this period.  Select 'Yes' to confirm assignment or 'No' to cancel. 

      " +
              CheckAllAssignments(), ["Yes", "No"]);
          if xDblBook = "Yes" then
              let myID := Id;
              let NN := (create Assignments);
              NN.(TASK := myID);
              NN.(ROLE := thisRecord.'ASSIGNED ROLE');
              NN.('PERIOD BOOKED' := thisRecord.'PERIOD BOOKED');
              NN.('ROLE PLAYER' := thisRecord.'ROLE PLAYER');
              NN.('DAY RATE' := thisRecord.'DAY RATE');
              "'ASSIGNED ENGINEERS'.('WEB APP ENABLED' := true)";
              "thisRecord.('ASSIGNED ROLE' := null)";
              "thisRecord.('PERIOD BOOKED' := null)";
              "thisRecord.('ROLE PLAYER' := 0)";
              "thisRecord.('DAY RATE' := null)";
              "thisRecord.('ASSIGNED FROM' := null)";
              "thisRecord.('ASSIGNED TO' := null)"
          else
              "thisRecord.('ROLE PLAYER' := 0)"
          end
      else
          let dialog := dialog("Assign Role", CheckAllAssignments(), ["Continue", "Cancel"]);
          if dialog = "Continue" then
              let myID := Id;
              let NN := (create Assignments);
              NN.(TASK := myID);
              NN.(ROLE := thisRecord.'ASSIGNED ROLE');
              NN.('PERIOD BOOKED' := thisRecord.'PERIOD BOOKED');
              NN.('ROLE PLAYER' := thisRecord.'ROLE PLAYER');
              NN.('DAY RATE' := thisRecord.'DAY RATE');
              if 'ASSIGNED ENGINEERS'.'WEB APP ENABLED' then
                  'ASSIGNED ENGINEERS'.('WEB APP ENABLED' := true);
                  thisRecord.('ASSIGNED ROLE' := null);
                  "thisRecord.('PERIOD BOOKED' := null)";
                  thisRecord.('ROLE PLAYER' := 0);
                  "thisRecord.('DAY RATE' := null)";
                  "thisRecord.('ASSIGNED FROM' := null)";
                  "thisRecord.('ASSIGNED TO' := null)"
              end
          end
      end

      • Kruna
      • 2 mths ago
      • Reported - view

       wow, thats great Alan!! Thank you very much for sharing and I am more than happy about that.😃

      I was trying to adapt but somehow I cant see through the code well in order to be able to adapt.

      I hope its not asked too much, buut is there maybe a sample database, where I can look at to understand the script, please?

      Thanks a lot 

      Kruna

      • Alan_Cooke
      • 2 mths ago
      • Reported - view

       Unfortunately I do not have a sample DB for you.  Maybe Jorg could assist.  To be honest I have no idea how this code works other than in a broad sense.

      • Kruna
      • 2 mths ago
      • Reported - view

       no problem at all Alan. Some fields I can assume whether they're date field or else. But some other - no way - same happens with tables...😅 

      • Fred
      • 2 mths ago
      • Reported - view

      If I read the script correctly, thanks to the easy read variable names, this is the heart of the code:

      xOverlap := count(select Assignments
                  where TASK = thisRecord and 'ROLE PLAYER' = myEngineer and
                                  date(start('PERIOD BOOKED')) <= myStart and
                              date(endof('PERIOD BOOKED')) >= myEnd or
                          TASK = thisRecord and 'ROLE PLAYER' = myEngineer and
                                  date(start('PERIOD BOOKED')) >= myStart and
                              date(endof('PERIOD BOOKED')) <= myEnd or
                      TASK = thisRecord and 'ROLE PLAYER' = myEngineer and
                              date(start('PERIOD BOOKED')) <= myStart and
                          date(endof('PERIOD BOOKED')) <= myEnd or
                  TASK = thisRecord and 'ROLE PLAYER' = myEngineer and
                          date(start('PERIOD BOOKED')) >= myStart and
                      date(endof('PERIOD BOOKED')) >= myEnd);
      

      There is four searches going on in the xOverlap variable, separated by the 'or'. If xOverlap = 0 then there is no overlap.

      • Kruna
      • 2 mths ago
      • Reported - view

       thank you.👍 I will try to make a sample database for this code.😅 

      • szormpas
      • 2 mths ago
      • Reported - view

        Hi, I think the code could be simplified like below:

      xOverlap := count(select Assignments where TASK = thisRecord and 'ROLE PLAYER' = myEngineer
                                           and date(start('PERIOD BOOKED')) <= myEnd
                                           and date(endof('PERIOD BOOKED')) >= myStart);
      
      

      OR

      let mystart := start(Appointment);
      let myend := endof(Appointment);
      let over := cnt(select myAppointments where start(Appointment) <= myend
                                              and endof(Appointment) >= mystart));
      if over > 1 then
      styled("Overlapping", "orange", "warn")
      end
      

      Am I understanding this right, or have I just got the wrong end of the stick?

Content aside

  • 2 mths agoLast active
  • 13Replies
  • 1172Views
  • 5 Following