Overview

Input and output from the Excel calculation model can be stored in a series of application database tables. As with all modules of the Designer, the cells corresponding with the desired values must be defined as named ranges within the workbook in order to bind them to databases, whether as columns or secondary one-to-many tables.

The page is utilized to initiate the creation of Primary, Secondary (One-to-One), and Secondary (One-to-Many) tables.

Primary Table

The primary table of your application should host all of the top-level information within your application data record structure. This table will also provide all of the system-generated primary keys for storing data in your application.

System-Generated Fields

Several important system-generated columns will be added to your primary table automatically. These are outlined below.

All subsequent tables for the target application will share keys with your primary table to provide linkages. As a result, since the primary key to this table is {{ RecordId, EditId }}, each additional table will also maintain those keys.

Best Practices

A primary table should not necessarily be your primary storage for all information in the application, unless you are storing a relatively small amount of data. It is important to consider the actual conceptual structure of your data before simply adding all inputs to this single table.

If your data model is large, then you will want to consider appending additional secondary database tables (outlined below), apportioned by some conceptual delineation (e.g. general information vs. user information, policy-level vs. instance-level).

Jim is creating an application to quote insurance for an end-user with up to 15 vehicles. His application captures general data (e.g. name, date of birth), policy-level data (e.g. policy type, policy-level deductibles), and vehicular data (e.g. vehicle model, vehicle make).

Rather than storing all of the data on the primary table, Jim is going to store the general data on the primary table, the policy-level data on a one-to-one secondary table, and the vehicle schedule on a one-to-many secondary table.

More information on creating secondary tables is detailed in the subsequent sections.

Secondary Tables

To better apportion and consolidate data, you can generate secondary tables in two different formats: one-to-one and one-to-many.

One-to-One (OtO) Secondary Tables

A one-to-one secondary table acts as an extension to the primary table. For each record in the primary table, there is only a single record in this secondary table. As a result, the data follows a flat structure and each column is bound to a Single Named Range (SNR) (i.e. a named range that is one cell in width and one cell in height). In turn, the configuration, definition, and modification of one-to-one secondary tables is effectively identical to primary tables.

The most noticeable difference is that there are fewer system-generated columns on these tables. The primary table will hold the majority of system-generated information and the secondary one-to-one table will simply link to it using the required keys (the aforementioned {{ RecordId, EditId }} composite key).

As denoted by its name, for each record in the primary table, there will be a single record in these types of secondary tables.

One-to-Many (OtM) Secondary Tables

A one-to-many secondary table acts as a means to stored scheduled information. Some examples:

• A census application may stores sets of information regarding multiple individuals.
• A property insurance quoting tool may store sets of information regarding individual locations.
• An order form may store sets of information regarding a series of ordered items.

In these types of scenarios, your data may be formatted as a two-dimensional table within your Excel model: this is known as a Table Named Range (TNR). This is the type of named range that is required for secondary one-to-many tables.

Typically, this type of range is associated with a grid in the user interface. Unlike a secondary one-to-one table, one-to-many tables will have their columns configured by the columns within the table named range. As a result, the number of rows per record will vary depending on the number of rows in the table named range.

For example, take the following table:

A secondary one-to-many table would generate three columns based on the data above: Name, Age, and State. The number of rows would be dependent on the number of rows in the named range. In this case, there are four maximum users catalogued by this range, and thus there will be four rows in this table for every one row in the primary table.

Each record links to the primary table using the normal key construct: {{ RecordId, EditId }}. In addition, these records will include a SequenceId key that maintains the integrity of the row order.

Creating Data Tables

In order to save data, the first table generated must be the primary table, since the key constraints are defined within that model. The option to create secondary tables will be locked until the primary table is generated. You can initiate this process by clicking on the Add New button under the Primary Table container or in the Database Designer Context Menu.

Once the primary table has been created, options to generate secondary tables will be exposed. To initiate the creation of a secondary table, simply click on the Add New button in the Secondary Tables container or in the Database Designer Context Menu.

Deactivating Data Tables

Data tables can be made inactive using the Deactivate button, which is shown when you hover over the target table name. Only secondary tables can be made inactive. When a table is made inactive after publishing, no data will be saved to this table and its data will be not be displayed in the Data page. A table can be reactivated using the Activate button, which is shown when you hover over the target table name. A table can only be activated if the changes have not yet been published. After you publish the application following table deactivation, it will not be possible to reactivate that table.

Deleting Data Tables

Data tables can be deleted by pressing the Delete button, which becomes visible when you mouse-over the target table name. Only secondary tables can be deleted. The delete operation is permanent and its data will be not be displayed on the Data page. The deletion will take place when the current transaction is published. Once the application is published, it will not be possible to recover deleted data. Please exercise caution when deleting tables that contain important information.

Data Extraction from Control Panel

When exporting the data for a given application, the output Excel file will contain worksheets based on the table structure and relations of your web application. The primary table will be included in the first worksheet and all subsequent related tables (including the Details table, which houses internal status and assignment information) will be included in subsequent sheets.

Linking secondary tables to the primary table requires matching the table's keys together, as described in the corresponding sections above.

Application Removal from Control Panel

When you remove a designer application, any data that has been stored in association with the application will be deleted permanently and cannot be recovered.