Dependent dropdowns allow you to limit the values displayed in one dropdown based on the value selected in another dropdown. For example, this Lead Tracker sample 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 dropdowns are driven by a separate lookup table.
In the sample, there is a separate 'Regions' lookup table with two columns: 'Region' and 'Country'. This acts as the lookup table for allowed combinations of regions and countries. Here is the table data used in the sample.
The 'Lead Region' column has a regular Valid_If constraint: Regions[Region]. Therefore, when a new entry is being added, the input for this column shows three choices: America, Asia, and Europe.
Likewise, the 'Country' column also specifies a similar Valid_If constraint: 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 dropdown.
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 Regions table
- Filter the rows to make sure that the Region column of the table matches the value in the 'Lead Region' column of the row being edited in the form
- Now extract the 'Country' column from those filtered rows
- Eliminate any duplicates --- these are the allowed countries!
- Recompute this list each time the 'Lead Region' is changed
Strictly for an expression afficionado, 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 dropdowns.
Disabling Dependent Drop
It can be convenient to create a single table that contains the dropdown values for several unrelated fields. In this case you want each dropdown to be independent of all other dropdowns, even though all of the dropdown values reside in one table.
Do this as follows:
- Create a worksheet and give it a meaningful name such as "Dropdown Values".
- Allocate a column for each independent set of dropdown values.
- Give each column a meaningful heading, such as "Department Names" and "Building Numbers".
- Fill in each column with valid dropdown values. For example, the "Department Names" column might contain "Accounting", "Payroll", and "Sales". The "Building Numbers" column might contain "Bldg 1", "Bldg 2", and "Bldg 3".
- In the field where you want the "Department Names" dropdown to appear, enter a Valid_If expression like this
SELECT(Dropdown Values[Department Names],TRUE)
- In the field where you want the "Building Numbers" dropdown to appear, enter a Valid_If expression like this
SELECT(Dropdown Values[Building Numbers],TRUE)