Lookup Tables are a convenient way to translate a value into another during the mapping process. These are very useful if you want to replace the occurrence of one name or string to the other.
In this guide:
Before we create any Lookup table let us review a couple of examples that clearly demonstrate how the lookup table can solve a very specific problem of data inconsistency.
Example1: Country Codes Standards
Imagine you are integrating two different API that both are exposing a country code but using different standards to represent a country. If a source API is using a two-letter (ISO 3166-1 alpha-2) code and the action a three-letter (ISO 3166-1 alpha-3) code, the target API will not be able to store the country code. This is where a Lookup Table is helpful. It helps to translate a two-letter country code into a three-letter country code by defining that value AT is to be translated into value AUT, as shown in the following screenshot:
Example2: Enumeration vs. Internal ID
Let's consider another example in which both API are exposing address types, such as billing and shipping. If the source API is using an enumeration for address types, such as billing and shipping, and the target API is expecting an internal ID of the address type, lookup tables can help to translate the address types during mapping. The following screenshot demonstrates an example of such a Lookup Table:
Now that we know how the Lookup Tables can help to solve a specific problem let us see how to create one
How to create a Lookup Table
Possibilities of Lookup table usage are many but it requires prior knowledge of the translation/replacement and it needs to be defined in advance to be used. Basically, if you are expecting such inconsistency between input and output data you better create a Lookup table to show our system how to translate one value into the other one.
To add a LookUp Table you must navigate to Setups > Lookup Tables.
When you open this page there would not be any Lookup tables:
Let's create one table by clicking on + New lookup table button and give a name. For consistency with the presented examples above we shall create one table called Country Code:
You will be redirected to a particular Lookup table page:
Here you can start inputting the values for Lookup Key and the Target value. Here is how the process would look like:
Please use the green plus sign button to add the imputed value - not enter.
By navigating to the main page of Lookup tables (simply click on Lookup Tables) we can see already created a table in the list.
Updating the Lookup table value
Let's assume that during the filling of the lookup table you accidentally entered a wrong Target value or would like to change it. To do so navigate to the particular Lookup table page by clicking on the name from the Lookup tables list and enter the Lookup Key which needs to be changed and the new value for the Target value field - press green plus button.
If you need to change the value of Lookup Key you would need to delete the old value or just simply create the new one using the form on the top.
Default Row in Lookup Tables
Sometimes you will need to define a default row in your Lookup Table. A default row is used when for the given lookup key no corresponding target value is found. Creating a default row in a Lookup Table is accomplished by adding a new row with an empty lookup key. This row will be recognised and its key will be displayed in our UI with the word Default, as shown in the following example:
How to use Lookup tables
The beauty of Lookup tables is seen when it is used in the mapping of incoming fields into outgoing fields. The mapper component will replace those values on-the-fly. Please consult the documentation called Using Lookup Tables along with data mapper to learn more. As a preview here is how to select already created the Lookup table in the mapping stage: