Use Case: The asset management team would like a user friendly way to submit advance shipping notices from the vendor and be notified of the import results via email.

This solution enables itil users to submit a record producer with an attached excel document which generates a data source and triggers one or many transform maps. Following a successful transform, an email notification is generated to the itil user containing the results of their transforms.

This can be also be used without the record producer to generate an email notification on completion of every transform set run. See the note at the bottom of this article for that modification.

Step 1:

Create a record producer to allow users to submit excel documents to be imported.

Name:

Table name: Data Source [sys_data_source]

Short Description, Description and Variables as needed.

Script: **Populate the << >> variables with the relevant data

var transformMapIDs = "<<sys_id>>";   //Use a comma to specify multiple transform maps
var importSetTableName = '<<table_name>>'; //import set staging table
// Setup data source for attachment
current.name = "<<Data Source Name>> on " + gs.nowDateTime();
current.import_set_table_name = importSetTableName;
current.file_retrieval_method = "Attachment";
current.type = "File";
current.format = "Excel";
current.header_row = 1;
current.sheet_number = 1;
current.insert();

// Process excel file
var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(current);
var ranload = loader.loadImportSetTable(importSetRec, current);
importSetRec.state = "loaded";
importSetRec.update();


// Transform import set
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapIDs);
transformWorker.setBackground(true);
transformWorker.start();

//The script is inserting the data source, prevent the record producer from creating an additional insert
current.setAbortAction(true);

Step 2:

Create a mail script to display the detailed results of any transform history.

As written, the script will not print any rows which have a state of 'ignored' in the import row history.

Name: import_set_rows

Mail Script: **Populate the << >> variables with the relevant data

(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,
    /* Optional EmailOutbound */
    email, /* Optional GlideRecord */ email_action,
    /* Optional GlideRecord */
    event) {

//Query the import set row table for any records, if none don't print anything (see: importRows variable). If records found, print label and build table with results that aren't in a state of ignored.
var grCount = new GlideAggregate('sys_import_set_row');
grCount.addQuery('sys_import_set', current.set);
grCount.addQuery('sys_import_state', '!=', 'ignored');
grCount.addQuery('sys_transform_map', '<<sys_id of transform map>>'); // This line can be commented out to send a notification
grCount.addAggregate('COUNT');
grCount.query();
var importRows = '0';
//Count the number of import rows, if 0 this mail script is not printed.

if (grCount.next()){
importRows = grCount.getAggregate('COUNT');
}
//If import sets rows are found they are printed in an HTML table format to be included in the email notification
    if (importRows > 0) {
        var gr = new GlideRecord('sys_import_set_row');
        gr.addQuery('sys_import_set', current.set);
        gr.addQuery('sys_import_state', '!=', 'ignored');
        gr.query();

        template.print('List of Records imported:');
        var open = String.fromCharCode(60);
        var close = String.fromCharCode(62);
//Building the table
        template.print(open + 'table style="border-collapse: collapse; width: 100%;" border="1"' + close);
        template.print(open + 'tbody' + close);
        template.print(open + 'tr' + close);
        template.print(open + 'td style="width: 16.6667%;"' + close + open + 'strong' + close + 'Created' + open + '/strong' + close + open + '/td' + close);
        template.print(open + 'td style="width: 16.6667%;"' + close + open + 'strong' + close + 'Row' + open + '/strong' + close + open + '/td' + close);
        template.print(open + 'td style="width: 16.6667%;"' + close + open + 'strong' + close + 'State' + open + '/strong' + close + open + '/td' + close);
        template.print(open + 'td style="width: 16.6667%;"' + close + open + 'strong' + close + 'Target Record' + open + '/strong' + close + open + '/td' + close);
        template.print(open + 'td style="width: 16.6667%;"' + close + open + 'strong' + close + 'Error' + open + '/strong' + close + open + '/td' + close);
        template.print(open + 'td style="width: 16.6667%;"' + close + open + 'strong' + close + 'Comment' + open + '/strong' + close + open + '/td' + close);
        template.print(open + '/tr' + close);

//Adding each record found as a row to the table
        while (gr.next()) {
            grCount = new GlideRecord(gr.sys_target_table);
            grCount.get('sys_id', gr.sys_target_sys_id);
            var targetRecord = grCount.getDisplayValue();

            template.print(open + 'tr style="height: 12px;"' + close);
            template.print(open + 'td style="width: 16.6667%; height: 12px;"' + close + open + 'strong' + close + gr.sys_created_on + open + '/strong' + close + open + '/td' + close);
            template.print(open + 'td style="width: 16.6667%; height: 12px;"' + close + open + 'strong' + close + gr.sys_import_row + open + '/strong' + close + open + '/td' + close);
            template.print(open + 'td style="width: 16.6667%; height: 12px;"' + close + open + 'strong' + close + gr.sys_import_state + open + '/strong' + close + open + '/td' + close);
            template.print(open + 'td style="width: 16.6667%; height: 12px;"' + close + open + 'strong' + close + targetRecord + open + '/strong' + close + open + '/td' + close);
            template.print(open + 'td style="width: 16.6667%; height: 12px;"' + close + open + 'strong' + close + gr.sys_row_error + open + '/strong' + close + open + '/td' + close);
            template.print(open + 'td style="width: 16.6667%; height: 12px;"' + close + open + 'strong' + close + gr.sys_import_state_comment + open + '/strong' + close + open + '/td' + close);
            template.print(open + '/tr' + close);

        }

        template.print(open + '/tbody' + close);
        template.print(open + '/table' + close);
    }
})(current, template, email, email_action, event);

Step 3:

Generate a notification to be sent via email:

Name: Post Transform Summary

Table: Transform History [sys_import_set_run]

Send when: Record inserted of updated

Select Updated

When to send:

Set Conditions: State > Changes

State > is one of > Complete, Complete with errors, Cancelled, Did not complete

Transform Map > is > <>

Who will receive: Users/Groups in fields > Created by

What it will contain: Content type: HTML only

Subject: ${sys_transform_map} has been completed with a state of ${state}

Message HTML:

<p>Transform: ${sys_transform_map}</p>
<p>Started: ${sys_created_on}</p>
<p>Completed: ${completed}</p>
<table style="height: 74px; width: 25%; border-collapse: collapse;" border="1">
<tbody>
<tr style="height: 13px;">
<td style="width: 50%; height: 13px;">Total:</td>
<td style="width: 50%; height: 13px;">${total}</td>
</tr>
<tr style="height: 11px;">
<td style="width: 50%; height: 11px;">Inserts:</td>
<td style="width: 50%; height: 11px;">${inserts}</td>
</tr>
<tr style="height: 13px;">
<td style="width: 50%; height: 13px;">Updates:</td>
<td style="width: 50%; height: 13px;">${updates}</td>
</tr>
<tr style="height: 13px;">
<td style="width: 50%; height: 13px;">Ignored:</td>
<td style="width: 50%; height: 13px;">${ignored}</td>
</tr>
<tr style="height: 13px;">
<td style="width: 50%; height: 11px;">Skipped:</td>
<td style="width: 50%; height: 11px;">${skipped}</td>
</tr>
<tr style="height: 13px;">
<td style="width: 50%; height: 13px;">Errors:</td>
<td style="width: 50%; height: 13px;">${errors}</td>
</tr>
</tbody>
</table>
<p><code>${mail_script:import_set_rows}</code></p>

Preview of the notification generated"

Note: You can remove line 11 from the mail script and the 'Transform map is' condition from the notification to generate a notification any time a transform map is run by a user other than the system or admin.