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 you are defining a column for a table, you must specify whether it is a key column.

Key columns define unique records of data in the table. If a table has one key column, then each value in that key column must be unique and defines a unique record in the table. If a table has multiple key columns, then 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 allows 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 Software cannot sum the contents of the Detail column.

If Detail is a key column, then 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.

Defining 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 master list in another table. For example, the key column GL2019.ACCT would be assigned a Lookup Column of ACCT.ACCT, so that the account values in the GL2019 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, then the icon is a yellow key and a blue key combined.

Example 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, you 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 Software displays the key values to end users in dialogs or in other built-in contexts such as drilling, the descriptions will be automatically included.

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

Aggregating 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, then by default Axiom Software 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, you must take care 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 will return 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 said "Software release event"—then 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, the aggregation can be disabled if desired (meaning that the data must not contain duplicate keys or else an error will result). When using Open Table in Spreadsheet, aggregation is not allowed in any case and duplicate keys will 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. These columns can be used 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 allows you to 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, the alternate key feature can be used for any column where unique values are required.

When the Axiom Software 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 mentioned.