Conditional dropdown independence

School_Bus · 09-12-2021 07:15 AM

I’m trying to create a conditional dropdown and it sort of works. Here is the what I have Route, Pick Up Location, Drop Off Location

So I can set the Route, and that limits Pick Up location, but I am unable to select my Drop Off Location without selecting my pick up location.

I’d like to select Pick Up Location or Drop Off location independently so that my user can use both to filter or one to filter. However it seems to all be linked and I can’t unlink it.

Any ideas? The only think I’m doing is using the validif field, and appsheet is setting up the conditional drop downs by itself from there.

Marc_Dillon

Appsheet provides automatic “dependent dropdown” functionality under certain circumstances, which include hiding the later dropdowns until the earlier dropdowns have been filled in.

Basically, to avoid it, you just need to write a more complicated expression in valid_if instead of the basic Table[Column] in order to disable the automatic functionality.

See here for more info:

Steve

Valid If to choose pick-up location according to chosen route and/or drop-off location:

UNIQUE(
  IFS(
    ISNOTBLANK([route]),
      SELECT(
        route[location],
        ([route] = [_THISROW].[route])
      ),
    ISNOTBLANK([dropoff]),
      SELECT(
        route[location],
        IN([route], SELECT(routes[route], ([location] = [_THISROW].[dropoff]), TRUE))
      ),
    TRUE,
      SELECT(
        route[location],
        TRUE
      )
  )
)

Valid If to choose drop-off location according to chosen route and/or pick-up location:

UNIQUE(
  IFS(
    ISNOTBLANK([route]),
      SELECT(
        route[location],
        ([route] = [_THISROW].[route])
      ),
    ISNOTBLANK([pickup]),
      SELECT(
        route[location],
        IN([route], SELECT(routes[route], ([location] = [_THISROW].[pickup]), TRUE))
      ),
    TRUE,
      SELECT(
        route[location],
        TRUE
      )
  )
)

Valid If to choose route according to chosen drop-off and/or pick-up locations:

INTERSECT(
  IF(
    ISNOTBLANK([pickup]),
    SELECT(routes[route], ([location] = [_THISROW].[pickup]), TRUE),
    SELECT(routes[route], TRUE, TRUE)
  ),
  IF(
    ISNOTBLANK([dropoff]),
    SELECT(routes[route], ([location] = [_THISROW].[dropoff]), TRUE),
    SELECT(routes[route], TRUE, TRUE)
  )
)

School_Bus

@Steve thanks a lot. Worked like a charm. One more question on conditional drop downs. I have another dropdown, is there a way to sort it by another column in the row? For example I want to show _computedname but I would like to sort it by the LastName

thanks again for helping everyone in this community

Steve

The only way to sort by a column other than the one displayed is if the column value is of type Ref. If so, the value displayed will be the label column value of the referenced row, and the ORDERBY() function can be used to sort the list by any columns of the referenced rows.

If the values of the list are not of type Ref, the list can only be sorted by the list values themselves.