Show available List of available items based on the selected dates

[yani]

Hi,

I have been stuck on this for a while. Any suggestions would be really helpful.

I have two tables, one is the Facility Master Data where we maintain all the list of facilities that can be booked, it has Columns FaciltyID and FacilityName
The other one is Request table with column FaciltyID, EventStartDate and EventEndDate.
Request table contains all the bookings made for each facilities.

Now, If I create a new Request, I only want to show in the dropdown list those facilities from Facility master data that are available based on the selected dates of my current request.
How can I achieve this?

[Marc_Dillon]

How do you determine availability?

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

Dropdown from Valid_If

How to create a dropdown that depends on a particular list of values.

[yani]

Availability base on the Request selected dates.

For example:

Facility Master Data Table
Facility A
Facility B
Facility C
Facility D

Request Table
Record 1:
FacilityID: Facility A
EventStartDate: 01/08/2021
EventEndDate: 05/08/2021

Record 2:
FacilityID: Facility B
EventStartDate: 03/08/2021
EventEndDate: 04/08/2021

In my new Request, I’m going to book for Dates 01/08/2021 to 06/08/2021
In my dropdown, I only want to see Facility C & D since A & B are already booked on the dates that I have selected.

[Steve]

Use list subtraction: get a list of all facilities, then subtract the list of already-booked facilities, leaving only not-booked facilities.

help.appsheet.com

List Subtraction

Subtract lists to produce another containing only the unique items of the first list that aren't also in the second.

[yani]

Thanks Steve! List subtraction is exactly what i needed.