AX2649

About key columns

Every table in the database must have at least one key column. Reference tables and document reference tables can have only one key column, whereas data tables can have multiple key columns. When defining a column for a table, specify whether it is a key column.

Key columns define unique records of data in the table. If a table has one key column, each value in that key column must be unique and defines a unique record in the table. If a table has multiple key columns, each combination of values in those key columns defines a unique record in the table.

For example, if you have a reference table DEPT, the key column is DEPT, and that column contains each unique department code. The remaining columns in the DEPT table contain information that describes each unique department code, such as the department name, or the region the department belongs to, or the manager of that department.

Data tables typically have at least two key columns, such as DEPT and ACCT. Each combination of a department and an account defines a unique record in the data table. This enables you to plan for each account at a department level.

The specific key columns vary depending on the level of planning. For example, you might forecast at the division and account level, or perform capital planning at the project and account level. When defining key columns for data tables, consider whether the data in that column must define a unique record of data in the database in relationship with the other key columns in the table.

Your plan may require additional key columns in a data table. For example, you might want to track item-level detail for certain accounts, such as Travel. You could have a Detail sheet in your plan files where each manager lists the specific detailed items that make up the Travel total, and assigns each one a Detail code (such as Detail_01). In this case, the Detail column needs to be a key column, so that each Dept/Acct/Detail combination is a unique record in the database. If Detail is not a key column, the save process would either fail due to a summing error, or the detail data would be collapsed within the Dept/Acct combination. For example:

If Detail is not a key column, the save process would fail because the database can only contain one row of data for the combination of DEPT 100 / ACCT 3000, and Axiom Platform cannot sum the contents of the Detail column.

If Detail is a key column, the save process would be successful, saving one row of data for DEPT 100 / ACCT 3000 / Detail_01, and another row of data for DEPT 100 / ACCT 3000 / Detail_02.

Define key columns

When defining a column in a table, you can designate it as a key column by setting the Key Column property to True. Key columns can only be the following data types: Identity, Integer (all types), String, or Date. Other data types cannot be key columns.

A key column can be assigned a lookup column so that the values in the key column are validated against a list in another table. For example, the key column GL2023.ACCT would be assigned a Lookup Column of ACCT.ACCT, so that the account values in the GL2023 table must match the account values in the ACCT table. Regardless of whether a key column is a validated column, key columns cannot contain blank values.

In the Edit Table dialog, key columns display with a yellow key icon next to the column name. If the key column is also a validated column, the icon is a yellow key and a blue key combined.

Example of table with two validated key columns and one non-validated key column

Descriptions for key values

When defining reference tables, each value in the key column typically has a unique description. For example, if the key column is DEPT.DEPT, each department code in that column has an associated description. In almost all cases, whenever the department codes are presented to users, you want the description to display as well because most users need the descriptions to find the departments they are looking for (or just to reinforce that they have the correct department).

To pair the key column with its descriptions, create another column in the table to hold the descriptions and then enable the Describes Key property for that column. Often this column is named Description as well but it does not have to be. When Axiom Platform displays the key values to users in dialogs or in other built-in contexts such as drilling, the descriptions are automatically included.

You can designate multiple columns as description columns; however, you should consider how this content appears to users when multiple columns are included along with the key value. Too much information may be confusing and difficult to read in certain contexts.

Aggregate duplicate keys during data save

Because each combination of key codes defines a unique record of data in the database, if two rows to be saved have the same key codes, by default Axiom Platform attempts to aggregate the data in these rows when saving to the database.

NOTE: This default aggregation behavior only applies to data tables. If duplicate key codes are found when saving to a reference table or a document reference table, an error always results.

For example, imagine that you have the following rows in a plan file:

When a save is performed, the duplicate keys are aggregated by summing the numeric fields, resulting in the following record of data in the database:

When string columns are used in a data table, do not to create a situation where the string column must be aggregated during a save-to-database. For example:

In this case, the save process returns an error because it cannot sum the contents of the Comment column. If both Comment fields contained the same string. For example, if they both read Software release event, the save would process successfully because it could reconcile the contents of the Comment column.

If aggregation applies, non-key fields are treated as follows:

• Numeric fields are summed.
• Integer fields (all types) are summed, unless the column is a validated column, in which case the values must match).
• String, Date, DateTime, and Boolean fields must match.

Aggregation applies by default when saving data to a data table using Save Type 1 or when using an import utility. In both cases, upi can disable the aggregation, if desired (meaning that the data must not contain duplicate keys or else an error is generated). When using Open Table in Spreadsheet, aggregation is not allowed in any case and duplicate keys result in an error.

Alternate key columns

Tables can have one or more alternate key columns. Alternate key columns are non-key columns that are required to have unique values. Use these columns to uniquely identify each row in the table, even though they are not formally designated as key columns. All tables except document reference tables can have alternate key columns.

Currently, the primary purpose of alternate key columns is when using the alternate key mapping feature of Save Type 1. This feature lets you save data by using an alternate key to look up the actual key of a table. For more information, see Saving data using alternate key mapping. However, you can use the alternate key feature for any column where unique values are required.

When the Axiom Platform documentation uses the term key column, this refers to primary key columns only (columns where Key Column is set to True). You should not assume that a requirement or behavior described in the documentation for key columns also applies to alternate key columns, unless alternate key columns are explicitly described.