AX2109

Saving data using alternate key mapping

When using Save Type 1, you can use special syntax to look up a key value based on an alternate key value in the same table. This can be used to save data to a "child" table, where you want to populate a column in the child table with the value from the key column in the "parent" table.

The primary use case for this functionality is when inserting rows in a table that has a lookup to an identity table. Many identity tables have a column that serves as an alternate key for the table. This alternate key is typically generated using meaningful values (such as dimension codes and dates), and serves as the primary identifier of the record to end users instead of the auto-generated identity value. When inserting records into a table that has a lookup to the identity table, it may be more convenient to supply the alternate key and let Axiom Software look up the corresponding identity value.

Table prerequisites

The following table prerequisites are required for this special save-to-database syntax to work:

  • The parent table is a reference table that contains a primary key and an alternate key. The primary key is typically an identity column and the alternate key is typically a string column, but other column types are also valid.

  • The alternate key column in the parent table must have Alternate Key set to True in the column properties. This officially flags the column as an alternate key for the table and requires the column to contain unique values. Note that after a column has been designated as an alternate key, the System.IndexMaintenance job must be run in Scheduler to create the unique index on the column. Until this job is run, no data can be saved to the table.

  • The child table must have a column with the correct data type to accept the values from the primary key of the parent table. Typically this column is a validated column with a lookup to the primary key of the parent table, but the lookup relationship is not required. This column can be a key column or a regular column.

Setting up the save-to-database for key mapping

The save-to-database to the child table must be set up as follows:

  • Aggregation must be disabled within the Save2DB tag. Assuming the minimum parameters, this looks as follows:

    [Save2DB;ChildTableName;;;;;False]

    TIP: Once you have created the base Save2DB tag, you can double-click the tag to bring up the tag editor and disable Aggregate on Save. This will create the tag with the appropriate number of semicolon delimiters.

  • In the save-to-database control row, instead of entering the name of the target column in the child table, enter the following syntax:

    [Column=ColumnName;AlternateKey=Table.Column]

    Where Column is the name of the target column in the child table, and AlternateKey is the name of the alternate key column in the parent table. The alternate key column must be specified using Table.Column syntax or an alias name.

    The save-to-database column that contains this special syntax is known as the mapped column. When populating the save-to-database rows, the mapped column should contain valid values as found in the alternate key column of the parent table.

When the save occurs and data is inserted or updated in the child table, Axiom Software takes the value in the mapped column and finds that value in the alternate key column of the parent table. Axiom Software then looks up the corresponding primary key value for that alternate key value, and populates the target column of the child table with the primary key value. You can also delete records when using alternate key mapping, even if the mapped column is a key column (in which case the mapped value will be used to identify the record to be deleted).

NOTES:  

  • Only one mapped column can be used in the save-to-database control row. It is not possible to map multiple columns in the save set.
  • If the user performing the save has a write filter defined for the child table, all columns used in that filter must be included in the save-to-database control row.

Example

In this example, the parent table has an identity key column named ProjectID, with an alternate key column named ProjectCode (a string column).

In the child table, the key column ProjectID has a lookup to Parent.ProjectID. The mapping process could also be used if ProjectID was not a key column.

In the spreadsheet, the save to database tags would be set up as follows:

When the save occurs, Axiom Software takes the alternate key value PRJ_D21000_072016 and finds it in the Parent.ProjectCode column. Axiom Software then looks up the corresponding key value in the Parent.ProjectID column, which in this case is 3. Axiom Software takes the value 3 and populates the Child.Project ID column with that value when creating the new record.