Drop-down fields enable users to select a value from a menu, which is accessible from formulas.
For instance, a drop-down field can allow users to select from the products you offer. You can then use our data editor to associate each product with a price and use the XLOOKUP formula function to determine the price of the selected product for use in calculations.
However, from their inception and up until today, you have had to determine the values to show in the menu once for every drop-down field. Moreover, you haven’t been able to change what’s displayed in the menu based on data entered into the app.
For products defined in data tables, this limitation meant repeating product names in every drop-down field displaying them.
With this release, that changes. You can now assign a formula to the Values property of number drop-down fields and text drop-down fields, enabling dynamic values.
Assigning a formula
To associate a formula with the Values property, press the new fx button in the inspector:
To allow the user to select between 3, 5 and 7, use this formula:
If 7 should only be available if Field1 is valid, use this formula instead:
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. Learn more by reading this tip.
Examples of other uses
At first glance, this appears to be a minor enhancement, but it unlocks powerful features our customers have wanted for years. They are explored in-depth in the following blog posts:
- Tip: Populate drop-downs from data tables. Link drop-down fields directly to a column in a data table, eliminating the need to manually copy and paste values.
- Tip: Multi-level drop-downs. Use one drop-down field to determine the values displayed in another.
- [Tip: Text-driven drop-down filtering][drop-downs-filter-text-field]. Allow your users to filter a drop-down field using a text field.
- Tip: Filter drop-downs with switch fields. Use switch fields to refine the available drop-down values.
- Tip: Filter drop-downs using dates. Filter drop-down values based on start and end dates selected in date and time fields.
- Tip: Filter drop-downs using user roles. Private apps require users to sign in. A role may be assigned to a user. Use a role to filter what’s available in a drop-down field.
- [Tip: Change the sort order in drop-downs][drop-downs-sort-order]. For drop-downs with many values, the order they are presented in can make a big difference. Change the sort order to make its values easier to scan.
(Click the links above to access detailed blog posts describing how to realize the described features.)
We have also improved the performance of drop-down fields. While we don’t suggest that you put tens of thousands of values in a drop-down field, Calcapp should now be able to handle that many values.
Finally, we have also created a new sample app showcasing various common data-handling techniques, including filtering drop-down fields.