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 GL2019 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 GL2019.DEPT.

Lookup Column assignment in the Edit Table dialog

The GL2019.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 GL2019 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.

Defining 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, then the assignment will only be allowed if the existing data can be validated against the lookup column.

Once 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, then the validated column must be an Integer column. If the columns are string columns, then 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, though often it is a good idea to use the same name to avoid confusion and to 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, then 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 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. You want to make sure that only valid department numbers are used in this column, so you assign it a lookup column of DEPT.DEPT. If you do this, keep in mind that 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 will not work as expected when mapping, because when summing the three records together the query will choose 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 way around this 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 will enforce the use of the values for the "parent" account. This is the same syntax that is 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.