Drop-down fields are essential for letting users select from lists of options. With this release, they become much more versatile: you can now assign formulas to the Values property of number drop-down fields and text drop-down fields.
The benefits are numerous:
- Reference data table columns directly instead of manually copying values.
- Create multi-level drop-downs where selecting from one field determines the options in another.
- Filter drop-down options based on text input, date ranges or other criteria. (Like the role of the signed-in user.)
- Sort options differently based on user input.
Assigning a formula
To associate a formula with the Values property, press the new fx button in the inspector:
Array syntax
Dynamic drop-downs use arrays (lists of values) to provide their options. Arrays are written between curly braces, with elements separated by commas:
This creates a drop-down with three numeric options. For text drop-downs, use quoted strings:
You can conditionally include elements using IF. If 7 should only be available when Field1 is valid, use this formula:
When Field1 is invalid, the drop-down shows only “3” and “5”. When valid, all three options appear.
To use the values of another drop-down, MyDropDown, use this formula:
When you determine the values to use by setting them directly in the designer, you can simply select one of them from the drop-down to determine which value is shown initialy.
That doesn’t work if you use a formula. In that case, you need to assign a formula to the InitialValue property.
What you can build
This enhancement enables several practical techniques. We’re launching with two detailed tip posts:
-
Tip: Populate drop-downs from data tables. Pull values directly from data table columns, eliminating manual copying and keeping everything synchronized automatically.
-
Tip: Multi-level drop-downs. Create drop-downs that adapt their options based on selections in other drop-downs, enabling sophisticated multi-level filtering.
All next year, we’ll be publishing additional tips covering text-driven filtering, date-based filtering, role-driven access control, dynamic sorting, and working with initial values in formula-driven drop-downs.
For a comprehensive overview of all available techniques, see the Values documentation, which includes concise explanations of the complete range of possibilities with formula-driven drop-downs.
We have also improved the performance of drop-down fields. While we don’t suggest that you put thousands of options in a drop-down field, Calcapp should now be able to handle that many options.