Creating Database Lookup Maps

Database Lookup

The Database Lookup map uses a database table containing both lookup values and output values. Database lookup can be performed based on a single lookup field, or a combination of fields. Similarly, Database Lookup can return a single output field from the database lookup table, or a combination of fields. In either case, the output field (or fields) are returned from the record in which the lookup value (or values) matched the incoming value (or values).

If there is no match for an incoming value (or values), Database Lookup object can trigger an error for that record, or generate a warning while returning a null value in the output field (or fields).

For example, the source contains names of states in abbreviated format (e.g. CA for California, NM for New Mexico, etc) in State_Abbr field, which you need to convert into fully spelled-out states in State field (e.g. California or Texas).

In this example, State_Abbr is the lookup field, and State is the output field.

To add a Database Lookup mapping, drag the Database Lookup object from the Maps group in the Flow toolbox and drop it on the dataflow.

An example of what a Database Lookup object might look like is shown below.

To configure the properties of a Database Lookup object after it was added to the dataflow, right-click on it and select Properties from the context menu. The following properties are available:

Database Info: Using this control, specify the connection information to the database storing your database lookup table.

Pick Table – select the database lookup table.

Add any additional WHERE clause input allows you to specify an additional filter for the lookup table. For example WHERE State_Abbr <> ‘HI’

Add Warning and Proceed – select this option if you want the Database Lookup to generate a warning and return a null value in the output field(s) when there is no match in the lookup field(s).

Add Error Message – select this option if you want the Database Lookup to trigger an error for the records that have no match in the lookup field(s).

General Options screen:

This screen shares the options common to most objects on the dataflow.

Usage

In the following example, a database lookup object is used to look up incoming data by SupplierId and Billing Postal Code. Whenever there is a match, the database lookup object outputs MasterRecordId, Name, Type, and ParentId which are then written to a delimited file.