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 Management section. Lookup fields are added on the Transformations tab. Since we want to add the new field to the Customer table, navigate to the Customer table and click the + button and select Lookup.

This will add a field named "Lookup" to the Customer table. Click on the field to see the configuration details where you will specify the table/field to look up and how the tables are joined.

To rename the field from "Lookup" to something relevant, click the pencil icon to the right of the Name field. This will allow you to change the field name. For this example, we will rename it to "Country Name" and click Save.

Next, we will define that table and field that we want to look the value up from. By default, there is no selection made (the Target field is set to No Selection), so you will click the pencil icon to the right of Target.

A window will appear showing all the tables and fields in the Staging Area. Select the table and field that you want the Lookup field to return. In this example, we are wanting to bring in the "Name" field from the "Country/Region" table.

Setting the Relationship Between the Tables

The Target field will now show the table and field that is being looked up. The last step is to define how the tables relate to each other, so Cosmos knows how to link the tables together to perform the lookup. You can click the Add Lookup Mapping button to add rows where the linking fields between the two tables can be defined. In the example below, a link has been set between the "Country/Region Code" field in the Customer table and the "Code" field in the "Country/Region" table. A secondary link has been added between the Company fields on each table. This way if there are different names specified in the data source depending on the company, the correct value will be returned.

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". 

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.