Defining Columns
The Columns grid displays a list of the table’s columns. All of the information pertaining to each column’s structure can be viewed and edited. In addition, you can create new and delete existing columns.
Data Entry
Primary Key
For SQL tables, the first grid column indicates whether a table column is part of the table's Primary Key. Primary Keys are a standard feature of SQL database tables. A Primary Key is a key in a relational database that is unique for each record. A relational database table must always have one and only one primary key.
Name
Enter a column name. For tables using the embedded database, you can use up to 32 characters. Valid characters in column names are: 'A...Z', '0...9', '@', '', and '#'. The first character must be alphabetic (A...Z). Invalid characters will be replaced by underscores ().
These specifications vary according to the database type. Refer to the appropriate database driver reference documentation for details.
The column name uniquely identifies the column. Column names must be unique among other columns in the table. The column name is used to reference the table’s column in your application source code. For example:
Entry_Item Customer.Name
Type
Select a data type for the column. The type you select defines what sort of data will be allowed in the column. The list of valid types varies according to the database type. Refer to the appropriate database driver reference documentation for details.
To select a column type, press one of the initial letters (e.g., “i” for “int”). If you prefer, you can click the combo button to see the list of allowable column types.
If you are editing an SQL database table, each native SQL data type maps to a DataFlex data type: one of ASCII, Numeric, Date, Text, Binary, or DateTime. You can review these mappings in the “DataFlex Type” column of Table Editor (see below). By default, SQL tables will model native types supported by the database and driver of the table.
Note that you can configure the Studio to model SQL tables using the DataFlex data type mappings directly, i.e., the table will be modeled as if it were an embedded DataFlex database table. See Configure Studio for more information.
The table below describes each of the valid column data types:
| Type | Description |
|---|---|
| ASCII | Character strings. Columns holding this type of data typically store the names, addresses, descriptions, and so on. |
| Numeric | Decimal numbers. Columns with this data type may contain only the numbers 0 through 9, a decimal separator, and a plus or minus sign. Used for money values, rates, and percentages. |
| Date | Columns with this data type contain date values (month/day/year, etc.). For the Embedded database driver, Date columns are stored using the Julian format. |
| Overlap | Note: Overlap columns are provided to support legacy applications. It is recommended that you do not use overlap-type columns in your database. Overlap columns are defined in terms of other physical columns, which must be contiguous. They do not take up space in the table, as they are "virtual" columns. For Overlap columns, you must specify the start and end position of the column via the column’s Size specification (below). |
| Text | Text columns (often called "memo" columns) are for storing large amounts of text in ASCII format. For the Embedded database driver, the usable size of the column will be two bytes less than the declared length. |
| Binary | Binary columns are used to store variable length sequences of bytes such as graphics images. In the Embedded database, the usable size of a binary column is two bytes less than the declared length. |
| DateTime | SQL database drivers support the DateTime data type. DateTime columns contain date & time values (e.g., 1/15/2007 3:03:10.545). The Embedded database driver does not support the DateTime type. |
The set of valid data types varies according to the table’s database type. Refer to the appropriate database driver reference documentation for details.
Size
The column size determines how much data can be stored in a column.
The allowable range of column sizes is predetermined by the data type selection. For some data types, the size is fixed and cannot be modified; other data types allow a certain range up to a maximum size. The supported range of sizes and data types varies according to the database type. Refer to the appropriate database driver documentation for details.
For Numeric columns that allow a decimal precision, enter two values separated by a decimal separator character, for example "6.2". The first value defines the number of digits stored to the left of the decimal separator for the numeric column. The second value defines the number of digits stored to the right of the decimal separator for the numeric column.
Enter a column size in accordance with the guidelines below. Zero-length columns are permitted, mostly for purposes of compatibility with legacy applications.
| Type | Length |
|---|---|
| ASCII | Embedded database ASCII columns permit a maximum size of up to 255 characters. Enter a size value between 0 and 255. Other database types may permit a different range. |
| Numeric | For embedded database Numeric columns, enter two values separated by a decimal separator character (e.g., "6.2"). The first value must be greater than 0 and less than 15; the second value must be greater than or equal to 0 and less than 9. The number of bytes required to store the column is half the total number of digits specified. Only even digits are allowed. The maximum negative value that can be stored is 1 digit less than the maximum positive value. The decimal separator is defined in the International settings of the Windows control panel. |
| Date | For embedded database Date columns, dates are always stored as three-byte numbers. This will be configured for you automatically. Other database types may permit different entries. |
| Overlap | Note: Overlap columns are provided to support legacy applications. It is recommended that you do not use overlap-type columns in your database. For Overlap columns, press F4 (or click the Prompt button) when specifying the column size to activate the Overlap Boundaries dialog. This dialog is used to enter the start position and end position of the overlap column. |
| Text | Embedded database Text columns permit a maximum size of 16KB. Enter a number between 0 and 16384. If the number entered is not divisible by 16, it will be increased to the next multiple of 16. Other database types may permit different entries. |
| Binary | Embedded database Binary columns permit a maximum size of 16KB. Enter a number between 0 and 16384. If the number entered is not divisible by 16, it will be increased to the next multiple of 16. Other database types may permit different entries. |
| DateTime | For DateTime columns, the column size is automatically set (usually to 23 bytes). This value may vary for different database types. The embedded database type does not support DateTime columns. |
Main Index
When a column appears in more than one index, only one of those indexes is used automatically when browsing data from that column in a data entry view. This index is identified as the column's Main Index.
Table Editor assigns the main index for a column as the first index the column appears in. If you want to assign a different main index, select an index from the combo list. Only indexes that the column participates in are listed.
For more information, see Finding Records.
DataFlex Type
When editing an SQL database table, an extra Table Editor column (DataFlex Type) is displayed showing the DataFlex type mapping associated with the selected SQL data type. Normally, this value is read-only. In certain cases, the SQL data type might potentially map to two or more DataFlex types. In this case, the DataFlex Type column will present a combo list of available mappings. You can select any value from the combo list to change the default mapping.
Other Attributes
A complete list of column attributes for the selected column is shown in the Properties Panel. These are comprised of both editable and read-only attributes. The column attribute list and the allowed attribute values are determined by each database driver type.
Toolbar
Add Column
Click the Add Column button to begin creating a new column at the bottom of the column list.
Keyboard shortcut: (Ctrl+A).
Insert Column
Click the Insert Column button to insert a new column above the currently selected column.
Keyboard shortcut: (Ctrl+I).
Delete Column
Click the Delete Column button to delete the currently selected column from the table's definition.
Keyboard shortcut: (Ctrl+D).