How to check if a Date is in between of 2 other Dates?

[Triggerz]

Hi,

I created an entity to record the bookings of conference rooms. Before saving, I wanted to check if there is already an existing record with a schedule conflict to what is being added.
I created the formula below and placed it in the API Before Save Script formula section, but I couldn't get it the right logic to work. Not sure if there is a Between() function that can easily do this. Please help.

$bInsideExist = record\exists('CConfRoom', 'dateStart>=', dateStart, 'dateEnd<=', dateEnd);
$bFrontExist = record\exists('CConfRoom', 'dateStart<=', dateStart, 'dateEnd<=', dateEnd);

if($bStartExist){
recordService\throwForbidden('The date & time of this meeting has a conflict with an existing booking. Please select a different Date & Time');
}

Thanks...

[lazovic]

Hi Triggerz,

Overall, this formula script should work fine. Can you provide an example of events and their start and end dates so we can try to reproduce the issue?

[Triggerz]

Hi lazovic,

I have an existing record that has the following dates :
startdate = 02/26/2025 10:00 AM
enddate = 02/26/2025 11:00 AM

If a user tries to add a new record, I wanted to check if there is already an existing record that conflicts with the new record's startdate and enddate.

For example, if the new record contains the dates below, it should prevent the user from saving it.

newstartdate = 02/26/2025 10:30 AM or newenddate = 02/26/2025 10:30 AM since these dates have conflicts with an existing record.

another example is (newstartdate = 02/26/2025 09:30 AM and newenddate = 02/26/2025 11:30 AM), since there is already a record that has the schedule of 02/26/2025 10:00 AM to 02/26/2025 11:00 AM

Thanks

[Ashif Malayil]

Hi Triggerz,
I have implemented a similar scenario in my Appointment module, and it is working fine. You can refer the same.

Formula:

$conflict = record\exists('Meeting',
'dateStart <', dateEnd,
'dateEnd >', dateStart
);

if($conflict){
recordService\throwForbidden('The date & time of this Appointment has a conflict with an existing Appointment. Please select a different slot.');
}

[victor]

Triggerz,

Please ry the following solution:

if (record\exists('CConfRoom', 'dateStart<=', dateEnd, 'dateEnd>=', dateStart)) {
recordService\throwForbidden('The date & time of this meeting has a conflict with an existing booking. Please select a different Date & Time.')
}