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
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.