Dropdown list change depending some criteria

AppSheet AppSheet Q&A
1

Good day,

I have a sheet with a list of material, and another sheet with a list of events. Each event can use one or many materials. When an event is created, I’d like to display only the available material at the selected date.

For example :

  • Material A is reserved on 2024-10-03 and 2024-10-04
  • Material B is reserved on 2024-10-04 and 2024-10-05
  • If the user wants to create an event on 2024-10-03, only Material B is available
  • If the user wants to create an event on 2024-10-04, no Material is available
  • If the user wants to create an event on 2024-10-05, only Material A is available

Could someone guide me for the method please?

Thank you!

2

Hi,

Item Reservation - this is what you are looking for

COUNT(
SELECT(
Reservation[Reservation Id],
AND(
[_THISROW].[Equipment Choice] = [Equipment Choice],
[_THISROW].[End Date] >= [Start Date],
[_THISROW].[Start Date] <= [End Date]
)
)
) = 0

Appsheet template example - Item Reservation

1 Like
3

Hi,

the previous answer works by displaying a message about availability on the start and end dates.
I detail the option to automatically filter dropdown ref.

Using the previous template
Table Reservation
1.- create a slice called for example “reservado”
2.- row filter condition slide

AND(
[Start Date] >= [_thisrow].[Start Date],
[End Date] >= [_thisrow].[End Date]
)

3.- Edit Equipment Choice Field (Ref)
4.- Suggested values

Equipment[Name]-
SELECT(
  Reservado[Equipment Choice],
    AND(
      [Start Date]<=[_THISROW].[Start Date],
      [End Date]>=[_THISROW].[End Date]
    )
)

bicycles?, rooms?, machinery?, rural houses?, coworking? no problem.
As you enter the dates, dropdown updates

1 Like