Feature: The Index property of drop-down fields

The new Index property of drop-down fields returns the numeric position of the selected value and the Size property returns the number of values. Index is designed to work well with the new CHOOSE formula function.

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:

IF(Product = "Product 1", 5.20, IF(Product = "Product 2", 5.20, IF(Product = "Product 3", 5.92, ...)))

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:

CHOOSE(Product.Index, 5.20, 5.20, 5.92, ...)

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.

« Letter: Having trouble editing very large apps? Feature: The CHOOSE function, an alternative to IF »