Preventing Inserts and Allowing Updates Only in ServiceNow Transform Maps

ServiceNow transform maps are powerful tools for importing and transforming data from external sources into your ServiceNow instance. A common requirement is to control whether new records are created (inserted) or existing records are updated during the data transformation process. This article addresses a specific scenario: how to configure a transform map to only update existing records and prevent the insertion of new ones. This is crucial for maintaining data integrity and preventing the unintended creation of duplicate or erroneous records in your ServiceNow tables. This guide provides clear, actionable steps to achieve this, ensuring a smooth and controlled data import process.

Let's say you are importing data about configuration items (CIs) into the CMDB (Configuration Management Database) using a transform map. You want to ensure that only existing CIs are updated with the new data, and that no new CIs are created. This is important if you're refreshing data from a trusted source and want to avoid duplicates caused by data entry errors.

The error you are addressing is essentially the unwanted creation of new records when only updates are desired. This typically happens because the transform map, by default, will insert a new record if it doesn't find a matching record based on the coalesce field(s).

Here are two verified methods to prevent the transform map from inserting new records:

Method 1: Utilizing Coalesce Fields

The coalesce field(s) are used to determine if a record already exists. Setting the coalesce attribute to true on a unique field is a good starting point.

1. Identify a Unique Field: Determine a field in your target table that uniquely identifies each record (e.g., serial number, asset tag, or a custom unique identifier). This should be a field that's consistent and reliable in both your source data and ServiceNow.

2. Set Coalesce to True: Navigate to the "Field Maps" related list of your transform map. Locate the field map corresponding to your chosen unique field. Check the "Coalesce" checkbox to set its value to true.

   
   

By setting coalesce to true, ServiceNow will attempt to find a matching record based on the value of this field. If a match is found, the existing record will be updated. However, if no match is found, by default ServiceNow will still insert a new record. This takes us to method 2.

Method 2: Using a Transform Script to Prevent Inserts

To completely prevent inserts, you need to use a transform script that checks the action variable.

1. Navigate to the Transform Map: Open the transform map you want to modify.

2. Access the Run Script Section: Find and click the "Run Script" field (or tab, depending on your ServiceNow version) on the transform map record. This will open the script editor.

3. Add the Following Script: Add the following code snippet to the "Run Script" section:

if (action == 'insert') { ignore = true; }

This script checks the value of the action variable, which indicates whether the transform map is attempting to insert a new record or update an existing one. If action is equal to 'insert', the ignore variable is set to true. This tells ServiceNow to skip the insertion of the current record.

Explanation:

• action: This is a system variable available within transform map scripts that indicates the current operation being performed (either 'insert' or 'update').

• ignore = true;: Setting the ignore variable to true prevents the current record from being processed, effectively stopping the insert operation.

  
  

Example Scenario:

Imagine you're importing employee data from an HR system into ServiceNow. You want to update existing employee records with the latest information, such as department or manager changes. However, you don't want to create new employee records through this import process.

In this case, you would:

1. Set the coalesce attribute to true on the employee_number field (assuming that's a unique identifier for employees in both systems).

2. Implement the transform script mentioned above to prevent any new employee records from being created.

   
   

Alternative Solutions:

While the above methods are the most common, you could also consider:

• Scheduled Jobs with Conditions: Instead of relying solely on the transform map, you could create a scheduled job that runs after the transform map to delete any newly created records based on certain criteria (e.g., records created within the last hour). However, this approach is less efficient and can lead to temporary data inconsistencies.

• Business Rules on the Target Table: You could create a business rule on the target table that prevents inserts. However, this would prevent all inserts, not just those originating from the transform map.

  
  

Controlling record insertion during data transformation in ServiceNow is crucial for data integrity. By leveraging the coalesce functionality in conjunction with a transform script that checks the action variable, you can effectively prevent the insertion of new records and ensure that your transform map only updates existing ones. Remember to choose a unique field for the coalesce attribute, and carefully test your transform map after implementing these changes to verify that it's working as expected. This ensures a cleaner, more controlled, and reliable data import process within your ServiceNow environment.

Next Steps:

1. Identify the appropriate unique field in your target table for coalescing.

2. Implement the transform script in your transform map's "Run Script" section.

3. Thoroughly test your transform map with a small subset of data to confirm that it only updates existing records and does not insert new ones.

4. Monitor the transform map's execution logs for any errors or unexpected behavior.