Zoomed Image
USA
800 691 9120
UK
01225 704844

Source to Destination Data Mapping

xAssets Configuration Guide
Integration and Data Operations with Transformations

Source to Destination Data Mapping

The Data Mapping tab defines how fields from the data source are mapped to fields in the data destination. This page explains the mapping interface, field properties, lookup resolution, and calculated fields.

Prerequisites

The Mapping Interface

The Data Mapping tab displays a source data preview (the first nine rows of data) and a list of all source fields with their current destination mapping:

Data Mapping tab showing source fields mapped to destination fields

The top section shows each source field and the destination field it is mapped to. Click on any source or destination field in the list to change the mapping or set rules for the mapping process. Use the Add Field link to create a mapping for a field that is not shown in the list (for example, a calculated field that does not exist in the source).

Field Mapping Properties

When you click a field mapping, the mapping editor opens:

Source-to-destination field mapping editor with lookup and rule options

The following properties are available for each field mapping:

Source Field

The name of the field in the data source. If set to CALC, the field does not come from the source data -- instead, its value is calculated from an expression or set to a fixed default value.

Destination Field

The field in the data destination where the value will be saved. The dropdown list of available fields is determined by the data destination tab configuration. For temporary table or export destinations, field names can be entered manually.

Lookup Value Using

When the source data contains a descriptive value (such as a location name) rather than the unique identifier (such as a location ID), specify a lookup here. The system will search the lookup table to find the matching ID.

For example, if the source data includes a location name like "London Office" but the destination requires a LocationID, configure a lookup of LocationID from LocationName:

TODO Screenshot (manual): Requires editing a specific field mapping that uses a lookup

If Not Found

When a lookup does not find a matching ID, the system can take one of four actions:

Option Behaviour
Automatically Add the Value into the Lookup Table Creates a new entry in the lookup table with the description/name supplied from the source. Use this when you want new values to be created automatically (e.g., new locations or departments).
Insert the Default value, or NULL Sets the destination to the default value defined below, or NULL if no default is specified. Use this when missing lookups should be silently replaced.
Ignore This Record Skips this record entirely -- no data is written for it. Other records continue to be processed. Use this when records without valid lookups should be excluded.
Fail the import with an Error Message Stops the entire transformation with an error. No data is written for any record. Use this when data integrity is critical and missing lookups indicate a problem that must be resolved before proceeding.

Warning: "Automatically Add" can create many unwanted entries in lookup tables if the source data contains typos or inconsistent naming. Consider using "Fail the import" during initial testing to identify data quality issues, then switch to "Automatically Add" once the source data is clean.

Only If NULL

When ticked, the system only writes data to the destination field if the destination does not already contain a value. This is useful for preserving manually entered data during imports -- the import fills in blanks but does not overwrite existing values.

Translation

Provides options for converting date formats between different regional conventions, or for inserting a unique auto-generated number into the destination field.

Default Value

A fixed value to insert into the destination field when no source data is available, or when a lookup fails (and the "Insert the Default value" option is selected).

Expression

An expression that calculates a value to place into the destination field. Expressions can reference other source fields, perform string concatenation, date arithmetic, and conditional logic. This is how calculated fields (source set to CALC) get their values.

Fixed Width

The width of the field for fixed-width file imports. Only relevant when importing from fixed-width text files where columns are defined by character positions rather than delimiters.

Common Mapping Patterns

Pattern Configuration
Direct mapping Source field maps to same-named destination field. No lookup or expression needed.
Lookup mapping Source field contains a name; lookup resolves it to an ID for the destination.
Calculated field Source field set to CALC; expression computes the value from other fields.
Default fill Source field set to CALC; default value provides a constant for all imported records.
Conditional write "Only If NULL" ticked to preserve existing data during re-imports.