AX2650

About lookup columns

Certain columns in tables need to be restricted to a specific set of valid values. These columns are known as validated columns, because before any data can be saved to the column, the data is first validated against a designated lookup column. The lookup column defines the set of allowed values for the validated column.

For example, if the table GL2023 has a column DEPT, then you need to restrict the values in that column to the set of valid departments for your organization. This list of valid departments is defined in the separate DEPT reference table, in the key column (for example: DEPT.DEPT). You can enforce this validation by assigning DEPT.DEPT as the lookup column for GL2023.DEPT.

Lookup Column assignment in the Edit Table dialog

The GL2023.DEPT column is now a validated column, and the DEPT.DEPT column is a lookup column. The DEPT reference table is also referred to as a lookup table for the GL2023 table.

Lookup column relationship

Both key and non-key columns can be validated columns. For example, you could have a reference table DEPT, with a non-key column named DEPTMGR that contains the manager name of each department. If you wanted to restrict that column to a set of valid manager names, you could create another reference table in the database named MGR, and then assign MGR.MGR as the lookup column for DEPT.DEPTMGR.

Define validated columns

When defining a column in a table, you can make it a validated column by assigning a Table.Column name in the Lookup Column field. You can assign a lookup column when creating or editing a column. If the table already contains data when you make the lookup column assignment, the assignment is only allowed if the existing data can be validated against the lookup column.

After a lookup relationship has been created, the validated column and the lookup column become interdependent. You cannot save data to the validated column unless it matches a valid value in the lookup column. Also, you cannot delete a value from the lookup column if it is used in the validated column.

Note the following about lookup columns and validated columns:

  • Only key columns of reference tables can be lookup columns.

  • A column can be the assigned lookup column for any number of validated columns. For example, multiple DEPT columns in multiple tables can all use DEPT.DEPT as a lookup column.

  • A validated column must have the same data type as its assigned lookup column. There is one exception—if the lookup column is an Identity column, the validated column must be an Integer column. If the columns are string columns, they must also have the same string length and Unicode status (enabled or disabled).

  • Lookup columns and validated columns are not required to have the same column name, although frequently it is a good practice to use the same name to avoid confusion and reinforce the relationship between the columns.

In the Edit Table dialog, validated columns display with a blue key icon next to the column name. If the validated column is also a key column, the icon is a yellow key and a blue key combined. The lookup column for the validated column displays in a tooltip when you hover your cursor over the column name.

Example of table with validated columns

Self-referencing lookup columns

You can set up self-referencing lookup columns within a reference table. This means that the validated column and the lookup column are within the same table. For example, the column DEPT.DeptMap can use DEPT.DEPT as a lookup column, both within the DEPT table.

This can be useful when setting up mapping for plan files. You may be mapping certain departments into a parent department for planning purposes, and you manage those mappings in the DeptMap column. Ensure that you use only valid department numbers in this column, so you assign it a lookup column of DEPT.DEPT. If you do this, you cannot have blank values or entries like Not Mapped in the DeptMap column—all rows must be completed and all entries must be valid department numbers.

This approach also allows you to force Axiom to use values from the parent record when mapping. For example, consider the following setup for the ACCT table:

ACCT   Description   BgtCombineDuringQuery   BgtSectionInFile   CMAssign
1000   Main Rev   1000   Fixed   Fixed CM
1100   Other Rev 1   1000   Fixed    
1200   Other Rev 2   1000   Variable   Variable CM

Imagine that you are mapping accounts 1100 and 1200 into account 1000 for planning purposes. Under normal circumstances, you might have an Axiom query data range filter of ACCT.CMAssign='Fixed' to bring accounts into that section, and the calc method assignment for the query would be Acct.CMAssign.

However, this approach does not work as expected when mapping because, when summing the three records together, the query chooses the string values from BgtSectionInFile and CMAssign seemingly at random. It might select Variable as the section instead of Fixed, and it might select the Variable CM or the blank cell from the CMAssign column (the latter case meaning the default calc method would be used). This is not the desired result—what you want is a way to force it to use the values from the parent account, which in this case is account 1000.

The workaround is to configure BgtCombineDuringQuery to use ACCT.ACCT as a lookup column. Then, in the Axiom query setup, you can use the multiple-level column syntax, which enforces the use of the values for the parent account. This is the same syntax used when there are actually multiple tables involved in the lookup chain (see Using multiple levels of column lookups). Effectively, this causes the account table to be joined back in after the records are summed, and then use the values from the BgtCombineDuringQuery column to determine the account.

In the case of the data filter, you would specify ACCT.BgtCombineDuringQuery.BgtSectionInFile='Fixed', and in the case of the calc method assignments, you would specify ACCT.BgtCombineDuringQuery.CMAssign. You would also use this syntax as appropriate in the field definition for the query, for example to bring in the appropriate account description: ACCT.BgtCombineDuringQuery.Description.

Design considerations for validated columns

If the data in a table is zeroed as part of a Save Type 1 process (using the Zero on save feature) or when using Zero/Delete Table Data, the values in validated columns are left as is, whether the validated column is a key or a non-key.

Alternatives to lookup columns

Use a column constraint as an alternative to specifying a lookup column. When you define a column constraint, you specify an expression that directly restricts the allowed values in a column. For example, an expression of in ('Active','Inactive') restricts the allowed values in the column to those two string values. For more information, see Using column constraints.

A column constraint can be a useful alternative when creating a separate lookup table is more overhead than necessary. However, you should use a lookup column instead of a column constraint if any of the following apply:

  • Multiple columns need to be restricted by the same set of values.
  • You need to define additional description or grouping columns for the values.
  • You need to be able to update the values relatively frequently.
  • You need to be able to join multiple tables in a report based on the shared values.
See also