Target Design

Set up Lookup Tables

In value mapping, an individual value in a Source field in one table is configured to be converted to a value stored in another table that the target system will accept in the Target field. For example, if two large companies merge, they may want to convert the Legacy Org Numbers first and then value map to the new Org numbers (in an Org check table) in the Target ERP system.

Refer to Perform Value Mapping for more information.

The field and value that must be value mapped are stored in check tables, also called lookup tables.

Lookup tables for a Source can be added in Target Design:

  • Manually by adding the tables and fields on the Target Lookup Table page in Target Design
  • Automatically on import, when a System Type that contains the lookup table is imported into Target Design. Check tables are stored in System Types, which are created in Common. Refer to System Types in Common for more information.

Lookup tables are automatically synced to Map when the lookup table is saved.

NOTE: A lookup table is registered as a Source table with a status of Documentation and a rule priority offset of 50000 so that it is sorted last. Documentation sources are not processed but are a reference item to see all the sources of data.

NOTE: If a Wave is copied in Console, the lookup tables are also copied.

A simple lookup table contains one key. A complex lookup table contains a multiple part key.

NOTE: A lookup table can be added in Target Design with up to five key fields. If the table requires more than five key fields, a view must be written.

NOTE: If the Target system is Oracle, a view must be written. A Target lookup table for an Oracle Target cannot be created using the Target Lookup Table page.

A lookup table that already exists can be edited, or a lookup table can be added. The process is the same.

This topic contains the following sections:

Set up a Simple Lookup Table

A simple lookup table contains one key.

NOTE: The RuleXref action must be used with a lookup table that has multiple key fields. This will allow missing values in the Target table to be inserted into the Value Mapping table. The Xref action can only be used with a lookup table that has a single key field. If the Target Lookup table is a multiple key table, the Value Mapping process will concatenate Target values from the multiple key fields into a single field. To map fields on a multiple key lookup table, use the RuleXref action for the field to be mapped on the Field Mappings page. RuleXref concatenates Source values and is the recommended Migration Solution process to load the values correctly into the Value Mapping process.

NOTE: A description for the Lookup Table Value table can be added on the Target Lookup Table page's Vertical View in the Description field.

To add or edit a simple lookup table in Target Design:

  1. Select Configuration > Lookup Table Setup in the Navigation pane.

  2. Click Edit or Add.

    View the field descriptions for the Target Lookup Table page

  3. Enter the name of the table that contains the values that are used in value mapping in the VALUE TABLE NAME field.

    NOTE: If editing a lookup table, this name is already provided.

  4. Select an option in the TYPE list box.
  5. Enter the key field in the lookup table in the VALUE TABLE COLUMN 1 field. 
  6. Enter the name of the table that stores the descriptions for the values in the value table in the DESCRIPTION TABLE NAME field.

    NOTE: This field displays in Map on the Vertical View of the Value Mapping (Config) page.

  7. Enter the name of the column that stores the descriptions for the values in the value table in the DESCRIPTION TABLE COLUMN field.
  8. Click Save.

Set up a Complex Lookup Table

A complex lookup table contains a multiple part key.

NOTE: A lookup table can be added in Target Design with up to five key fields. If the table requires more than five key fields, a view must be written.

NOTE: If the Target system is Oracle, a view must be written. A Target lookup table for an Oracle Target cannot be created using the Target Lookup Table page.

NOTE: The RuleXref action must be used with a lookup table that has multiple key fields. This allows missing values in the Target table to be inserted into the Value Mapping table. The Xref action can only be used with a lookup table that has a single key field. If the Target Lookup table is a multiple key table, the Value Mapping process concatenates Target values from the multiple key fields into a single field. To map fields on a multiple key lookup table, use the RuleXref action for the field to be mapped on the Field Mappings page. RuleXref concatenates Source values and is the recommended Migration Solution process to load the values correctly into the Value Mapping process.

NOTE: When mapping fields on a multiple key lookup table, concatenate the fields with a ":" separator, for example, field1:field 2.

NOTE: A description for the Lookup Table Value table can be added on the Target Lookup Table page's Vertical View in the Description field.

To configure a complex lookup table (one with a multipart key) in Target Design:

  1. Select Configuration > Lookup Table Setup in the Navigation pane.

  2. Click Edit or Add.

    View the field descriptions for the Target Lookup Table page

  3. Enter the name of the table that contains the values that are used in value mapping in the VALUE TABLE NAME field.

    NOTE: If editing a lookup table, this name is already provided.

  4. Select an option in the TYPE list box.

  5. Enter the first key in a value table with a multipart key in the VALUE TABLE COLUMN 1 field.

  6. Enter the second key in a value table with a multipart key in the VALUE TABLE COLUMN 2 field.

    NOTE: Additional columns can be added on the Vertical View.

  7. Enter the column on the Value Table that stores the Client or Tenant of the data in the VALUE TABLE CLIENT COLUMN field.

    NOTE: This field is used with SAP only.

  8. Enter the name of the table that stores the descriptions for the values in the value table in the DESCRIPTION TABLE NAME field.

    NOTE: This field displays in Map on the Vertical View of the Value Mapping (Config) page (Configuration > Value Mapping (Config)).

  9. Enter the name of the column that stores the descriptions for the values in the value table in the DESCRIPTION TABLE COLUMN field.

  10. Enter the key column on the Description table used to uniquely identify values if the Description table key is different from the Value table's key column in the DESCRIPTION TABLE KEY COLUMN field.

    NOTE: This field is used with the lookup table T006.

    NOTE: If this field is blank, the Description Table Key uses the Value Table Key.

  11. Enter the column on the Description Table that stores the Client or Tenant of the data in the DESCRIPTION TABLE CLIENT COLUMN field.

    NOTE: This field is used with SAP only.

  12. Enter the column on the Description Table that stores the language identifier column in the DESCRIPTION TABLE LANGUAGE COLUMN field.

    NOTE: This field is used with SAP only.

  13. Click Save; the Vertical View displays.

  14. Select the column containing the value to use if more than one value table column is set for the value table (as in, there is data in the VALUE TABLE COLUMN 1 and VALUE TABLE COLUMN 2 fields and so on) in the MULTI VALUE FIELD LOOKUP TABLE VALUE COLUMN list box.

    NOTE: This is the actual field that will be loaded into the Target table.

  15. Enter the where clause used to filter values in the value table column to restrict the values used in the lookup table in the WHERE CLAUSE field, if needed.
  16. Click Save.