It’s very common for drop-down fields to feature the exact same values that are part of a table column defined in the data editor. These values may be the products your company is selling, for example.
Prior to our latest release, these values had to be copied from the table and made part of the drop-down field. That meant that any changes made to the data table had to be manually carried over to the relevant drop-down fields.
Now that drop-down field values can be defined [using formulas][drop-downs-values-formula], there is a better way. Drop-down fields can now simply take their values from the table columns directly.
Imagine that we have a the following data table:
| Name | Kind | Size | Color | Price |
|---|---|---|---|---|
| Ocean Breeze Knit | Sweater | Medium | Blue | 29 |
| Deep Blue Comfort | Sweater | Large | Blue | 49 |
| Royal Glow Pullover | Sweater | Large | Purple | 39 |
| Violet Cozy Wrap | Sweater | Small | Purple | 29 |
| Moonlight Shift | Dress | Small | Gray | 69 |
| Midnight Elegance | Dress | Large | Black | 129 |
| Sapphire Flow | Dress | Medium | Blue | 72 |
| Shadow Flex Shorts | Shorts | Medium | Black | 39 |
| Onyx Sport Shorts | Shorts | Large | Black | 52 |
| Ash Drift Shorts | Shorts | Medium | Gray | 49 |
| Mystic Air Shorts | Shorts | Large | Purple | 19 |
Let’s say that we create a new form screen, Products, and use the data editor to create one named value for every column. The product name, for example, will then be accessible as the Name named value.
If we create a text drop-down field that should enable users to select a product, we can assign this formula to its Values property:
What if we want to enable users to select a color? This formula works, but is problematic:
The issue is that there are only four colors (blue, purple, gray and black), but the drop-down contains eleven colors — one for every product — including lots of duplicates.
We solve this problem using the UNIQUE formula function:
Finally, if we want the colors sorted in alphabetical order, we also need the SORT function:
Note: Named values and the data editor are not available in our Starter plans.