Transform Maps and Field Mapping

Transform Maps and Field Mapping in ServiceNow – Complete Guide for CAD Exam

Introduction

Transform Maps and Field Mapping are essential components of ServiceNow's data import framework. They serve as the bridge between imported data (stored in staging tables) and the target tables where data ultimately resides in the platform. Understanding how they work is critical for the ServiceNow Certified Application Developer (CAD) exam, as questions on this topic frequently appear and test both conceptual understanding and practical application.

Why Are Transform Maps and Field Mapping Important?

In real-world ServiceNow implementations, data rarely originates within the platform itself. Organizations need to import data from external sources such as spreadsheets, CSV files, LDAP directories, JDBC databases, and other systems. Transform Maps and Field Mapping are important because they:

• Enable structured data migration – They allow administrators and developers to bring external data into ServiceNow in a controlled, repeatable manner.
• Ensure data integrity – By defining explicit mappings, you reduce the risk of data landing in incorrect fields or being malformed.
• Support data transformation – They allow you to manipulate, coalesce, and transform data during the import process rather than requiring post-import cleanup.
• Facilitate automation – Scheduled imports paired with transform maps enable ongoing, automated data synchronization between ServiceNow and external systems.
• Provide flexibility – Multiple transform maps can be applied to a single import set, allowing one data source to populate multiple target tables.

What Is a Transform Map?

A Transform Map is a ServiceNow record that defines the relationship between a source table (import set table/staging table) and a target table. It tells the system: "Take data from this staging table and put it into this destination table using these rules."

Key attributes of a Transform Map include:

• Name – A descriptive name for the transform map.
• Source table – The import set (staging) table where raw imported data resides.
• Target table – The ServiceNow table where the transformed data will be written (e.g., incident, cmdb_ci_computer, sys_user).
• Active – A flag indicating whether the transform map is currently in use.
• Run business rules – A checkbox that determines whether business rules on the target table will execute during the transformation.
• Enforce mandatory fields – Controls whether mandatory field requirements on the target table are enforced.
• Order – When multiple transform maps exist for the same import set, order determines the sequence of execution.

What Is Field Mapping?

A Field Map is a child record of a Transform Map that defines the relationship between a specific column in the source (staging) table and a specific field in the target table. Each field map says: "Take the value from this source column and place it into this target field."

There are several types of field mapping:

• Direct mapping (Mapping) – A straightforward one-to-one mapping from a source field to a target field. For example, source column email_address maps to target field email.
• Source script mapping – Uses a script to derive the value that will be placed into the target field. The script runs in the context of the source row and returns a value.
• Date/Time format – Allows specification of date and time formatting when the source data uses a non-standard format.
• Reference field mapping – When the target field is a reference field, you can define how to look up the referenced record (e.g., match by name, sys_id, or another field).
• Choice action – Defines what happens when the source value does not match an existing choice value on the target field: reject, create, or ignore.

How the Import and Transform Process Works

The end-to-end process follows these steps:

Step 1: Data Source Configuration
A Data Source is created in ServiceNow that points to the external data (file, JDBC, LDAP, etc.). This defines where the data comes from and how to connect to it.

Step 2: Import Set Table (Staging Table)
When data is loaded, it first lands in an Import Set Table (also called a staging table). This is a temporary holding area. The table is typically auto-created based on the structure of the incoming data. Each row in the import set table represents one record from the source.

Step 3: Transform Map Creation
A Transform Map is created (or auto-mapped) that links the import set table to the desired target table. You define the source table, target table, and configure options like whether to run business rules.

Step 4: Field Mapping Definition
Within the Transform Map, Field Maps are created to specify how each source column corresponds to a target field. Auto-mapping is available and attempts to match columns by name, but manual adjustments are often necessary.

Step 5: Coalesce Fields
One or more field maps can be marked as Coalesce. A coalesce field acts as a unique identifier to determine whether a record in the target table should be updated (if a match is found) or inserted (if no match exists). This is critical for preventing duplicate records. For example, if you coalesce on the email field when importing users, the system will update existing users whose email matches and create new records for emails not found.

Step 6: Transform Execution
The transform runs, processing each row from the staging table through the field maps and writing data to the target table. Each row gets a transform result status: inserted, updated, ignored, or error.

Step 7: Review Results
After transformation, you can review the Import Set Run and Import Log to see which records were inserted, updated, or encountered errors.

Key Concepts for the CAD Exam

1. Coalesce Fields
This is one of the most frequently tested concepts. A coalesce field is used to match incoming data against existing records in the target table. If a match is found, the record is updated. If no match is found, a new record is inserted. Multiple fields can be marked as coalesce, and when multiple coalesce fields exist, they work together using AND logic – all coalesce fields must match for an update to occur.

2. Transform Event Scripts
Transform Maps support event scripts that run at various points during the transformation:

• onStart – Runs once at the beginning of the entire transform, before any rows are processed. Useful for initialization.
• onBefore – Runs before each individual row is transformed. You can modify field values, skip rows, or set values dynamically. Access the source row with source and the target record with target.
• onAfter – Runs after each individual row has been transformed and saved. Useful for creating related records or performing post-processing on the target record.
• onComplete – Runs once after all rows have been processed. Useful for sending notifications or performing cleanup tasks.
• onForeignInsert – Runs when a referenced record needs to be created because it doesn't exist.

3. Run Business Rules
The Run business rules checkbox on the Transform Map determines whether business rules defined on the target table will fire during the import. In many cases, this is set to false to improve performance and avoid unintended side effects (such as triggering notifications for every imported record).

4. Auto Mapping
ServiceNow can automatically attempt to map source columns to target fields by matching names. While convenient, auto-mapping should always be reviewed and verified because:
• Column names may not exactly match field names.
• Some fields may need scripted transformations.
• Reference fields may require specific lookup configurations.

5. Multiple Transform Maps
A single import set can have multiple transform maps, each targeting a different table. They execute in the order defined by the Order field. This is useful when a single data source needs to populate multiple tables (e.g., importing both users and their department records from one file).

6. Choice Action
When mapping to a choice field on the target table, the Choice Action setting determines behavior when the source value doesn't match any existing choice:
• create – Creates a new choice value.
• reject – Rejects the record and marks it as an error.
• ignore – Leaves the field empty and continues processing.

7. Import Set Table Cleanup
Import set tables are staging tables and can accumulate large amounts of data over time. ServiceNow provides a scheduled job to clean up old import sets. This is important for performance and storage management.

8. The source and target Objects in Scripts
In transform map scripts (onBefore, onAfter, etc.):
• source – Refers to the current row in the import set (staging) table.
• target – Refers to the corresponding record in the target table.
• map – Refers to the current transform map record.
• action – Indicates whether the current operation is an insert or update.
• ignore – A boolean that can be set to true in onBefore to skip the current row.
• error – A boolean that can be set to true to mark the row as an error.

Practical Example

Suppose you need to import a CSV file of employees into the sys_user table:

1. Upload the CSV file via System Import Sets > Load Data. This creates a staging table (e.g., u_employee_import) and populates it with rows from the CSV.
2. Create a Transform Map: Source = u_employee_import, Target = sys_user.
3. Define Field Maps:
- u_first_name → first_name (direct mapping)
- u_last_name → last_name (direct mapping)
- u_email → email (direct mapping, marked as Coalesce = true)
- u_department_name → department (reference mapping, lookup by department name)
- u_active → active (source script to convert "Yes"/"No" to true/false)
4. Add an onBefore script to set default values for fields not in the source data (e.g., target.notification = 2; to disable notifications).
5. Run the transform. Records matching by email are updated; new emails result in new user records.

Common Pitfalls

• Forgetting to set coalesce – Without a coalesce field, every import run creates duplicate records in the target table.
• Not handling reference fields properly – If a reference field value doesn't match any existing record and no onForeignInsert handler exists, the reference field may be left empty or cause an error.
• Leaving Run Business Rules enabled for bulk imports – This can severely impact performance and trigger unwanted workflows or notifications.
• Not testing with a small data set first – Always validate transform maps with a subset of data before running against the full data set.

Exam Tips: Answering Questions on Transform Maps and Field Mapping

1. Know the coalesce concept thoroughly. Expect at least one question on what coalesce does. Remember: coalesce determines whether to insert or update. Multiple coalesce fields use AND logic.

2. Understand the order of transform event scripts. The sequence is: onStart → (onBefore → onAfter) per row → onComplete. Know what each event is used for and what objects are available in each context.

3. Remember the source, target, and action objects. Questions may ask what variable you use to reference the staging table row (source) versus the target table record (target) in transform scripts.

4. Know how to skip a record during transformation. Setting ignore = true in an onBefore script will cause the current row to be skipped. This is a commonly tested scenario.

5. Understand the relationship between Data Sources, Import Sets, and Transform Maps. Data Source → loads data into → Import Set Table → transformed by → Transform Map → writes to → Target Table. Be clear on this flow.

6. Pay attention to the Run Business Rules setting. If a question describes a scenario where business rules should or shouldn't fire during import, this checkbox is the answer.

7. Watch for questions about multiple transform maps. Remember that multiple transform maps can exist for a single import set and they execute in order. This is useful for populating multiple tables from one data source.

8. Be familiar with auto-mapping. Know that it matches by name and that it should be reviewed for accuracy. Exam questions may present a scenario where auto-mapping produces incorrect results.

9. Choice Action is a detail that appears on exams. Know the three options (create, reject, ignore) and when each is appropriate.

10. Read questions carefully for keywords. Words like "prevent duplicates" point to coalesce. Words like "before saving" point to onBefore scripts. Words like "after all records" point to onComplete. Words like "skip" or "exclude" point to setting ignore = true.

11. Distinguish between onBefore and onAfter. onBefore runs before the record is written to the target table (you can still modify target values). onAfter runs after the record is saved (the target record has a sys_id, useful for creating related records).

12. Practice in a ServiceNow instance. The best way to internalize these concepts is to perform an actual import: upload a CSV, create a transform map, configure field maps with coalesce, write a simple onBefore script, and observe the results. Hands-on experience solidifies understanding far better than memorization alone.

Summary

Transform Maps and Field Mapping are the core mechanism for moving external data into ServiceNow tables. They provide the rules and logic that bridge raw imported data in staging tables to structured, validated data in production tables. For the CAD exam, focus on understanding coalesce fields, transform event scripts (onStart, onBefore, onAfter, onComplete), the source/target/ignore objects in scripts, the Run Business Rules setting, and the overall data import flow from Data Source through to target table. Mastery of these concepts will prepare you to confidently answer exam questions on this critical topic.