November 1, 2021 update: Calcapp now supports the XLOOKUP function (blog post), along with an app that generates XLOOKUP formulas from spreadsheet tables. Generating CHOOSE formulas is sometimes still the better choice, though, but XLOOKUP provides more flexibility. Refer to the links for details.
Original text: Calcapp has had robust support for calculations for a long time now. With support for almost 300 formula functions (most of them Excel-compatible) and with features such as cross-references and calculated properties, most calculation needs are met.
The situation for data is less rosy. Many spreadsheets consist of large tables holding data that formulas operate on, often using formula functions like VLOOKUP, HLOOKUP, MATCH and INDEX. Converting these spreadsheets to apps with Calcapp has historically been an exercise in frustration.
Our latest release makes it easy to convert table data to formulas for use with drop-down fields. We have built an app for generating these formulas automatically (just paste data from your spreadsheets) and have also introduced a new formula function which represents data compactly and efficiently.
If you need to present values based on what a user selects from a drop-down field, these new features enable you to quickly build apps that previously would have been very time-consuming to create. If you have more sophisticated table lookup needs, these new features may not be sufficient.
Let’s dive in and see what this all means. You can also watch a video tutorial demonstrating the import process.
Converting a spreadsheet table for use with drop-down fields
Consider the following small table:
| 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 |
Let’s say that you want to add one drop-down field containing all the product names (named Product) and two additional fields which should display the price of the selected product and its color:
The Product drop-down field needs to be populated with the product names. A new feature enables you to edit the values as text, which in turn makes it easy to copy the values straight from the spreadsheet and paste them into Calcapp Creator:
Creating the app with nested IF functions
Creating this simple app with the tools previously offered by Calcapp is surprisingly hard. You’re limited to the IF formula function, which requires you to use deep nesting to test for multiple values. To select a price based on the selected product, you would need to write this cumbersome formula:
Manually converting a spreadsheet column to nested IF functions is hard, tedious, error-prone work. Also, every time you need to update the data, you need to essentially start from scratch.
Creating the app with the new CHOOSE function
Our new release introduces a new formula function, CHOOSE, which selects a value based on its first number parameter. Also, drop-down fields now support a property named Index which returns the numeric position of the selected value (1 for the first value, 2 for the second value, etc).
The following formula, using CHOOSE and the Index property of the Product drop-down field, fully replaces the cumbersome IF-based formula above:
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.
Automatically converting spreadsheet columns to CHOOSE formulas
The best formulas are the ones you don’t have to write. While writing and maintaining a CHOOSE-based formula is certainly better than writing and maintaining an IF-based formula, what if we could simply copy-and-paste our data straight from our spreadsheet?
We’ve written an app that does just this (using Calcapp, of course). There’s even a video tutorial that shows you exactly how to use it. You just paste your data, one column at a time, into the app to have it generate the CHOOSE-based formulas automatically:
First, you enter the name of the drop-down field in the Field name field (Product in this case). Then, you copy the column data from your spreadsheet and paste it into the Column data field.
The app generates two CHOOSE formulas for you, one for number fields and one for text fields. Here’s the generated formula for number fields:
The app automatically converts your table data to numbers (removing the leading dollar sign in this case).
Here’s the generated formula for text fields:
As you can see, it uses the column data verbatim and encloses every value in quotation marks, which is perfect for a text field.
Simply repeat this process for every column of your table. We suggest that you continue to maintain the data in your spreadsheet — it’s convenient to be able to see the table all at once instead of just looking at single columns. When you update the data, simply repeat this process to transfer the data to Calcapp Creator.
Here’s an embedded version of the table converter app, which you can also run as a stand-alone app:
Further reading
This feature is made up of a number of smaller features, which work in tandem to realize the new functionality. These features are described in detail in the following blog posts: