Dynamic Coalescing with Transform Map Scripts - ben-vargas/servicenow-wiki GitHub Wiki

This article explains how to implement dynamic coalescing in ServiceNow Transform Maps using a script instead of traditional field mapping. This approach allows you to handle scenarios where the unique key for matching target records can vary based on the data in the source record.

The Challenge

When importing data using Transform Maps, you often need to identify existing records to update rather than create new ones. Coalesce fields help identify these records based on unique key values. However, if the unique identifier is not always on the same field, or a single field, standard field coalescing will not work.

For example, consider a scenario where you're importing user data into the sys_user table. The unique key for a user might be their Active Directory (AD) account name or their employee number, but not both. Standard coalescing on multiple fields could lead to unexpected results, such as creating duplicate records if one of the fields contains incorrect data.

Traditional Coalesce Issues

Setting the coalesce attribute on multiple field maps might seem like an intuitive solution. However, this approach fails because if one coalesce field has a value that does not match, even if another coalesce field has a match, it will cause a new record to be inserted.

The Solution: Scripted Coalesce

The recommended solution is to use a single field map with the following configurations:

  • Target field: Set the target field to Sys ID. This is used as a target field as it is a unique identifier.
  • Source field: Set the source field to Script. This tells the transform map that this value will come from a server side script.
  • Coalesce: Check the Coalesce checkbox.
  • Coalesce empty fields: Check the Coalesce empty fields checkbox.

In the source script, you can then implement the logic for dynamically finding the matching target record by performing a GlideRecord query and returning the sys_id of the target record (if a record is found), otherwise leaving it empty to create a new record. This logic is controlled by a javascript script, which gives you complete control on how a match is made.

Sample Source Script:

(function(source, target, map, log, skip) {
    // Check if we can find a user
    var gr = new GlideRecord("sys_user");
	gr.addQuery("company", source.u_company); //Assumes source has the company value
    if (hasRealValue(source.u_hr_employeeid)) {
        gr.addQuery("employee_number", source.u_hr_employeeid);
    } else {
        if (hasRealValue(source.u_hr_userid)) {
            gr.addQuery("u_ad_account", source.u_hr_userid);
        } else {
            gr.addQuery("sys_id", "NOT_FOUND");
        }
    }
    gr.query();
    if (gr.next()) {
        answer = gr.sys_id;
    }

    function hasRealValue(hItem) {
        return (Boolean(hItem) && hItem != "NULL");
    }
})(source, target, map, log, skip);

Explanation:

  1. function(source, target, map, log, skip) { ... }: The anonymous function that is used as the source script for the transform map, which is passed the source, target, map, log, and skip variables.
  2. var gr = new GlideRecord("sys_user");: Creates a GlideRecord to query the sys_user table.
  3. gr.addQuery("company", source.u_company);: Adds a query to find records with a matching company value.
  4. Conditional Queries:
    • if (hasRealValue(source.u_hr_employeeid)) { gr.addQuery("employee_number", source.u_hr_employeeid); }: If the source record has a value for u_hr_employeeid, it queries for a matching employee_number in the target table.
    • else { if (hasRealValue(source.u_hr_userid)) { gr.addQuery("u_ad_account", source.u_hr_userid); } else { gr.addQuery("sys_id", "NOT_FOUND"); } } : If the source record has no value for u_hr_employeeid, it checks to see if there is a value for u_hr_userid and if so will query for a matching u_ad_account, and if neither value is present it will query using a sys_id value that is not a record in the system which will effectively make the query fail.
  5. gr.query();: Executes the query based on the conditions set.
  6. if (gr.next()) { answer = gr.sys_id; }: If a record is found, the answer variable is set to the sys_id of the matched record. If no record is found, the answer variable is not set.
  7. function hasRealValue(hItem) { ... }: A helper function to check if a value is not empty or "NULL".

How It Works:

  • If a record is found, the transform map will use the returned sys_id to update the existing record.
  • If no record is found (the answer variable remains empty), the transform map will insert a new record.
  • Checking the coalesce empty fields checkbox ensures that if the script returns an empty value a new record will be inserted.

Benefits of this Approach

  • Flexibility: This provides flexibility when a unique record identifier may exist in one or more fields.
  • Accuracy: This prevents the creation of duplicate records due to partial or inaccurate matches.
  • Maintainability: Centralized logic in the script makes it easier to understand and maintain.
  • Dynamic Matching: You can implement any complex logic in the script to determine the record match.

Best Practices

  • Single Coalesce Field: Use a single coalesce field map with a script as its source for complex coalescing scenarios.
  • Target Field: Always set the target field to the Sys ID as this will be the most performant matching logic.
  • Script Comments: Use comments within your script to explain the logic.
  • Error Handling: Implement logging and error handling to identify potential issues during the import process.
  • Testing: Thoroughly test your transform map with different data variations to ensure that it behaves as expected.
  • Performance: The use of a script can have performance implications, if you have a large number of records or a more complex script. Ensure you are testing and monitoring these transform maps.
  • Data Integrity: Validate that your script uses proper logic and the intended fields when querying for a target record, as it is your logic that is determining how to handle the record matching.

When to Use This Method

  • When you need to coalesce on multiple fields, but the match criteria can vary.
  • When you have more complex logic than what is achievable using standard coalesce settings.
  • When you need to check for empty or null values before coalescing.
  • When you need to ensure the user match is accurate based on multiple fields.

Conclusion

This method of using a script within a single coalesce field map for a transform map provides a powerful and flexible way to implement complex coalescing logic. By using a script, you have full control over how records are matched and updated, leading to more accurate and reliable data imports, but also the responsibility for the logic to make that match. Remember to thoroughly test your scripts in a non-production instance before deploying to production.