Schedule overlap issue

Egor_P
Participant I

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

Solved Solved
0 2 803
1 ACCEPTED SOLUTION

Steve
Participant V

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?โ€

View solution in original post

2 REPLIES 2

Steve
Participant V

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?โ€

That solves it, thanks!

Top Labels in this Space