How to Create an Import Mapping

This lesson describes how to set up an Import Mapping during the third step of the import plan process. The lesson that describes the import plan process in full detail is here. This part of the import wizard requires the user to create a “mapping” which matches source data fields to their corresponding fields in the local database table.

1. Select the Item that Will Be Mapped on the Left-Hand Item List

1. Select the Item that Will Be Mapped on the Left-Hand Item List
  1. Select a tree item/table column from the left container by clicking the name ( the checkbox is used for lookup key selection which is explained later on in this lesson). When the item is clicked it will have a blue highlight. This is how the user knows they selected that item.

2. Then Check the Box of the Corresponding Header Row in the Source File.

2. Then Check the Box of the Corresponding Header Row in the Source File.
  1. And map it to a source column in the right container by selecting the checkbox to the left of the source file header row. The column names from the Source Data in the right container will not always match exactly with the column names from the tree in the left container, so the user determines which columns match based on this mapping technique.
  2. The tree item will show in the Mapped To column in the right container. Columns can be remapped by selecting a different source item or unmapped by unchecking the checkbox.

3. Select Unique Lookup Keys

3. Select Unique Lookup Keys
  1. Once mapped, a tree item can be selected as a Unique Lookup Key by clicking the checkbox next to the column name. A Unique Lookup Key is the field(s) that identify a unique record in the database table. Multiple items can be selected to make up a composite key. Table columns with a unique index are preselected as keys (meaning they are already required) and cannot be unselected. Unique key lookup data ignores case.

Viewing the Target Attributes

Viewing the Target Attributes
  1. When a column from the left-hand container is selected, the Target Attributes values get filled in automatically. The user can use this for reference when creating unique lookup keys. All required fields must be mapped. These attributes are further defined in the mapping section in this lesson.

Note: The Target Attributes information is only displayed for whatever item(in the left-hand container) is currently selected. If the user wants to see the Target Attributes for another item, then they must select it first.

Mapping Fields in Reference Tables

Mapping Fields in Reference Tables

The user can also map to fields in reference tables (shown as folders that are able to be expanded) by choosing a field in that reference table.

  1. In this example: Manufacturer is a reference table to Invoice Items, so if the user wants to map to a column in that table, they must expand the Manufacturer table and select the appropriate field (which in this example is “Company Name”):

If a user wants to create a new record in a referenced table(if they selected Create New for the Lookup Record Not Found option in the Target Attributes), they MUST map the required fields in that referenced table. This is because a record cannot be created in a table without all of the required fields. A user can tell if a field is required because that field will have a red checkmark next to it and it will be listed as required in the Target Attributes list.

If a user does NOT want to create a new record in a referenced table (if they selected Skip Import for the Lookup Record Not Found option in the Target Attributes) then they will not have to map all of the required fields for that referenced table.

Selecting A Referenced Table as a Unique Key

Selecting A Referenced Table as a Unique Key

The user is able to select a reference table as a Unique Lookup Key, however if they choose to do this they must specify a key field in the reference table as well. This is because if a new record has to be created in a reference table, then the importer must know how to identify unique records.

  1. In this example the Invoice table is selected as a lookup key for Invoice Items and Invoice Number is selected as a lookup key for the Invoice table.
  2. Viewing the target attributes assists the user when creating the lookup keys. When the item “Invoice” is selected, the target attributes show that this is part of the Main Lookup.
  3. If "Invoice Number" was seleceted, then the checkbox for Part of Main Lookup would be UNCHECKED because it is a field in the Invoice table.

Selecting an Action for when the Lookup Record is Not Found

Selecting an Action for when the Lookup Record is Not Found

Existing records matching the key are updated and/or linked to a related table record. When no existing records matching the key are found, the user can select whether to Create New records or Skip Import for that row by choosing a value for “Lookup Record Not Found” found in the Target Attributes list above the Source Data.

Note: The "Lookup Record Not Found" selection must be made for every table that has a mapping.

If multiple tables share key values then selecting to skip importing a record on one table will also skip import on a related table instead of creating a new record. An example would be if the user only wants to add Invoice Item records for existing Invoice records. So for the Invoice table "Lookup Record Not Found" is set to "Skip Import", but for the Invoice Item table it is set to "Create New". If a lookup record is not found on the Invoice table, since it is set to "Skip Import" and Invoice Item uses the Invoice table as a lookup key, then the import should be skipped for the Invoice Item as well.

Note: If the user wants to map a referenced field but does not want to create new records on that referenced table then they can choose Skip Import. This way they will NOT have to map all of the required fields for that referenced table.

Mapping Columns in Tables without Selecting Keys

Mapping Columns in Tables without Selecting Keys

The User may encounter this audit message if they do not create keys for tables with mapped columns. Since keys specify unique records the system will prompt the user to add key for any mapped table that does not have a key selected.