Overview

This screen will allow you to create and edit columns on existing databases.

In the Designer, each column on a single database (i.e. primary or secondary one-to-one) can be bound directly to a Single Named Range (SNR), or a named range that points to exactly a single cell.

In primary table, a column can also be tied to a Print Process.

In a secondary one-to-many table, each column is tied to a single column of the Table Named Range (TNR) , or a named range that points to a two-dimensional range of cells in the workbook.

Creating a Column

In order to store user input during a save action, a named range should be bound to both an input in the user interface, as well as to a column in one of the databases. Note that, so long as the range is defined, you can also store output values (e.g. calculated values) in the database without binding the named range to an input in the user interface.

Named Range

The accessible Single Named Ranges can be found under the Named Range field on this screen. Upon selecting a named range, the system will attempt to suggest some of the target data types and the column name automatically, based on metadata captured from your calculation model.

Print Process

When you are defining a primary table column upon selecting Print Process for Bind Column To options, an additional field will be available: Print Process. A list of the defined Print Processes will be available for selection in this dropdown.

At runtime, if the Print Process selection has a valid print process defined, then the document will be printed and the output contents will be saved to this database column (along with a secondary column that will include the file name).

This input is only available on the Primary Table when Print Process option is selected for Bind Column To.

Data Type

When creating a column, it is important to consider the type of data that you are planning to store. If the value of the named range does not conform to the target data type during a save action, then it will be stored as NULL (if the column is nullable) or the entire action may fail, presenting the end user with an error message.

Note that when a column is bound to a Print Process, Data Type will be automatically set to File.

Boolean

A Boolean value translates to a bit of data on the database, denoting a TRUE or FALSE value. This is most commonly utilized in conjunction with the checkbox control when capturing user input.

Integer

An Integer value denotes a numerical whole number (i.e. zero-precision or decimal points). This is most commonly utilized in conjunction with the textbox control with integer validation. This data type can translate into several different types on the database, dependent on the maximum value defined in the column properties.

As a result, the Maximum Value property on this page may be a rough estimate of the actual maximum value, since the system will determine the data type based on the value entered (i.e. the type will be large enough to fit the defined maximum value but smaller than the subsequent tier).

Note that the maximum value does not constitute or replace the maximum value validation of a control in the system . This means that binding a control to the same named range that your column is bound to will not restrict the user from entering higher values. This type of restriction can only be done through the control's maximum value validation property.

Decimal

A Decimal value denotes a numerical data type with a customizable precision, both prior to and after the decimal point. These values correspond to the float data type in the database. This is most commonly utilized in conjunction with the textbox control with decimal, percentage, or currency validation.

The numerical precision of whole number digits (i.e. the values before the decimal) can be defined with the Maximum Integer Length property. The numerical precision of the decimal digits (i.e. the values after the decimal) can be defined with the Maximum Decimal Length.

Leaving these properties empty will occupy the maximum float precision allowable by SQL.

String

The String data type corresponds to an nvarchar data type in the database. The maximum length can be defined using the Maximum Length property. This is most commonly utilized in conjunction with the textbox control.

Note that the maximum length does not constitute or replace the maximum length validation of a control in the system . This means that binding a control to the same named range that your column is bound to will not restrict the user from entering longer values. This type of restriction can only be done through the control's maximum length validation property.

File

The File data type corresponds to an nvarbinary data type in the database. The maximum file size can be defined using the Maximum File Size property (in kilobytes, KB).

This data type should only utilized in conjunction with a Print Process or a Named Range that is bound to a File Upload control.

Note that the maximum file size does not constitute or replace the maximum file size validation of the file upload control in the system . This means that binding a control to the same named range that your column is bound to will not restrict the user from uploading larger files. This type of restriction can only be done through the control's maximum file size validation property.

DateTime

The DateTime data type corresponds to a datetime data type in the database. This is most commonly used in conjunction with the calendar (datepicker or timepicker) control or the textbox control using date or time validation.

Column Name

This property determines the label this column will take in the application database. Application data saved under this field will correspond to the label entered into the Column Name property. This property is also the header for the selected Named Range, when records are downloaded from the Data page. In other words, this is the name the Named Range will take for application database purposes.

By default, the system will auto-populate the Column Name property with the Named Range label. This field can take up to 50 characters. Any spaces or special characters will be automatically replaced with underscores. The system will give an error if two separate columns are evaluated to be the same (i.e. “M@X” and “M!X” both evaluate to “M_M”).

Description

This is an optional field where notes about the data field can be added.

The text entered into this field will be displayed as a tooltip when you mouse-over the Name column on the Columns grid in the Edit Table page. These notes are only available on this page, and will not be visible anywhere else.

This feature is available on both Primary and Secondary Database Tables. The Description property can take up to 150 characters.

Allow Null Values

The Allow Null Values property configures whether the field on the database is nullable. This indicates if the column value for a record can be NULL or whether it requires a value.

This property is defaulted to TRUE and it is advised to retain a column's nullability, unless there is a high degree of certainty that the value will be calculated or entered appropriately. A non-nullable column with no appropriate value can cause the save action to fail.

One-to-One vs. One-to-Many Table Columns

In a one-to-one database table (e.g. primary table or secondary one-to-one table), each column is bound to a single named range. For each record generated by an end user, each of these columns will represent a single specific value.

In contrast, a database column on a one-to-many table will have multiple values (based on the number of rows) per every record generated by an end user.

For example, a sample application may have a section where user information is stored, as well as a grid where scheduled, or sequenced, information is stored:

As a result, with secondary one-to-many tables, it is important to qualify the types of data based on the entire set of values that will be stored in the data row.

Since the number of rows is defined statically (based on the number of rows in the Table Named Range), there is a high degree of probability that the number of filled-in rows will be variable. As a result, it is always advised that columns defined on one-to-many secondary tables are also nullable.