Dependent drop-downs allow you to limit the values displayed in one drop-down based on the value selected in another drop-down. For example, this Lead Tracking app template asks for the Lead Region (America, Asia, Europe) and then for a Country within that region.
This actually requires relatively complex logic, but AppSheet tries to make it simple. Dependent drop-downs are driven by a separate lookup table.
In the sample, there is a separate
Regions lookup table with two columns:
Country. This acts as the lookup table for allowed combinations of regions and countries.
The Lead Region column has a regular
Regions[Region]. Therefore, when a new entry is being added, the input for this column shows three choices:
Country column also specifies a similar
Regions[Country]. However, because it follows the
Lead Region column and because both specify columns from the same lookup table
Regions, AppSheet recognizes the intent and implements a dependent drop-down.
Internally, AppSheet creates an expression to capture the allowed set of values for the
Country column. The expression must say (in English):
- Look at the
- Filter the rows to make sure that the
Regioncolumn of the table matches the value in the
Lead Regioncolumn of the row being edited in the form
- Now extract the
Countrycolumn from those filtered rows
- Eliminate any duplicates --- these are the allowed countries
- Recompute this list each time the
Lead Regionis changed
Strictly for an expression expert, here is the full underlying AppSheet expression:
IN( [_THIS], SELECT(Regions[Country], [_THISROW].[Lead Region] = [Region]))
While most app creators will never need to express something this complicated, you could in fact provide this expression as a
Valid_If constraint. It is useful to know for advanced use cases. For example, instead of using an equality condition, an app creator could use inequality or richer expressions to build very expressive dynamic drop-downs.
Disable dependent drop-downs
When the AppSheet platform finds several columns that have
Valid_If constraints from the same lookup table, it assumes that the desired behavior is to have depedent drop-downs. Occasionally, this may not be what you wanted.
In these cases, provide an explicit
SELECT() expression instead of just providing a column set expression. In other words, instead of