In ServiceNow, Transform Maps are a key part of the data import process. They are used to map and transform data from an Import Set Table into a Target Table, ensuring that the incoming data is properly formatted and aligned with the system's data structure.
Let's break down the different components, functionality, and steps involved in working with Transform Maps in ServiceNow.
1. What is a Transform Map?
A Transform Map is a set of field mappings between a source (import set) and a target (ServiceNow table). It defines how the data from the import set should be transformed and inserted or updated in the target table.
- Import Set: A staging area where external data is temporarily stored before being processed.
- Target Table: The table in ServiceNow where the data is eventually inserted or updated.
For example, you might use a Transform Map to import user data from a CSV file (stored in an Import Set) into the User [sys_user] table in ServiceNow.
2. Key Components of a Transform Map
- Source Table (Import Set Table): This is the table where the incoming data is initially loaded. For example, if you are importing user data, it could be an import set table like
u_import_user. - Target Table: The table where the transformed data will be inserted. In the case of user data, the target would be the User [sys_user] table.
- Field Mapping: Defines which fields from the source table are mapped to which fields in the target table. You can manually map fields or use the Auto Map Matching Fields feature if the field names are similar.
- Coalesce Field: A coalesce field is a unique field (or set of fields) that ServiceNow uses to determine whether to insert a new record or update an existing one. If a record with the same coalesce value exists in the target table, ServiceNow updates that record instead of creating a new one.
3. Steps to Create a Transform Map
Here's how to create and use a Transform Map:
Step 1: Load Data into Import Set Table
First, you need to bring the external data into ServiceNow using an Import Set. This could be through a CSV file, an integration with an external system, or other data sources.
Navigate to System Import Sets > Load Data and load your data into an Import Set table.
Step 2: Create a New Transform Map
- Go to System Import Sets > Transform Maps.
- Click on New to create a new Transform Map.
- Provide a name for the Transform Map and select the Import Set Table and Target Table.
- Click Submit.
Step 3: Define Field Mappings
Once the Transform Map is created, define the mappings between the Import Set fields and the Target Table fields:
- In the Transform Map form, click on Mapping Assist.
- Map the fields from the Import Set Table to the corresponding fields in the Target Table.
- Save the mappings.
Step 4: Set Coalesce Fields
In the Transform Map, select one or more fields as the coalesce field(s). These are the fields ServiceNow will use to identify whether to insert a new record or update an existing one.
For example, if you're importing users, you might choose the Email field as the coalesce field. If the email exists in the User table, it will update the existing record; otherwise, a new record will be created.
Step 5: Transform the Data
Once the mapping is complete, you can Run Transform to transform the data from the Import Set into the Target Table.
4. Transform Scripts
Transform Maps support scripts that allow for more advanced data manipulation. There are four types of scripts that can be used during the transform process:
- onBefore Script: Executes before any record is processed. This is useful for preparing data or setting up prerequisites before the transform begins.
- onAfter Script: Runs after a record has been processed. It is useful for post-processing tasks such as logging or triggering other actions.
- onStart Script: Runs before the first record is processed. It's used for setup tasks that need to happen only once before the import.
- onComplete Script: Runs after all records have been processed. Useful for cleanup, sending notifications, or triggering related actions after the data import.
Example (onBefore Script):
(function transformRow(source, target, map, log, isUpdate) {
// If the department field is empty in the source, set a default value
if (!source.department) {
target.department = 'General';
}
})(source, target, map, log, isUpdate);
5. Coalesce Fields Explained
The coalesce field is a crucial part of the transform process. It helps ServiceNow determine whether the incoming data should be:
- Inserted as a new record: If no matching record is found based on the coalesce field.
- Updated: If a matching record is found in the target table.
If you don't set any field as a coalesce field, the system will always insert new records, even if the data already exists.
You can coalesce on multiple fields for more complex scenarios, such as matching both the email and username fields to ensure accurate updates.
6. Best Practices for Transform Maps
- Test in a Development Environment: Always test your Transform Map in a development or staging environment to avoid corrupting production data.
- Use Coalesce Wisely: Be cautious when selecting coalesce fields to prevent unintentional data overwrites or duplicate records.
- Data Validation: Use onBefore and onAfter scripts to validate data and ensure data integrity before it is inserted into the target table.
- Performance Optimization: Limit unnecessary field mappings and avoid complex scripts that could slow down the transform process.
7. Example Scenario: Importing User Data
Objective: Import user data from a CSV file into the User [sys_user] table in ServiceNow.
Steps:
- Load the user data from the CSV file into an Import Set Table.
- Create a Transform Map to map fields like first_name, last_name, email, and department from the Import Set Table to the User table.
- Set email as the coalesce field to avoid creating duplicate users.
- Use onBefore script to set a default department if one is missing.
- Run the Transform to update existing user records or create new users as needed.