Show available List of available items based on the selected dates


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?

How do you determine availability?

1 Like

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.

1 Like

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

1 Like

Thanks Steve! List subtraction is exactly what i needed.

1 Like