AX2343

Managing column aliases using Save Type 4

Using Save Type 4, you can create, edit, or delete column aliases by using save-to-database within a spreadsheet, instead of using the Edit Table dialog. This may be a more convenient approach when "rolling over" many aliases from one table to another.

One major advantage of using Save Type 4 is that you can move column aliases directly from one table to another, without needing to delete the alias first. When you edit aliases using the Edit Table dialog, you must delete and then re-create the alias if you want to move it to a different table.

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 which hold the alias properties.
• Row tags in the save-to-database control column, to determine the action to take on the alias (create or delete).

Save-to-database tag summary

Tag Type	Tag Syntax
Primary tag	[SaveStructure2DB;Axiom.Aliases;CustomSaveTag=Name]
Row tags	[Save] [Delete]
Column tags	Alias Description TableName ColumnName

Placing the primary save-to-database tag in the sheet

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 alias properties in the control row

Within the control row for the save-to-database process, specify the columns that define the alias properties. You can place these properties in any column:

Column Tag	Description
Alias	The name of the alias.
Description	The description of the alias.
TableName	The table that contains the source column for the alias.
ColumnName	The source column for the alias.

The column names can be placed to either the right or the left of the SaveStructure2DB tag. All of the columns except Description are required and must be placed somewhere in the save-to-database control row. If the description column is omitted entirely, or if the description is left blank for a particular row, any existing description for the alias will be deleted when the save occurs.

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]	Saves the alias with the properties specified in the spreadsheet. If no alias previously existed with this specified alias name, a new alias is created. If the alias previously existed, it is updated with the properties specified in the spreadsheet. If the alias previously pointed to a different table and/or column, the alias is changed to point to the new table and/or column. You do not have to delete the alias if you want to use it in a different table (as you would if using the Edit Table dialog). 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.Aliases; CustomSaveTag=MySave] then you would place the tag [MySave] in the rows that you wanted to be saved.
[Delete]	Deletes the alias. The delete action is only necessary if the alias is not needed anymore, and you no longer want the alias name to exist in the system. If you have an existing alias that you want to point to a new table and/or column, you do not have to delete it first and then recreate it; only a single [Save] action is necessary to update the alias. If an alias in the spreadsheet is marked with delete, but no matches are found in the database for that alias name, no error occurs during the save.

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 alias properties in the spreadsheet

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

For example, imagine that you have 12 aliases, NYB1-NYB12, which are currently assigned to columns M1-M12 in table Plan2011. You are getting ready to start a new year of planning, so you want to reassign those aliases to columns M1-M12 in table Plan2012. You can:

• Create an Axiom query to bring in the current alias properties. You would set up the query so that the entries in the query's field definition row line up with the appropriate alias properties in the save-to-database control row (or you can use the same row for both purposes).
• Modify the [TableName] column to change Plan2011 to Plan2012. In this example, all other alias properties can remain the same.
• In the save-to-database control column, place a [Save] tag in each row.

When you perform the save-to-database, the aliases will be updated to point to the new table.

TIP: After you use the Axiom query to bring in the current alias names as a starting point, you may want to disable the query to avoid accidentally refreshing the file and overwriting any changes you have made to the alias properties. You could also change the refresh behavior for the query to insert and update (instead of rebuild).

Examples

The following example shows the save-to-database tags and several aliases. When a save is performed, the aliases marked with [Delete] will be deleted, and then all of the aliases marked with [Save] will be saved.

The purpose of this example would be two-fold:

• To move the NYB aliases from the Plan2011 table to the Plan2012 table.
• To delete the aliases that use an underscore in their name, and recreate them with no underscore.

The next example shows how an Axiom query could be used to bring in the current alias names as a starting point. Row 5 can double as both the field definition row for the query, and as the control row for the save-to-database operation (the query will ignore the save tag in row 5).

In this example, the query was configured to bring in all alias names for the Plan2011 table. If we want to move all of these aliases to the Plan2012 table, we can edit the TableName column to say Plan2012, add [Save] tags to the save-to-database control column, and then save. (You could also place the [Save] tag in your in-sheet calc method to automatically populate the column during the Axiom query refresh. However, after the query is run you must delete the tag from the in-sheet calc method, or else Axiom Software will try to save that row.)