Create a field map

Field maps establish a relationship between a field in an import set table and a field in the target table.

Before you begin

Role required: import_transformer, import_admin, or admin

About this task

The field map determines what values from the source table the transformation adds to the target table.

Procedure

  1. Navigate to System Import Sets > Transform Maps.
  2. Open a transform map.
  3. In the Field Maps related list, click New.
  4. Fill in the fields, as appropriate (see table).
  5. Click Save.
  6. Repeat steps 3-4 for each field mapping.
  7. Click Update.
    Table 1. Creating a Field Map
    Field Description
    Source table Displays the table where the raw import set data is loaded (the source for the transformation). This field is automatically populated from the transform map. You can select only tables within the currently selected application scope.
    Source field Select the field on the source table to be transformed. This may be blank if the Source table only contains raw data.
    Map Displays the transform map that uses this field mapping. This field is automatically populated.
    Date format This field is available if the target field is a date or date-time field. This field specifies the date format of the source field.
    Choice action This field is available if the target field is a choice list or reference field. This field specifies what to do if the import set contains a reference or choice value other than those available. Select one of these options:
    • create: Create a new choice or record in the reference table.
    • ignore: Ignore the new value from the source table.
    • reject: Skip the entire row (record) containing the new value and continue to the next row.
    Use source script Select this check box to use a script instead of the Source field.
    Source script Enter a script to determine the source of this field mapping instead of the Source field. The script should return the answer variable. For example, this source script combines information from multiple sources into a single value.
    Target table Select the table where you want transformed data to be placed. You can select only tables within the currently selected application scope, the global scope, or tables that grant write access to other applications
    Target field Select the field where the values from the source field is stored.
    Referenced value field name When the target field is a reference field, the transform map needs a way to match incoming source values to existing records in the reference field's source table. Since most imports do not provide a 32-character sys_id value, you must specify a column from the reference field's source table that contains values that match the incoming source values.

    When there is a matching record, the transform map stores the sys_id of the matching record in the target field. If there is no matching record, the transform map creates a new record in the reference field's table and stores the sys_id of the new record in the target field.

    If you leave this field blank, the transform map looks for matching values from the display value column of the reference field table.

    For example, suppose you are importing incident records and the incoming data lists user IDs for the Assigned to field. If you leave Referenced value field name blank, the transform map searches for matching values in the User table's display value column: name. By setting the Referenced value field name to the user_name column you can match the user ID values to the appropriate user records.

    Coalesce Configuring a target field to coalesce causes the import set to treat the field as a unique key. When selected, the import set application attempts to match source values to records with values from an existing record. If a match is found, the transform map updates the record instead of creating a new record. When false, the import set application always creates new records for each transformation. If multiple fields are set to coalesce, all coalesce values must match an existing record. If two fields are set for coalescing and a matching value is found for one of the coalescing fields but not on the other, a new record is inserted.

    You must create an index on the target table if none of the coalesce fields are indexed. Indexing can improve performance for read and write operations. If one or more coalesce fields already has an index, you do not need to create an additional index. After setting the Coalesce value for all fields on the target table, use the Index Coalesce Fields related link to create an index. Before creating the index, ensure that the Coalesce value is set correctly for all fields on the target table to avoid creating unnecessary indexes for the same target table.

    Coalesce empty fields Select this check box to match an empty source field value to an empty target field value. For example, the User transform map coalesces on the email field. With this option selected, a source record containing an empty email address coalesces to a target record containing an empty email address.
    Coalesce case sensitive Select this check box to have case sensitive coalesce values result in the creation of new records. By default, values marked as Coalesce are used in a case insensitive lookup for existing records. Case insensitive records only update existing records and do not cause the creation of new records.
    A completed field map record with coalescence enabled might look like this:
    Field map record