AX1032

Managing columns using Save Type 4

Using Save Type 4, you can create, edit, or delete columns in a table by using save-to-database within a spreadsheet, instead of using the Edit Table dialog. This may be a more convenient approach when you want to impact many columns at one time.

Save Type 4 depends on the placement of save-to-database tags within the sheet. There are three components:

• The primary SaveStructure2DB tag, which defines the locations of the save-to-database control row and control column, and specifies the desired operation.
• Column tags in the save-to-database control row, to specify the columns to update.
• Row tags in the save-to-database control column, to determine the action to take on the column.

Save-to-database tag summary

Tag Type	Tag Syntax
Primary tag	[SaveStructure2DB;Axiom.Columns;CustomSaveTag=Name]
Row tags	[Save] [Delete]
Column tags	Any available column property exposed from Axiom.Columns. See Defining the column properties in the save-to-database control row for a list of supported column properties.

Defining the save-to-database control row and control column

To define the save-to-database process, place the following tag in any cell in the sheet, within the first 500 rows:

The row containing this tag becomes the control row for the process, and the column containing this tag becomes the control column for the process.

You can also optionally use the custom save tag parameter. For example:

Defining the column properties in the save-to-database control row

Within the control row for the save-to-database process, specify the columns that define the column properties by entering the reserved column names from Axiom.Columns. The column tags can be placed to either the right or the left of the SaveStructure2DB tag.

The following properties can be used:

Column Tag	Description
TableName	The name of the table.
ColumnName	The name of the column.
ColumnId	The database ID of the column. NOTE: This item should be left blank when creating a new column.
Description	The optional description of the column.
DataType	The data type of the column. See Column properties for a list of available column data types.
StringDataMaxLength	The maximum allowed length of the string, if the column is a String column. Defaults to 20 when creating a new column if not specified.
IsUnicode	Whether the column is Unicode-compliant. Defaults to False when creating a new column if not specified.
NumericType	The numeric type of the column, for display formatting purposes. Only applies to Integer (all types) and Numeric columns. Defaults to Number when creating a new column if not specified.
IsKeyColumn	Whether the column is a key column (True/False). Defaults to False when creating a new column if not specified.
IsUniqueKeyColumn	Whether the column is an alternate key column (True/False). Defaults to False when creating a new column if not specified.
IsDescription	Whether the column is a description column for the key column (True/False). Defaults to False when creating a new column if not specified.
ParentColumnId	The database ID of the assigned lookup column for the column. Leave blank if no lookup column applies.
IsDataReadOnly	Whether the column is read-only (True/False). Defaults to False when creating a new column if not specified.
IsFixedStructure	Whether the column properties can be modified using Open Table in Spreadsheet (True/False - True means it cannot). Defaults to False when creating a new column if not specified.
SortOrder	The order of the column in relation to other columns. The first column is 0, the next column is 1, and so on. Use caution when creating a new column or modifying the sort order for an existing column. Ideally, every column should be assigned a unique number for the sort order. If multiple columns are assigned the same number, then the database will determine the order of those columns. By default if sort order is omitted on a new column, it will be assigned a sort order of 0. New columns should be assigned to one number higher than the last number used in the table (assuming you want the column to be placed at the end of the sort order). If you need to reorder the sort order, then you must manually reassign the number of every impacted column; the Save Type 4 process does not have a way of automatically updating these numbers. For example, if you have columns with a sort order of 0 to 4, and you want to create a new column to place at a sort order of 3, then you will also have to update the existing column 3 to 4, and the existing column 4 to 5.
IsFilterColumn	Whether the column displays in the Filter Wizard (True/False). Defaults to True when creating a new column if not specified.
HierarchyDisplayName	Optional alternative name to display in hierarchy views.
HyperlinkLabel	Flags the column as containing hyperlink data (web URLs or Axiom file paths). Label is used when auto-generating the hyperlink via Axiom query. Only applies to string columns. Leave blank if column does not contain hyperlink data.
DefaultValue	Defines the default value for the column. Note that blank is a valid value and means that you want the default value to be null. If you are creating a new column and you want it to use the system default for the applicable data type instead, then you should not include the DefaultValue property in the save. If you want to change this value to a string literal (such as 'N/A') for a String column, the leading quotation mark must be escaped by another quotation mark. For example, the actual value in the cell must be ''n/a'. This causes Excel to treat the leading quotation mark as part of the actual value; otherwise it is treated as a text marker and stripped from the value (this will cause an error when saving).

TableName, ColumnName, and Data Type are required; all other fields can be included or not as desired.

The control row must be dedicated to containing only valid column names for the Save Type 4 operation to the target table. Any invalid entries in the control row will cause an error when saving.

Flagging the rows to be saved or deleted

Within the control column for the save-to-database process, mark each row that you want to be processed with the appropriate row tag:

Row Tag	Description
[Save]	Creates or updates the column with the properties specified in the spreadsheet. NOTE: If you have defined a custom save tag in the SaveStructure2DB tag, then you must mark the rows with that tag instead of the default tag. For example, if your primary tag is [SaveStructure2DB; Axiom.Columns; CustomSaveTag=MySave] then you would place the tag [MySave] in the rows that you wanted to be saved.
[Delete]	Deletes the column, based on the specified ColumnID (or ColumnName if no ColumnID is specified).

Only rows that are marked with a valid tag are processed; all other rows are ignored, even if there is content in the property columns. If a row contains a valid tag but no content exists in the property columns, a save error will occur.

When the save-to-database occurs, delete actions are processed first, followed by save actions.

NOTE: The row tag can be placed within a formula if desired. For example, you might want to use a formula to determine whether a particular row should be saved or deleted.

Populating the column properties in the spreadsheet

You can manually type the column properties within the spreadsheet, or you can use an Axiom query to populate the spreadsheet with the columns that are currently defined in the database. Once you have the current list of columns as a starting point, you can adjust them as necessary, and then save the changes back to the database.

For example, imagine that you want to update columns for the Dept table. You can:

• Create an Axiom query to bring in the current columns for that table, by querying Axiom.Columns and filtering the results to just that table.
• Set up the save-to-database tags so that the fields to be saved line up with the information brought in by the Axiom query.
• If you want to edit the properties of existing columns, you can change the properties and then place a [Save] tag in the save-to-database control column. If you want to delete an existing column, you would use a [Delete] tag.
• If you want to create a new column for that table, you can enter the column properties (leaving ColumnID blank), then place a [Save] tag in the save-to-database control column.

When you perform the save-to-database, the columns will be created, updated, or deleted as appropriate.