In value mapping, an individual value in a source field is configured to be converted to a value 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.
Value mapping is performed by a Business User and documents the legacy and target value that will be mapped, while Developers write the rules or use AutoGen to create the rules that perform the actual value mapping.
Lookup tables, also called check tables, contain a value that must be value mapped to the source field in the legacy system.
Value Mapping can be performed on lookup tables with a single key . . .
. . . or a multi-part key.
This article uses an example of value mapping the Region field, which uses a multi-part Key lookup table.
At a high level, the value mapping process is:
- Configure lookup tables in Target Design.
- Assign an XRef action to the field while field mapping.
- Refresh target values and legacy values.
- Perform value mapping.
In Target Design, lookup tables can be imported as part of a System Type, database, or Excel file or can be set up manually.
NOTE: You can also add a lookup table manually to a system type in Common. Lookup tables must be added manually to System Types for systems where there is not a complete model set up or where the System Types are manually built. In this case, when the System Type is imported into Target Design, the lookup table is also imported. Refer to “Add a Lookup Table Manually” in the online help for more information.
When configuring a single key or multi-part key lookup table in Target Design manually:
- Enter the lookup table name in the VALUE TABLE NAME field.
- Set the TYPE to Configuration.
NOTE: The Configuration type must be used for value mapping.
- Enter the key field(s) in the VALUE TABLE COLUMN1 and VALUE TABLE COLUMN2 fields as needed.
NOTE: You can have up to five keys for a lookup table. Additional fields are available on the Vertical View.
- Complete the DESCRIPTION TABLE NAME and DESCRIPTION TABLE COLUMN fields that provide the description of the key field used during the value mapping process.
Configure the MULTI VALUE FIELD LOOKUP TABLE VALUE COLUMN, if setting up a lookup table with a multi-part key. This field is required, and indicates which key is stored in the target.
in this example, the LAND1 field (Country) and the BLAND field (Region) are the multi-part key. The BLAND field will be stored in the target once value mapping is complete.
NOTE: You cannot use this page to configure a lookup table if more than one key must be stored. This task must be performed on the back end.
- Assign the lookup table to a field.
When the target is synced to map, the lookup table is also synced.
This step must be performed before you can refresh values or perform value mapping. In this example, the Region field is assigned an Xref action.
NOTE: Use RuleXref if the legacy data must be modified so that it can be used by an Xref rule. For example, if a legacy key field has Division and Department in the same field and only the Division is used in the Xref rule, then write a rule that extracts this part from the legacy values and create the Xref rule on the extracted data. If you have a multi-part key but do not need to modify legacy values, then use an Xref rule.
Submit the rule when it’s complete.
NOTE: Depending on how your project is set up, you could then use SQL AutoGen to generate the source table, target table, and the source rules for the Xref rule. Once the rules are built, AutoGen registers the rules in Transform. However, the value mapping process does not require the Xref rule to be generated. A Business user can perform value mapping at any time before or after the rule has been submitted..
Refresh target values first to ensure you have the latest data from the ERP system, capturing any new or updated values that must be value mapped.
Refresh legacy values next to pull in all the distinct values for the field from the legacy database.
For example, if the Region field is being value mapped, when the legacy refresh occurs, all of the distinct values stored in the Region field in the legacy database, such as Alabama, Alaska, etc, are pulled in to the lookup table.
Begin the value mapping process on the Value Mapping page.
NOTE: If the Values or Mappings icons do not have numbers (i.e., there are no records), then value mapping has not been configured correctly. These records must exist for value mapping to proceed.
On the Value Mapping (Legacy to Target) page, the legacy values pulled from the legacy refresh display in the LEGACY VALUE column.
The TARGET VALUE column, the value that will be saved in the ERP system, uses the configuration set up in Target Design. For a lookup table with a single key, the configuration results in the format Description (K), for example Alabama (AL).
If a description is not configured for the lookup table in Target Design, the Target Value is (K) or (AL).
For a multi-part key, the format is Description (K:K). The Value Table Column1 and Value Table Column2 fields configured in Target Design store the multi-part key. These values display as Alabama (US: AL) in the TARGET VALUE column.
Select the value in the TARGET VALUE field to finish the value mapping process.
You can use the PROPOSED VALUE field to document a proposed target value; however, the value is for informational purposes only.
Populate the Legacy Description Field
If, as on the screen shot above, the LEGACY DESCRIPTION field on the Value Mapping (Legacy to Target) page displays “Description Field Not Defined,” identify the table and field on the Legacy Value Source Table Fields page.
- On the Value Mapping (Config) page, click the Sources icon for the lookup table.
- With the Source selected on the Value Mapping (Sources) page, click the Tables icon for the lookup table on the Source Check Table (Config) page.
- Select the table and the field that contains the description in the LEGACY DESCRIPTION TABLE and LEGACY DESCRIPTION FIELD. This value will then populate the LEGACY DESCRIPTION field on the Value Mapping (Legacy to Target) page.
- Click Refresh on the Legacy Value (Source Table Fields) page.