Multi-level drop-downs are a common pattern: select a country, then choose from states or provinces specific to that country. Select a category, then pick from products in that category. The second drop-down adapts based on the first selection.
Previously, this required complex workarounds involving multiple hidden drop-down fields that appeared and disappeared based on the first selection. Now that the Values property can be set using formulas that return arrays, multi-level drop-downs are straightforward to implement.
Example: North American regions
Here’s an app that lets users select a country in North America. The state or province drop-down adapts its values based on the country selection:
The app uses three named values: UsStates, MexicanStates and CanadianStatesTerritories. Here’s the start of the UsStates value:
The Values property of the state/province drop-down uses this formula:
This uses the SWITCH function, but IF, IFS and CHOOSE work equally well.
Dynamic labels
Canadian provinces are either states or territories. The drop-down label reflects this using a formula for the Label property:
Selecting a default value
Drop-downs with formula-based values don’t have a default selection. When the country changes, no state or province is initially selected.
To automatically select the first option, assign this formula to the InitialValue property:
The Item formula parameter accesses the current drop-down field, letting you reference its Values property. (You could also write State.ValuesState,Values to reference the field by name.)
For more InitialValue examples, see this blog post.
Note: This technique requires named values, which are not available in our Starter plans. You can work around this by directly embedding the array formulas instead of using named value references.