Filter drop-down based on date range and other fields (RV Park Reservations)

[LLake]

I have built a system for taking reservations and keeping track of customers currently staying on sites at an RV park. 

I currently use a Google Form to take reservation requests that are accepted or denied. During the approval process, a camp site is selected by the app-operator for the reservation to stay on, customers can not pick their own camp site. 

Upon being approved, the row in Reservation Requests is moved to the Reservations table. Upon being checked-in, the row in Reservations is moved to Customers table. All three tables have a ref to the site numbers in the Sites table /  Standard Sites slice. 

I would like the camp site # drop-down selectors to filter and only show site #'s that are:

1. Compatible by power availability ([RV Amperage] vs [Site Amperage]) (30 AMP or 50 AMP enum) (Some sites support both or one)

2. Compatible by site length ([Reservations].[RV Length] <= [Sites].[Site Length]) 

3. Compatible by date range availability ([Date In] to [Date Out])

-Date range availability is where I am having the most trouble creating an expression. The expression would have to take into account future reservations in one table and customers currently checked into the site on another table.

I would appreciate any help in figuring out how best to handle this expression.

Tables:

Sites - Campsites with the key column being the camp site numbers (265 total)

Seasonal Site Slice - Campsites that don't take reservations

Standard Site Slice - Campsites available for reservations, all reservation and customer related tables ref back to this slice

Customers - Reservations that have been checked-in

Reservations - Reservations that will be checked-in in the future

Reservation Requests - Google form results from people who would like to make a reservation, to be accepted or denied

Columns:

[Site] [Site Length] [Site Amperage]
[RV Length]
[RV Amperage]
[Date In]
[Date Out]

[WillowMobileSys]

---

@LLake wrote:

-Date range availability is where I am having the most trouble creating an expression. The expression would have to take into account future reservations in one table and customers currently checked into the site on another table.

---

First, I would recommend NOT moving rows between tables.  A Reservation is a reservation.  Simply use columns to segregate Active stays from future stays.  Maybe a column named [Checked In?].  Then in the app you can separate the rows into different datasets using Slices.  This helps increase efficiency (i.e. performance) in the app, helps with maintenance and simplifies your Scheduling expression.

To find available sites by Date Range, basically you want to check that existing reservations DO NOT overlap - ie: 

1. Reservation End Date < [Date In]
2. Reservation Start Date > [Date Out]

Your expression need to search for Sites where overlapping reservations is ZERO.  It would be something like this (not a full expression):

SELECT(Standard Sites[Site ID],
     AND(
         [Site Length] = [_THISROW].[RV Length],
         <<other site to RV comparisons,
         ...
         COUNT(SELECT(Reservations[Reservation ID], 
                   AND([Site] = [Site ID], 
                       [End Date < [_THISROW].[Date In],
                       [Start Date] >[_THISROW].[Date Out]))
         ) = 0
     )
)

 NOTE: This relies on having different names for the site id columns.

IF you do need/want to have the separate tables for future reservations versus checked-in reservations then the expression can be modified like the below. 

SELECT(Standard Sites[Site ID],
     AND(
         [Site Length] = [_THISROW].[RV Length],
         <<other site to RV comparisons,
         ...
         COUNT(SELECT(Reservations[Reservation ID], 
                   AND([Site] = [Site ID], 
                       [End Date < [_THISROW].[Date In],
                       [Start Date] >[_THISROW].[Date Out]))
               +
               SELECT(Customers[Reservation ID], 
                          AND([Site] = [Site ID], 
                              [End Date < [_THISROW].[Date In],
                              [Start Date] >[_THISROW].[Date Out]))
         ) = 0
    )
)

These expressions are just a guide.  You may need to tweak them for your use case.

I hope this helps!

[LLake]

Thank you for this! I finally feel like I'm on the right track. I tried adapting the multi-table expression to my data sets and columns but I am receiving the following error:

Condition AND(([Site Length] >= [_THISROW].[RV Length]), CONTAINS([Site Amperage],[_THISROW].[RV Amperage]), COUNT((SELECT(Reservations[Unique ID],AND((Reservations[Site] = Standard Sites[Site]), ([Date Out] < [_THISROW].[Date In]), ([Date In] > [_THISROW].[Date Out])))+SELECT(Customers[Unique ID],AND((Customers[Site] = Standard Sites[Site]), ([Date Out] < [_THISROW].[Date In]), ([Date In] > [_THISROW].[Date Out])))))) has an invalid structure: subexpressions must be Yes/No conditions

Expression:

SELECT(Standard Sites[Site],
AND(
[Site Length] >= [_THISROW].[RV Length],
CONTAINS([Site Amperage],[_THISROW].[RV Amperage]),
COUNT(SELECT(Reservations[Unique ID],
AND(Reservations[Site] = Standard Sites[Site],
[Date Out] < [_THISROW].[Date In],
[Date In] > [_THISROW].[Date Out]))
+
SELECT(Customers[Unique ID],
AND(Customers[Site] = Standard Sites[Site],
[Date Out] < [_THISROW].[Date In],
[Date In] > [_THISROW].[Date Out]))
)) = 0
)

Any ideas would be appreciated! 

[LLake · Accepted solution]

@WillowMobileSys 

After many iterations and attempts to get this drop down filter working, I ended up having to change to a different methodology to achieve the functionality. The formula structure you suggested works well for checking the currently selected site for compatibility by date, but it was not able to iterate over all sites and perform the check. The amperage and length portion worked fine.

In the end I set up another table that represents all of the dates in our regular season, 216 days total, to Base-36 codes. (Eg. 6/25/2023 = F6). From there I set up expressions to generate a list of Date Code values that represents the date range for each customer and reservation based on their date in and date out. Another expression built a master list of unique date codes for each site by combining the codes of all reservations and customers on each site.

From there I was able to use the FILTER function to compare the date codes of a reservation or customer to the master code lists of all sites and produce a list of compatible sites. This solution works dynamically and updates automatically while editing a reservation or customer. 

So far it works well and isn't bogging down sync performance, we will see how that pans out next season with a full dataset in the tables. Perhaps not the most efficient way to handle the data and tables but hey, it works.