ServiceNow Data Tranformation helper script

readme.md

ClassName: DataTransformRecord

The DataTransformRecord class facilitates the handling of data transformations in ServiceNow by managing data source records and transformations. It provides methods to create, retrieve, copy, manipulate data sources, manage attachments, perform data imports, and clean up post-import tasks.

copyAtt

Copies attachments from one record to another specified data source.

Parameters:

Name	Type	Required	Description
source	GlideRecord	Yes	The source record from which to copy attachments.

Returns:

Type	Description
DataTransformRecord	Returns 'this' for method chaining.

Example:

// Example usage of copyAtt
var transformRecord = new DataTransformRecord();
transformRecord.getDataSourceByName("Source DataSource")
   .copyAtt(sourceRecord);

getMapBySysID

Retrieves a transform map record by its sys_id.

Parameters:

Name	Type	Required	Description
sysid	String	Yes	The sys_id of the transform map to retrieve.

Returns:

Type	Description
DataTransformRecord	Returns 'this' to allow for method chaining.

Example:

// Example usage of getMapBySysID
var transformRecord = new DataTransformRecord();
transformRecord.getMapBySysID('12345abcdef12345abcdefabcd6789');

getMapByName

Retrieves a Transform Map by its name.

Parameters:

Name	Type	Required	Description
name	String	Yes	The name of the Transform Map.

Returns:

Type	Description
DataTransformRecord	Returns 'this' for method chaining.

Example:

// Example usage of getMapByName
var transformRecord = new DataTransformRecord();
transformRecord.getMapByName("Sample Transform Map");

loadData

Initiates data loading from the specified data source into the import set table.

Parameters:

Name	Type	Required	Description
sourceGR	GlideRecord	No	The GlideRecord of the data source. By default, uses the data source set during initialization.

Returns:

Type	Description
DataTransformRecord	Returns 'this' for method chaining.

Example:

// Example usage of loadData
var transformRecord = new DataTransformRecord();
transformRecord.getDataSourceByName("Load DataSource").loadData();

importData

Applies the transform map to the loaded data, performing the import.

Parameters:

Name	Type	Required	Description
grDataSource	GlideRecord	No	The data source GlideRecord, default to current.
grTransformMap	GlideRecord	No	The transform map GlideRecord, default to current.

Returns:

Type	Description
DataTransformRecord	Returns 'this' for method chaining.

Example:

// Example usage of importData
var transformRecord = new DataTransformRecord();
transformRecord.getDataSourceByName("DataSource")
    .getMapByName("Transform Map")
    .loadData()
    .importData();

cleanUpImport

Performs cleanup by deleting temporary import sets and data sources created during the import process.

Parameters:

None

Returns:

Type	Description
DataTransformRecord	Returns 'this' for method chaining.

Example:

// Example usage of cleanUpImport
var transformRecord = new DataTransformRecord();
transformRecord.getDataSourceByName("DataSource")
    .getMapByName("Transform Map")
    .loadData()
    .importData()
    .cleanUpImport();

log

Logs all status updates and actions taken during the usage of the DataTransformRecord.

Parameters:

None

Returns:

Type	Description
Void	No return value.

Example:

// Example usage of log
var transformRecord = new DataTransformRecord();
transformRecord.log();

changeLog.md

Changes

2023-06-23: I decided to rename this as it is more of an object class and less of a utils script include. When I have time I have plans to refactor some more. If I can ever find the update set, their are a set of flow designer actions that compliment this.

DataTransformRecord.js

/**
 * DataTransformRecord Class
 * Manages the lifecycle of data transformation in ServiceNow by automating the creation, manipulation,
 * and cleanup of data sources and associated transform maps.
 *
 * @class
 */
var DataTransformRecord = function() {
    var datasource;
    var map;
    var importSetGr;
    var imported = false;
    var loaded = false;
    var newDS = false;
    var copiedDS = false;
    var status = [];

    /**
     * Creates a new data source based on a specified map of field-value pairs.
     * @param {object} dataSourceMap - An object containing data source fields and values.
     * @returns {DataTransformRecord} Returns this for chaining.
     * @throws Will throw an error if dataSourceMap is not an object.
     */
    function createDataSource(dataSourceMap) {
        if (typeof dataSourceMap !== 'object' || dataSourceMap === null) {
            throw new Error('dataSourceMap variable must be an object containing field name and value pairs');
        }
        var grDS = new GlideRecord('sys_data_source');
        for (var key in dataSourceMap) {
            if (dataSourceMap.hasOwnProperty(key)) {
                grDS.setValue(key, dataSourceMap[key]);
            }
        }
        grDS.name += _getTimeStamp();
        grDS.insert();
        datasource = grDS;
        newDS = true;
        _updateStatus("Created DataSource - " + datasource.name);
        return this;
    }

    /**
     * Retrieves a DataSource record by its sys_id.
     * @param {string} sysid - The sys_id of the DataSource to retrieve.
     * @returns {DataTransformRecord} Returns this for chaining.
     */
    function getDataSourceBySysID(sysid) {
        datasource = _getRecord('sys_data_source', sysid);
        _updateStatus("Retrieved DataSource - " + datasource.name);
        return this;
    }

    /**
     * Retrieves a DataSource record by its name.
     * @param {string} name - The name of the DataSource.
     * @returns {DataTransformRecord} Returns this for chaining.
     * @throws Will throw an error if the name is empty.
     */
    function getDataSourceByName(name) {
        if (!name) {
            throw new Error("DataSource name must not be empty");
        }
        datasource = _qryRecord('sys_data_source', [{ field: "name", value: name }]);
        _updateStatus("Retrieved DataSource - " + datasource.name);
        return this;
    }

    /**
     * Retrieves a Transform Map record by its sys_id.
     * @param {string} sysid - The sys_id of the Transform Map to retrieve.
     * @returns {DataTransformRecord} Returns this for chaining.
     * @throws Will throw an error if sysid is empty or invalid.
     */
    function getMapBySysID(sysid) {
        map = _getRecord('sys_transform_map', sysid);
        _updateStatus("Retrieved Transform Map - " + map.name);
        return this;
    }

    /**
     * Retrieves a Transform Map by its name.
     * @param {string} name - The name of the Transform Map.
     * @returns {DataTransformRecord} Returns this for chaining.
     * @throws Will throw an error if the name is empty.
     */
    function getMapByName(name) {
        if (!name) {
            throw new Error("Transform Map name must not be empty");
        }
        map = _qryRecord('sys_transform_map', [{ field: "name", value: name }]);
        _updateStatus("Retrieved Transform Map - " + map.name);
        return this;
    }

    /**
     * Copies attachments from a source record to the current datasource.
     * @param {GlideRecord} source - The source record from which to copy attachments.
     * @returns {DataTransformRecord} Returns this for chaining.
     * @throws Will throw an error if source or datasource are invalid or not set.
     */
    function copyAtt(source) {
        if (!source || !datasource) {
            throw new Error("Source record and DataSource must be set and valid");
        }
        new GlideSysAttachment().copy(source.getTableName(), source.getUniqueValue(), datasource.getTableName(), datasource.getUniqueValue());
        _updateStatus("Copied attachments from " + source.getDisplayValue() + " to " + datasource.getDisplayValue());
        return this;
    }

    /**
     * Loads data from the datasource into the import set table.
     * @returns {DataTransformRecord} Returns this for chaining.
     * @throws Will throw an error if datasource is not set or data loading fails.
     */
    function loadData() {
        if (!datasource) {
            throw new Error("DataSource must be set before loading data");
        }
        var loader = new GlideImportSetLoader();
        importSetGr = loader.getImportSetGr(datasource);
        var ranLoad = loader.loadImportSetTable(importSetGr, datasource);
        if (!ranLoad) {
            throw new Error("Failed to load data for DataSource - " + datasource.name);
        }
        loaded = true;
        _updateStatus("Loaded data using DataSource - " + datasource.name);
        return this;
    }

    /**
     * Transforms data using the specified transform map.
     * @returns {DataTransformRecord} Returns this for chaining.
     * @throws Will throw an error if data has not been loaded or Transform Map is not set.
     */
    function importData() {
        if (!loaded || !map) {
            throw new Error("Data must be loaded and Transform Map must be set before importing data");
        }
        var transformer = new GlideImportSetTransformer();
        transformer.setMapID(map.getUniqueValue());
        transformer.transformAllMaps(importSetGr);
        imported = true;
        _updateStatus("Data imported using Transform Map - " + map.name);
        return this;
    }

    /**
     * Cleans up temporary resources created during the data import process.
     * @returns {DataTransformRecord} Returns this for chaining.
     */
    function cleanUp() {
        if (newDS || copiedDS) {
            datasource.deleteRecord();
            _updateStatus("Deleted temporary DataSource - " + datasource.name);
        }
        if (imported) {
            var cleaner = new GlideImportSetCleaner(importSetGr.getTableName());
            cleaner.cleanUp();
            _updateStatus("Cleaned up Import Set");
        }
        return this;
    }

    /**
     * Logs all status updates and actions taken during the usage of the DataTransformRecord to the system log.
     * @returns {void}
     */
    function log() {
        gs.info(status.join("\n"), "DataTransformRecord Logs");
    }

    /**
     * Internal method to update statuses with timestamp.
     * @param {string} msg - Message to log.
     * @private
     */
    function _updateStatus(msg) {
        var timeStamp = new GlideDateTime().getByFormat("yyyy-MM-dd HH:mm:ss");
        status.push(timeStamp + ": " + msg);
    }

    /**
     * Internal method to get a record by sys_id from a specified table.
     * @param {string} tbl - Table name from which to retrieve the record.
     * @param {string} sysid - sys_id of the record to retrieve.
     * @returns {GlideRecord} Returns the GlideRecord of the retrieved record.
     * @throws Will throw an error if the table name or sys_id is empty or the record does not exist.
     * @private
     */
    function _getRecord(tbl, sysid) {
        if (!tbl || !sysid) {
            throw new Error("Table name and sys_id must not be empty");
        }
        var gr = new GlideRecord(tbl);
        if (!gr.get(sysid)) {
            throw new Error("No record found with sys_id: " + sysid + " in table: " + tbl);
        }
        return gr;
    }

    /**
     * Internal method to query a record based on provided criteria.
     * @param {string} tbl - Table from which to query the record.
     * @param {Array} criteria - Array of objects containing field, value, and optional operator.
     * @returns {GlideRecord} Returns the queried GlideRecord.
     * @throws Will throw an error if the query criteria are not met or the record does not exist.
     * @private
     */
    function _qryRecord(tbl, criteria) {
        if (!tbl || !criteria || !Array.isArray(criteria)) {
            throw new Error("Table name and criteria array must be provided and valid");
        }
        var record = new GlideRecord(tbl);
        criteria.forEach(function(crit) {
            record.addQuery(crit.field, crit.operator || "=", crit.value);
        });
        record.query();
        if (!record.next()) {
            throw new Error("No record found with the provided criteria in table: " + tbl);
        }
        return record;
    }

    return {
        createDataSource,
        getDataSourceBySysID,
        getDataSourceByName,
        getMapBySysID,
        getMapByName,
        copyAtt,
        loadData,
        importData,
        cleanUp,
        log
    };
};