Preventing Scheduling Conflicts
I'm helping develop an app to track and reserve company vehicles. One of the requirements given to me was to prevent scheduling conflicts for the vehicles - essentially not allowing any overlaps for a given vehicle. I have searched this before and I found some answers on these forums that helped point me in the right direction but I'm still coming up short.
What I've done is pair two formula queries into a field that would check for start and end time overlaps and add the queries together. The field is simply called Conflicts and if it is over 0, I know there is a conflict. My issue is two-fold.
The first formula I used and works (sort of, we'll get to that) is
Size(GetRecords("{7.OAF.'"&[Start]&"'} AND {7.OBF.'"&[End]&"'} AND {10.CT.'"&[Vehicle - Vin Last Four]&"'}",[_DBID_RESERVATIONS])) +
Size(GetRecords("{6.OAF.'"&[Start]&"'} AND {6.OBF.'"&[End]&"'} AND {10.CT.'"&[Vehicle - Vin Last Four]&"'}",[_DBID_RESERVATIONS]))First things first is that this means each record will presumably 'find itself' so the result will always be at least 2. This is mitigated by simply adding '- 2' to the end to account for that. The trouble is that if you create a new record, it's the pre-existing records that count the conflict, not the new one, which is useless if I'm trying to use a form rule to prevent a new record from conflicting with others. Swapping the OAFs and OBFs seems to work logically but doesn't deliver the expected results. So that's my first sticking point is that I can't seem to get the logic in this formula correct. I just need to check to see if the starting or ending time falls between the start and end times of other records using the same vehicle.
My second issue is that even testing this with NOT accounting for a default result of 2, I can't get the form rules to stop a save if the conflict is > 0. Which does make sense to me since it can't count the records before it exists, but I'm not sure how else to notify someone that the record they are trying to create has a conflict. The form rule does work if I reopen the record but that doesn't help during creation.
Does anyone have any ideas on how to get this working? Many thanks in advance!
I think that the formula you want to look for conflicts would be
var text QUERY =
"{7.OAF.'" & [Start] & "'}" // 7 is the fid of the field [END]
& " AND "
& "{6.OBF.'" & [End] & "'}" & " AND " // 6 is the fid of the field [Start]
& "{8.EX.'" & [Vehicle Identifier] & "'}"; // replace 8 with the field ID for the [Vehicle Identifier]Size(
GetRecords($QUERY))I believe that it will count the unsaved record, so it does not matter if the record is saved. If the value returned is >1, then there is a conflict overlap.
