Drop-down fields are
a great way to enable your users to choose from a pre-defined set of
values, either text stings or numbers. To access the selected value
from a formula, simply write the name of the field: IF(MyDropDownField =
...).
Up until this release,
accessing the value has been the only way of determining the value
selected by your user. Now, there’s an additional way: the
Index property. To access it from a formula, write
MyDropDownField.Index.
This property returns a number which is 1 if the first value is selected. If the last value is selected, the Index property returns a number equal to the number of values you have defined for the field. If you have added three values and the last value is selected, the Index property returns 3. If no value has been selected, the property returns a blank value (which you can check for using the ISBLANK function).
There is another new property: Size, which returns the
number of values you have added to the field. Write MyDropDownField.Size to
access it from a formula. It enables you to conveniently determine if
the user has selected the last item from a formula: IF(MyDropDownField.Index =
MyDropDownField.Size, ...).
Using the INDEX property with the new CHOOSE function
It is typical to use the IF formula function to select a different value based on the value selected by your user. If you have a drop-down field named Product, the formula might look like this:
The new CHOOSE function
takes a number as its first parameter, which determines what
parameter should be returned by the function. CHOOSE(2, "one", "two",
"three") returns “two”.
This is perfect for the new Index property. Just pass the index as the first parameter to the CHOOSE function and pass the desired values as the other parameters. The formula above may be written as follows using CHOOSE:
This version is clearly much shorter and far easier to maintain when you need to update your data. The only downside is that your formulas are now dependent on the order of your drop-down field values. If you change the ordering, you must update all formulas.
We added the Index property partly to let you more easily convert spreadsheet data tables to formulas. You can read more about doing the conversion here and also watch a video of the process.