Figure 3-4: Column Properties Dialog.
Picking Grid/Columns brings up the Column Properties dialog. From here you can add, remove or modify columns.
Figure 3-4: Column Properties Dialog.
If a column is selected in the list, the properties of that column will appear on the right, and made available for modification. Changes made to column values will affect the selected column when the dialog is closed.
The column list supports Drag and Drop, Copy and Paste, and a Context menu. Dragging allows you to move the column to a new position. You can also move columns using the Arrow buttons in the toolbar.
The Provider entry for Data is used to identify the local type name. For example varchar, is a common Access type name, and int auto_increment in a MySql provider type name. When left blank, the provider name will be chosen based on type, size and flags. An attempt will be made to provide column type parameters from size, precision and scale, however it is not always possible to recognize what parameters must be provided and these should be provided manually. For example using MSSql (6,2) may need to be added to numeric(6,2) manually.
A column that has a Data Type, such as CustomerID of type WSTR in figure 3-3 above, is considered a data type. Columns that have a data type are exchanged with external data.
Figure 3-5: Data Types.
If CustomerID in figure 3-4 were to have its data type changed from WSTR to <None> - No Data Link using the [...] button, it would no longer be a data column. If File/Write Data were then picked, column CustomerID would be ignored. In our example, because CustomerID is the primary key column, Write Data would report that it could not match the primary key, and stop the update.
By definition, a column that has a data type is a data column. To convert to a data column, simply select a data type. To convert a data column to a text column, select <None> as the data type.
Formula columns have a default value that is a formula. Formula defaults can be assigned to either data columns or text columns.
Shorthand: Holding Shift when pressing [OK] when closing from the Data Type dialog will cause all matching column types will be converted similarly.
A data column is any column that has a data type selected. A Data column is given a data type, such string, float, integer, date, or bool. Only data columns will be written out to external databases.
Advanced Column Properties
Figure 3-6: Advanced Column Properties
Most Advanced settings are optional. And are Agent and data specific.
Data Ordinal - Column Matching
Understanding the column ordinal is important if the number, or name of columns in the external database changes.
A column data ordinal is the column number of a table in the database. Much like the row number is a row ordinal.
When writing to an external database using the Write Data command, columns in the spreadsheet are matched with columns in the external data. Columns are identified and matched by column name, by column ordinal, or both.
If column names are present in both the grid and in external data, names are matched. If the column ordinal is identified in the grid, that value is used. If both name and ordinal are assigned, both the name and ordinal must agree. Figure 3-4 identifies "CustomerID" as the data column name, and 3-6 shows one (1) assigned the data column ordinal; thus when matching to a column in the external table, the name of column one must be "CustomerID", otherwise the match will fail.
To reassign data ordinals and use only the column name, select [Reset] for each column and <Default> will appear. Holding Shift when pressing [Reset] will cause <Default> All to be set, which will reset all columns, not just the one selected when the Advanced Column dialog was opened.
If you want a column to have the type features of a data column, but not exchange information with an external database, you can select <None> as the ordinal from the advanced column properties dialog. For example, if you add a column "Verified", select type BOOL, and choose <None> as the ordinal in the Advanced dialog. The Verified column will be a checkbox column, but will not write out to external data. Without choosing <None> as the ordinal, the Verified column would be added to the database with the next Write Data command.
Agents may assign <None> to control columns, such as with Line and Offset columns by the Desktop agent for .txt files. Those columns do not get written into data, but need to be recognized by the agent. The Desktop Tutorial illustrates Line numbers, and the direct use of data ordinals for SDF files.
A column 'ordinal' of zero is reserved for bookmarks. Bookmarks are row identifiers used internally by agents and do not represent actual data.
Editorial comment: In the presence of named columns, the use of column ordinals can appear unnecessary. And in many cases it is. Where all columns are named and the number or order of columns in the database changes you will be forced to manually resolve, or [reset] ordinals. I elected to enforce agreement for safety reasons. I did not want data flask to assume that in cases where the number of columns can change, the situation should go unwarned. For example, if SELECT * returns an additional column, and that new column appears before any columns held in the grid, a name-ordinal conflict will arise. The conflict will be reported and Write Data will no longer work until the conflict is resolved. This behavior is simply a design decision; I did not want to assume that it was safe to ignore the new column. To avoid tracking ordinals and exclusively use column names, [Reset] all columns when you first create the grid.
Column Data Flags
Figure 3-7: Column Data Flags
The meaning of most column data flags is Agent and data specific. Data Flask supports, in syntax, the structures DBTYPE (external) and DBCOLUMNFLAGS (external) from Microsoft OleDb. Most of the flags are directly meaningful only to the OleDb agent, however since OleDb is a Uniform Data Access API, the structures are supported in anticipation of future agents making use of them.
The KEYCOLUMN data flag has special meaning to the File/Reload Data/Reload Update command. Key column(s) can be used to update from the data into the grid with this command. This feature is available regardless of the capability of the database itself. For example, consider a grid linked to a comma delimited text file with Name, Address fields. If you set the keycolumn flag for Name, Address can be updated from the text file using the Reload Update command. If two columns are set as keycolumn the key is composite. Lastname+Firstname would be a composite key.
When ISLONG is selected, column Size indixates a maximum size. It will be used to select a provider type during table creation. Use -1 to indicate the largest size available.
Data
Flask Copyright © 2006 Interscape
Corporation