Schedule overlap issue

Dear Appsheet community,

I have recently faced a problem with data validity in the scheduling app. I am using the app to reserve trailers for specific events and to prevent reservations if that trailer is already booked within the required timeframe.

In order to do so, I used this example, which works well with my data and prevents me from making impossible reservations.

However, if I try to change some information about that trailer (driver/status) after it was saved, it prevents me by stating that my date selection is no longer valid, even if there aren’t any conflicts with other existing reservations. I can successfully add a new reservation with avoiding unavailable timeslots, but if I try to edit it immediately after saving it is no longer possible, resulting in “This entry is invalid” error.

I use the following Valid_if formula:

(COUNT(
SELECT(
Logistics[_ComputedKey],
AND(
[_THISROW].[Trailer] = [Trailer],
[_THISROW].[Arrival time] >= [Packing start],
[_THISROW].[Packing start] <= [Arrival time]
)
)
))=0

Could anyone tell me how to fix this issue? Thanks in advance!

UPD: Added a screenshot

This is a common problem.

Your expression counts all rows in your table that match the new row. If no existing entries match, the new row is valid. When adding a new row, the new row doesn’t yet exist in the table so your expression doesn’t find and count it. When you go back and edit that row, the row now exists in the table, so your expression does find and count it. To avoid the problem, have your expression specifically ignore the current row:

(COUNT(
SELECT(
Logistics[_ComputedKey],
AND(
[_THISROW].[Trailer] = [Trailer],
[_THISROW].[Arrival time] >= [Packing start],
[_THISROW].[Packing start] <= [Arrival time],
[_THISROW].[_ComputedKey] <> [_ComputedKey]
)
)
))=0

In effect, your original expression asked, “are there any rows that match this one?” My expression asks, “are there any other rows that match this one?”

4 Likes

That solves it, thanks!

1 Like