AX2076

Using multiple levels of column lookups

Axiom Software supports multiple levels of column lookups in tables. These multi-level relationships can be used when querying data in Axiom Software—from returning data via an Axiom query, to displaying a list of items using GetDataElement or a RefreshVariables data source.

For example, the column GLData.DEPT can link to DEPT.DEPT, and therefore you can include any column in the DEPT table when querying the GLData table, such as DEPT.Region or DEPT.VP. This is the first level of column lookup that is used by every system. But the DEPT table itself can have additional lookups, such as DEPT.VP linking to VP.VP. In this case, you can also include any column in the VP table when querying the GLData table, such as VP.FullName or VP.Email.

When including a column from a reference table that the data table directly links to (single level of lookup), then you can use normal Table.Column syntax, such as DEPT.VP. However, when including a column from a reference table that is linked by multiple levels of lookups, you must indicate the full "path" from the first level of lookup all the way to the target column. For example, to include VP.Email in this Axiom query, you would use the following:

The following diagram illustrates the linking between these tables, and the ultimate column to be returned (in yellow):

The easiest way to ensure you are using the correct syntax in this situation is to use the Axiom wizards and tools to select the column. For example, use the column choosers in the Sheet Assistant to complete Axiom query settings. To complete settings in the sheet itself, such as field definition entries, drag and drop the column from the Columns / Aliases section of the Sheet Assistant to the desired cell in the sheet.

If you want to manually type in the column, keep in mind that the syntax can get fairly complicated if there are multiple levels of lookups, or if there are special issues such as:

• Multiple columns in a table have a lookup relationship with the same reference table. For example, Encounter.PrimaryPhysician and Encounter.SecondaryPhysician both look up to Physician.Physician.

  In this case, you must start the syntax with the name of the column in the original table, instead of starting it with the lookup reference table. You can't just say Physician.FirstName, because there are two possible paths to the Physician lookup column. Instead you would have to specify one or the other, such as PrimaryPhysician.FirstName (or Encounter.PrimaryPhysician.FirstName, if Encounter is not the primary table.

• The validated column does not use the same name as the lookup column. In this case, if there are multiple levels of lookups then you must use the name of the validated column in place of the lookup column. (If there is only one level of lookup, then you can use direct Table.Column syntax for the column in the lookup table.)

The following is an example of several levels of lookups:

The following diagram illustrates the linking between these tables, and the ultimate column to be returned (in yellow). In this example, the data table being queried is the ChargeDetail table.

Note the following about this example:

• The syntax starts with the first reference table: Encounter. Because the validated Encounter column in the data table (ChargeDetail) has the same name as its lookup column in the Encounter table, we do not need to include that column.
• The syntax specifies PrimaryPhysician instead of Physician, because the column name in the Encounter table is different than the corresponding column in the Physician table.

AX2076