Drop-downs and data
Drop-down fields enable users to select from a number of predetermined options:
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:
(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:
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:
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,
Again, this process is explained in detail in our video tutorial.
An app for converting spreadsheet data to drop-down formulas
Continue reading about customizing the appearance of your apps »