Can I use Valid_If to blackout certain dates from being selected

[rommel]

I have a desk reservation AppSheet app, and I want to create a feature to allow for future reservation of desks. I want to prevent users from selecting a Check-in / Check-out dates if there is already an existing reservation for that desk for those specific dates.

I have a Reservations table with the following columns Reservation ID, Desk ID, User, Checkin, Checkout

My idea is to use the data validity feature to filter the records in the Reservations table that match the Desk ID and are in the future, and somehow blackout the in-between dates when the user goes to select the Checkin and Checkout dates. I’m trying to wrap my head around what the Valid_If expression will look like, but I’m hitting a blocker - any help is much appreciated!

I know I’m able to filter the records in the table that match the blackout dates I want using something like this, but how would I then use these records to tell the user that these dates are unavailable?

SELECT(
  Reservations[Checkin],
  AND(
    ([Workstation] = [_THISROW].[Workstation]),
    ([Checkin] >= Today())
  )
)

Thanks in advance!

[Marc_Dillon]

Are you asking if you can achieve this?

If so, the answer is no. The calendar pop-up is purely a function of the browser/device, and can’t be modified by Appsheet. In fact, Firefox doesn’t even get a calendar pop-up!

[rommel]

Something like that would be ideal, but I figured that wouldn’t be possible. I’m thinking that if I use the Valid_If I can at least get something like this:

[Joseph_Seddik]

Here you have an unsolvable problem which is an infinite data set; all days of the future. If you turn your dataset into finite one, then you can have the key for a solution.

If your desks are all the same, and it doesn’t matter for the user which one he will have as long as he has one, then you should instead let your user enter his requested dates and you show him only the desks available between those dates in a drop-down list.

Otherwise, you should limit the days that can be reserved to a finite number, say 30 days in advance. Then equally you can show the available days for a particular desk to the user.

[rommel]

Hm that’s a good point with the infinite dataset, thanks! I’m okay with setting a limit for days that the user would be able to book in advance.

Not sure if I’m thinking about this the right way, but would it be possible to use the Valid_If to invalidate the Checkin date field if the user selection falls between two existing Checkin/Checkout dates, and do the same thing to the Checkout date field?

If I were to write some sudo code in Python to do this it might look something like:

filtered_reservations_table = existing reservation that match Workstation ID and are in the future

for reservation in filtered_reservations_table:
     if date_user_selected >= reservation[check in] OR date_user_selected <= reservation[check out]:
          invalidate entry

[Marc_Dillon · Accepted solution]

https://community.appsheet.com/search?q=prevent%20duplicates

[rommel]

Aha! Thank you very much, that’s exactly what I’m looking for

I’ll update the thread with the expression I eventually create so that it can help others with the same issue

[Joseph_Seddik]

Please read the articles Marc suggested, and you’ll have AppSheet exact not pseudo expressions

I’d also suggest to help your users choose the right dates from the beginning by using the Suggested_Values field.

[rommel]

Thanks, KJ! Great feedback for UX sake I appreciate the help

[rommel]

Updating this thread for visibility.

Ended up finding this other thread that helped me tremendously. In a nutshell, I updated the Valid_If expression on the checkout date to this:

AND(
 ([Checkout] >= [Checkin]),
 (COUNT(
   FILTER(
     "Reservations",
     OR(
       AND(
         ([Checkin] >= [_THISROW].[Checkin]),
         ([Checkin] < [_THISROW].[Checkout]), 
         ([Workstation] = [_THISROW].[Workstation]),
         ([Checkin] >= Today())
       ),
       AND(
         ([Checkout] > [_THISROW].[Checkin]),
         ([Checkout] <= [_THISROW].[Checkout]), 
         ([Workstation] = [_THISROW].[Workstation]),
         ([Checkin] >= Today())
       ),
       AND(
         ([Checkin] < [_THISROW].[Checkin]),
         ([Checkout] > [_THISROW].[Checkout]), 
         ([Workstation] = [_THISROW].[Workstation]),
         ([Checkin] >= Today())
       )
     )
   )
 ) = 0)
)

This throws off an error when there is a conflicting reservation that matches any of the conditions. For the Checkin Valid_If expression I simply need to check that the Checkin date is not in the past.

If someone chooses dates that have a conflict it presents the following message to the user:

Hope this helps someone!