Is there a way to find overlapping appointments?

Jörg 

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

Alan Cooke 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

Kruna 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.

Alan Cooke 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... 

Kruna 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.

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

Fred  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?