Using Lookups for Settings & Defaults

Often when integrating with an application a number of settings or default values are required to either control the processing of an integration or set fields of a newly created record with a default.

There are two way to store these values:

  • Embedding in the Job Configuration - This approach whilst easy, does make it difficult to maintain as changes need to be made to the configuration and the actual location of value must be found.
  • Using a Lookup Table - This approach externalises any setting or default values to an IMan Lookup table. This makes changing the setting trivial (as it's just a change to value in the table) and also allows end users to maintain the values.

This article shows you have to setup and use the second approach.

Setting up the Lookup Table

  1. Go Setup, Lookup Tables, and double click the empty row at the top to create a new entry.
  2. Enter a value into the Lookup ID, and Description, Lookup Result Label and press Save.

    Depending on the customer, the number of integrations will determine if you setup a single table for all settings, for all integrations or create different tables for different sets of integrations.

    This guide is based on the assumption that a single table is created called INTCONSTANTS (as shown).

  3. Click on the Lookups menu option, and create a new Lookup, using the following options (for simplicity the Lookup ID is the same as the Lookup table ID).

Populating the Lookup Table

  1. Go Setup, Lookup Table Maint, select the INTCONSTANTS table from the drop down.
  2. Double click to create a new entry, and press the Green tick to save. In this example we can created an entry for a default; of course you can use the table to store any setting.
  3. Repeat for each of the settings/defaults you wish to store.

Using the Lookup

Return to the designer, and in any transform or task supporting VBScript use the following formula to recall the values from the table, where:

  • First Parameter - The Lookup ID/Lookup Table ID.
  • Second Parameter - For simple lookups (such as this one) the field to return is always LKUPRESULT.
  • Third Parameter - The ID of the record in the lookup table.
  • Fourth Parameter - Set to True - Expect a matching record in the Lookup table, otherwise someone has deleted it.
Lookup("INTCONSTANTS", "LKUPRESULT", "CUSTGRP", True)