Most apps created with Calcapp don’t just consist of calculations, they also need data to work. That data may be parts of your product catalog or your price list. More often than not, all this data is maintained in spreadsheets and needs to make its way to Calcapp Creator, our app designer.
While Calcapp has always been able to handle data, through use of its IF formula function, our new release makes making data part of your apps much easier, at least if you want to use a simple mapping from a drop-down field to your data.
First, the values of drop-down fields can now be edited as text, enabling you to paste data (say, your product names) directly from your spreadsheet. Second, the new CHOOSE formula function makes it much easier to make data part of your formulas. Third, a new app we have written (with Calcapp, of course) converts spreadsheet data directly to formulas which use the new CHOOSE function.
The video above includes closed captioning explaining the various steps. A more detailed discussion appears below. Run the conversion app here.
Inspecting the spreadsheet (00:02)
In this video, we create an app from a simple product catalog stored in a Google Sheets spreadsheet (click the link to open it if you want to follow along). Importing data from Microsoft Excel, Apple Numbers, LibreOffice or any other popular spreadsheet is very similar.
There are three columns: Product, Price and Color.
Creating the calculation panel (00:08)
Start Calcapp Creator and create a new app. We’ll create three form fields, each one holding a value for a different column. The color field is a text field and the price field is a currency field. The product field should be a drop-down field, enabling users to select the product from a menu.
Copying the product names from the spreadsheet (00:51)
Switch back to the spreadsheet and select all product names and copy them to the clipboard by pressing Control+C (PC) or Command+C (Mac).
Pasting the product names into the app (00:59)
Back in Calcapp Creator, press Edit as text to edit the drop-down values of the product field as text. That enables you to easily paste the product names from the spreadsheet by pressing Control+V (PC) or Command+V (Mac).
Select the first product name in the drop-down field in the app designer to make that particular product pre-selected when the app starts.
Copying the prices from the spreadsheet (01:11)
Select all prices in the spreadsheet and copy them to the clipboard by pressing Control+C (PC) or Command+C (Mac).
Converting the prices to a formula using an app (01:23)
To convert the price data to a CHOOSE formula, use our app. Paste the data into the Column data field (press Control+V on a PC or Command+V on a Mac).
To ensure that the generated CHOOSE formulas use the right drop-down field, enter its name under Field name. In this case, type “Product”.
You will find that two CHOOSE formulas are generated for you, one suitable for use with number fields and one for use with text fields. The CHOOSE formula for text fields use all values you have pasted as-is, enclosed in quotation marks.
The formula for number fields tries to interpret your values as
numbers. If a value can’t be interpreted as a number, the formula
BLANK() is
used instead of the value (this function simply returns a blank
value).
As prices are numbers, you need to copy the formula generated for number fields to the clipboard.
Pasting the prices into the app (01:45)
Switch back to Calcapp Creator, make sure that the Price field is selected and paste the formula you copied into the formula field.
Copying the colors from the spreadsheet (01:58)
Select all colors in the spreadsheet and copy them to the clipboard.
Converting the colors to a formula using the app (02:07)
Go back to the conversion app and remove the old data. (Press the Column data field and press Control+A on a PC or Command+A on a Mac followed by pressing the Backspace key.)
Paste the new data. Colors are text strings, meaning that you need to copy the CHOOSE formula for text fields.
Pasting the colors into the app (02:18)
Switch back to Calcapp Creator, make sure that the Color field is selected and paste the formula you copied into the formula field.
Previewing the app (02:25)
That’s all there is to it! Press the play button to bring up a preview of the app. Try selecting a few different products. You’ll notice that the price and color shown in the app match the spreadsheet.
Do you have any requests for other topics we should cover in a future video tutorial? Let us know!