• Learn
  • Blog
  • Pricing
  • About
  • Sign in

Drop-downs and data

Drop-down fields enable users to select from a number of predetermined options:

Drop-down fields

A drop-down field either allows a user to select between numbers or between text strings. To make a field a drop-down field, select Number drop-down or Text drop-down from the menu in the inspector. The List values section in the inspector enables you to add and remove values. Press the Add value button to add values and the button to remove them. Drag the symbol to reorder values.

Select an initial value that is pre-selected for your user by selecting the corresponding option from the drop-down in the app designer (it reads °C to °F in the image above). If you don’t want an option to be pre-selected, select the first blank option. To enable your users to select a blank option, simply include an option under List values in the inspector with no content.

To use different formulas depending on the value selected by the user, you can use the IF function. In the image above, the Result field might use this formula to determine if degrees Celsius should be converted to degrees Fahrenheit or the other way around:

IF(Conversion = "°C to °F", (9/5) * Input + 32, (5/9) * (Input - 32))

Write shorter drop-down formulas with CHOOSE and Index

Drop-down fields provide a property named Index which returns 1 if the first option is selected, 2 if the second option is selected, etc. The CHOOSE function selects what parameter to return based on the number given as the first parameter, meaning that CHOOSE(2, "one", "two", "three") returns “two”.

Index and CHOOSE may be used to make the IF formula above shorter:

CHOOSE(Conversion.Index, (9/5) * Input + 32, (5/9) * (Input - 32))

This is especially advantageous if your drop-down field contains many options. The only downside is that formulas using CHOOSE and Index are dependent on the order of the options in the drop-down field.

Using spreadsheet data with drop-downs

Calcapp requires that all data is made part of formulas. We have written an app that converts spreadsheet columns to formulas targeting drop-down fields. These formulas use CHOOSE and Index, as above. We recommend that you continue maintaining your tabular data in spreadsheets and convert your columns to formulas every time you make an update.

The process of doing the conversion is described below. You can also watch a video of the process.

Let’s say that you want to convert a small table to an app:

Product Price Color
Sample 1 $5.20 Indigo
Sample 2 $5.20 Ivory
Sample 3 $5.92 Khaki
Sample 4 $5.92 Lavender
Sample 5 $7.04 Silver
Sample 6 $7.04 Snow
Sample 7 $7.55 Tan
Sample 8 $7.55 Teal
Sample 9 $9.59 Thistle
Sample 10 $9.59 Violet

(A larger version of this table is available as a Google Sheets spreadsheet.)

We’ll represent the three columns Product, Price and Color as three fields in the app, with the same names. The Product field should be a text drop-down field, the Price field should be a currency field and the Color field should be a text field:

An app visualizing a spreadsheet table

Making a selection in the Product field should update the information in the Price and Color fields. Those two fields should be associated with formulas using the CHOOSE function in conjunction with the Index property of the Product field.

To transfer the product names from a spreadsheet, select all of them and copy them to the clipboard. Then, select the Product drop-down field and click its Edit as text button to edit the values as text instead of a list. You can then position the cursor in the text box and paste the values from the clipboard:

Editing drop-down values as text

To convert the Price and Color columns to two formulas you can associate with the corresponding fields, paste their data into the Column data field of the app below. You’ll find that CHOOSE formulas are created, one suitable for number fields and one suitable for text fields. Use the formula for number fields for the Price field and the one for text fields for the Color field.

This is the formula that is generated for the Price field:

CHOOSE(Product.Index, 5.20, 5.20, 5.92, 5.92, 7.04, 7.04, 7.55, 7.55, 9.59, 9.59)

Again, this process is explained in detail in our video tutorial.

An app for converting spreadsheet data to drop-down formulas

Run a standalone version of the app.

Continue reading about customizing the appearance of your apps »