Overview

Lookup fields provide a way to consolidate data together by starting with a primary table and then going to a secondary table to return a related value. An example of this could be a transaction table that has a customer number, but no customer name. You can use a Lookup field to go to the Customer table, find the customer that matches the transaction, and then return the customer's name onto the transaction itself. This article will cover the details of working with Lookup fields.

• Adding a Lookup Field to a Table
• Setting the Relationship Between the Tables
• Using Hard-Coded Values in Relationships

Details

Lookup fields provide a streamlined way to bring in data from one table to another table. This is primarily used when you need to look up a descriptive value from a secondary table, such as a customer name, and bring it onto a primary table, such as a sales transactions table.

Adding a Lookup Field to a Table

In the example below, we have a Customer table that has a Country Code field and we have a Country/Region table that contains a list of all countries and their associated names. We are looking to bring in the Name field of the country and add it to the Customer table. 

Before getting started, make sure that all the relevant tables and fields, including the fields that will be used to join the tables together, have been added to the Staging Area. 

To add a lookup field, log in to the Cosmos Portal and navigate to the Data Model section. Lookup fields are added on the Lookup Transformations tab. Since we want to add the new field to the Customer table, navigate to the Customer table and click the + button in the  Lookup Transformations section.

This will open an Add Lookup Transformations window. You will then name the lookup, for example Country Name, and then pick the data source that you want to lookup the field from. You can then select the table and field that you want to bring in from the other table.

Next, you will add the correct join criteria which will generally by on the Company field in addition to any other fields that are needed to bring in the correct data from the other table. To add more join fields you can click the + button on the right.

Using Hard-Coded Values in Relationships

There may be times where you need to add a hard-coded relationship to the lookup table. This will typically happen when there may be multiple records that are returned based on field-to-field relationships, and you need to add a hard-coded value to further narrow the lookup record set down.

One example of this could be when adding advanced dimension lookups into the G/L Entry table. In the example below, the top row of relationships (Dimension Set ID = Dimension Set ID) are field-to-field selections, while the second row specifies that the "Dimension Code" field in the Dimension Set Entry table should be equal to the hard-coded value of "AREA". You will add this in the Filter Criteria section shown below.

With the three records below that all have the "Dimensions Set ID" = 3, only the value for the record that has the "Dimension Code" = AREA, which is the top record, would be returned by the lookup

Once the lookup table and field has been defined and the lookup mapping field(s) set, the Lookup field is finished and can be used in other transformations or mapped into the reporting database. 

These changes will be made once the extension has been checked in and the Publish Pipeline button has been pressed to publish the changes.