AX2345

Managing tables using Save Type 4

Using Save Type 4, you can create, edit, and delete tables by using save-to-database within a spreadsheet, rather than using the table administration tools. This may be a more convenient approach if you have many tables to modify, or if you need to update the tables frequently.

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 table properties.
• Row tags in the save-to-database control column, to flag rows to be saved.

Save-to-database tag summary

Tag Type	Tag Syntax
Primary tag	[SaveStructure2DB;Axiom.Tables;CustomSaveTag=Name]
Row tags	[Save] [Delete]
Column tags	Any supported table property exposed from Axiom.Tables. See Defining the table properties in the control row.

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

To define the location of the save-to-database control row and control column, place the following tag in any cell in the sheet, within the first 500 rows:

The row containing SaveStructure2DB becomes the control row, and the column containing SaveStructure2DB becomes the control column.

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

Defining the table properties in the control row

Within the control row for the save-to-database process, specify the columns that define the table properties. The column tags can be placed in any column, to either the right or the left of the SaveStructure2DB tag. See Axiom.Tables for a full list of table properties.

At minimum, you must include the following properties on the save row to create a new table. All other table properties will use the default value if omitted from the save.

• TableName
• FolderPath (will be created if it does not currently exist)
• TableClassification (Data or Reference)

NOTE: It is not possible to create new picklist tables or new KPI tables using Save Type 4.

When updating an existing table, you must include the TableID and the TableName in the save to identify the table to update. You can modify any of the following table properties:

• Description
• TableClassification (table must be eligible for conversion to the target table classification)
• TableType (table must meet the requirements of the target table type)
• FolderPath
• IndexScheme (can change between Default and Hybrid, but not to or from LargeTable)
• IsDataAudited
• IsDataReadOnly
• CurrentPeriod

It is also possible to configure data conversion for a table using Save Type 4, using the following table properties. Saving valid data conversion properties will cause data conversion to become enabled for the table.

• RateTableID
• RateTableFromColumnDefinitionID
• RateTableToColumnDefinitionID
• RateTableTypeColumnDefinitionID
• RateTableScenarioColumnDefinitionID
• DefaultCurrency (meaning Conversion Value From)
• DefaultCurrencyColumnID (meaning Conversion Value Use Lookup)
• ConversionType
• ConversionTypeColumnID
• Scenario

IMPORTANT: To complete the data conversion configuration, you must also use Save Type 4 to the Axiom.ColumnSequences table, to set the RateSequenceID on the applicable sequence in the table you are configuring. The RateSequenceID for the table sequence must be set to the sequence ID of the sequence on the data conversion table (the table specified as the RateTableID). This maps the two sequences together for data conversion. For example, if the sequence ID for the sequence on the data conversion table is 130, then the RateSequenceID for the sequence on the table must be set to 130.

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 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 table 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.Tables; CustomSaveTag=TableSave] then you would place the tag [TableSave] in the rows that you wanted to be saved.
[Delete]	Deletes the table, based on the specified TableID (or TableName if no TableID is specified). When processing the save interactively in the Desktop Client, a warning message occurs before any tables are deleted, to verify that you want to delete the tables. The delete action cannot be undone. IMPORTANT: If the save process is executed in an Axiom form or using Scheduler, no warning message displays and the tables flagged for deletion are simply deleted. Use caution before exposing this functionality for use in Axiom forms or Scheduler.

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.

Populating the table properties in the spreadsheet

In the property columns, enter the table name and the other relevant values for each table that you want to create or update. You can manually type the table properties within the spreadsheet, or you can use an Axiom query to Axiom.Tables to populate the spreadsheet with the properties for existing tables. Once you have the current list 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 "move" a table from one system to another. One way to do this is to:

• Create an Axiom query in the original system to return the table properties and column properties for the table.
• Export the file out of that system and then import it to the new system.
• Within the new system, set up Save Type 4 to save the new table and then save the new columns.
• Make any additional edits to the table manually as needed, such as creating aliases, sequences, and calculated fields.

Considerations when creating new tables using Save Type 4

When using Save Type 4 to create new tables, use one SaveStructure2DB tag to Axiom.Tables to create the table, and another SaveStructure2DB tag to Axiom.Columns to create the individual columns in the table. A table must have at least one key column to be a valid table.

As long as the two save blocks are in the correct order—so that the table is created before attempting to create the columns—you can create the entire sequence within a single save-to-database process.