Home >

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

Lookup Column assignment in the Edit Table dialog

The GL2018.DEPT column is now a validated column (also known as a linked column), and the DEPT.DEPT column is a lookup column. The DEPT reference table is also referred to as a lookup table for the GL2018 table.

Lookup column relationship

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.

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.

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.

Validated columns and default values

When you make a column a validated column, you should decide whether to also set the default value of the column to a valid value in the lookup column. If the default value is a valid value for the column, then you have the option to omit the validated column from save processes and/or leave the column blank in save processes, and the column will use the default value automatically.

Alternatively, you can decide to not set a valid default value for the column, which means that the column must always be included in save processes and must always have a defined value. You might do this if you always want the column to have a specified or calculated value for the save process as opposed to using a default value.

For example, imagine that you have a validated column with a lookup to a column that contains category codes. If it is acceptable for this validated column to use a default category if no category is set by the save process, then you would set the default value of the validated column to the desired default value. This default value would likely be a pseudo-code such as 99, meaning no assigned category. On the other hand, if you always want the category to be explicitly selected or calculated for the save process, then you might want to leave the default value at an invalid value, so that the save process will fail if the validated column is missing or blank.

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:

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.

• If a column is assigned a lookup column, then the values in that validated column can never be blank or "null." All lookup columns are key columns, and key columns cannot be blank. Therefore any blank value in the validated column cannot be validated against the lookup column.