Handling tabular data

Many apps created with Calcapp look up values found in data tables and present the results to the user, rather than doing traditional math. This data can include everything from tax brackets to information on the products offered by a company.

Calcapp supports spreadsheet-like data tables using our data editor:

To insert data and bring up the data editor, press the DATA button in the app designer:

The first row must consist of column names.

To transfer data from your spreadsheet, follow these instructions:

1. Switch to your spreadsheet.

2. Select the desired range.

3. Copy the data to the clipboard (press Ctrl+C on PC or ⌘+C on Mac).

4. Switch back to Calcapp Creator and the data editor.

5. Select the cell in the upper left corner.

6. Paste the data from your spreadsheet (press Ctrl+V or ⌘+V on Mac).

When you click Save, one named value per column is created:

The DATA button changes to DATA when a compatible named value is selected, allowing you to edit the data using the data editor.

If you need to rename a column, rename the named value in the app designer instead of renaming the column in the data editor. If you do the latter, the data editor will leave the named value corresponding to the old column in place, and add a new named value with the new column name.

Apps can read and process the data you enter using the data editor. However, they cannot currently modify the data. To change the data, the app author needs to use Calcapp Creator.

Detecting numbers and dates

If you enter dates or numbers in the data editor, it should produce named values whose formulas are number or date arrays, and not text arrays. That enables them to be acted upon by functions and operators that accept numbers and dates.

To that end, the data editor automatically detects numbers and dates. In order for that to work, the language setting must match the data (which can differ from the language of the app).

Set the language by pressing the button:

Detected numbers appear on a blue background and detected dates appear on a pink background.

Select a range and press the button to force Calcapp to interpret the range as text. Press the button to make Calcapp try to automatically detect if the selected range represents numbers or dates.

Using dedicated screens for data

The data editor creates and works with named values that are part of the current screen. For small tables with a few columns, that can work well.

However, if your tables are large or are used from multiple screens, it’s a good idea to move them to dedicated screens, never shown to your users.

Named values, corresponding to the table with sweaters, dresses and shorts above, could be put on a screen named Products, for instance. That would enable you to reference, say, the Color named value by writing Products!ColorProducts!Color in a formula.

To ensure that your users cannot reach these data screens, there are a few techniques you can use:

• If your app contains a list screen, you can create a navigator that is permanently invisible that leads to the data screens.
• If your app only consists of form screens, put the data screens after the last user-visible form screen. Prevent your users from reaching the data screens using the NextScreenAvailable property of the last user-visible screen.

If you have multiple data screens, create a list screen whose navigators lead to these data screens, and make the list screen itself unreachable using the techniques listed above.

Accessing data from formulas

The named values created from the Products table can easily be used from formulas. This section discusses some commonly-used formulas for processing the data.

If you want to try the formulas on this page for yourself, here’s the data as a Google Sheets spreadsheet, ready to be imported into an app.

This formula calculates the average product price:

This formula returns the number of products costing more than $100:

This formula returns the kind of the product costing the most:

This formula returns the color of the product costing $129, “Black”:

To learn how XLOOKUP works, refer to our documentation, which contains many more formula samples.

Using XLOOKUP with multiple criteria

What if we need to use multiple criteria with XLOOKUP, and not just a single one?

Let’s assume that there are three text drop-down fields, named Kind, Size and Color, each containing the values found in the appropriate column in the table.

This formula returns the price of the product matching the selected kind, size and color:

(Kind, Size and Color in the formula refer to the text drop-down fields, whereas names starting with Products! refer to named values that are part of the Products screen.)

To see why this works, refer to the blog post we wrote when introducing this feature. Also, our documentation for XLOOKUP has a similar example.

Continue reading about customizing the appearance of your apps »