How to compare the source and target table data in transform map through script?

[vishaljaiswal]

Requirement: Need to import around 8000 records from an external source to ServiceNow table 'cmn_department', where after importing the data, compare source data with all target table records and make all particular records of department false in ServiceNow table which are not present in the source table.

How to achieve this?

[Satishkumar B · Accepted solution]

Hi @vishaljaiswal 

to compare the source and target table data in a transform map and update the cmn_department table accordingly. refer below script for the onComplete section of your transform map

(function runTransformScript(source, map, log, target) {
    // Get all unique department names from the source data
    var sourceDepartments = [];
    var grSource = new GlideRecord('your_import_table_name'); // Replace with your import table name
    grSource.query();
    while (grSource.next()) {
        var departmentName = grSource.getValue('department'); // Replace with the source field name
        if (sourceDepartments.indexOf(departmentName) === -1) {
            sourceDepartments.push(departmentName);
        }
    }

    // Mark departments as inactive in the target table if not present in the source
    var grTarget = new GlideRecord('cmn_department');
    grTarget.query();
    while (grTarget.next()) {
        if (sourceDepartments.indexOf(grTarget.getValue('name')) === -1) { // Assuming 'name' is the field to match
            grTarget.setValue('active', false); // Assuming 'active' is the field to set inactive
            grTarget.update();
        }
    }
})(source, map, log, target);

Steps:

1. Retrieve Source Data: Fetch all department names from the import table.
2. Store Unique Names: Save these names in an array, sourceDepartments.
3. Compare and Update:
   • Query cmn_department records.
   • If a department name isn't in the sourceDepartments array, mark it inactive by setting the active field to false.

Notes:

• Replace 'your_import_table_name' and 'department' with the actual import table and field names.
• Ensure the field names 'name' and 'active' are correct for your cmn_department table.
• Test the script in a safe environment before using it in production.

This script will effectively deactivate records in the cmn_department table that are not present in the source data

……………………………………………………………………………………………………

Please Mark it helpful👍 and Accept Solution✔️!! If this helps you to understand.