Import data via a Record producer and generate a notification with detailed results

[Brian24]

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: <Choose  a 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 > <<Transform Map>>

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.

[mballinger]

Used this without the mail script and notification and it worked perfect. Thanks!

[Maloy Banerjee]

Hi Brian,

Thank you for sharing the article. It is very helpful. I have one question.

Instead of sharing the table in the email notification, will it be possible to share the link to the list view of the target table with the updated records. As it might be a drawback if the count of the updated records is large like 100s of records. E.g. as given below:

https://dev71910.service-now.com/cmdb_ci_business_app_list.do?sysparm_query=nameINServiceNow%2CChlor...

We'll state - "Click here to see the updated records".

If yes, then how can we provide the link?

Regards,

Maloy Banerjee

[Maloy Banerjee]

Hi,

I found the solution. Below is the code I wrote in the Email Script:

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

          // Add your code here
	var arry = [];
var str = '';
var gr = new GlideRecord('sys_import_set_row');
        gr.addQuery('sys_import_set', current.set);
        gr.addQuery('sys_import_state', '!=', 'ignored');
        gr.query();
	while(gr.next())
{
arry.push(gr.sys_target_sys_id.toString());
}

}
var link = '/fm_expense_line_list.do?sysparm_query=sys_idIN' + arry;
	var url = '<br/><a href="' + link + '">Take Me to the Expense Line</a>'; 
    template.print(url);

})(current, template, email, email_action, event);

Regards,

Maloy

[stacybentley]

Hi Brian, 

Thank you for sharing this article regarding emailing import logs. It is very helpful. I have one question: can this work for an inbound email action instead of a record producer?

Thank you,

Stacy Bentley

[James Bouchard]

Has anyone been able to get this piece to work?  For me, it brings up a Form to enter the data, but doesn't import the data to the staging table.  

Step 1:

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

Name: <Choose  a name>

Table name: Data Source [sys_data_source]

Short Description, Description and Variables as needed.

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

[Prathamjeet Sin]

Thank you for this great article. I have two questions on this
1. Is there a way to delete the data source after the transformation?

2. Also, the "created by" field in the import set run table is a string which cannot be directly used in notification(who will receive). Please let me know if I misunderstood something on that part. 

[shivani42]

Hi All,

I am working on the similar task. is there any possibility now, to show the error logs to the user when they click on submit button while transforming the data to target table using the record producer. I tried to add the code in record producer script itself. but i feel there is some timing issue it is not showing the logs in info message. This is my script.

var transformsysid = 'XXXXXXXXXXXXXXXXXXXXXXX'; 

current.name = gs.getUserName() + " User Import at " + new GlideDateTime();

current.import_set_table_name = 'u_import_test';

current.file_retrieval_method = 'Attachment';

current.tyoe = 'File'; current.format = 'Excel';

current.header_row = 1;

current.sheet_number = 1;

current.insert();

var loader = new GlideImportSetLoader();

var importsetrec = loader.getImportSetGr(current);

var ranload = loader.loadImportSetTable(importsetrec, current);

importsetrec.state = "loaded";

importsetrec.update();

var setsysid = importsetrec.sys_id;

gs.addInfoMessage(importsetrec.sys_id);

var Transformworker = new GlideImportSetTransformerWorker(importsetrec.sys_id, transformsysid); Transformworker.setBackground(true);

Transformworker.start();

gs.addInfoMessage("hi");

current.setAbortAction(true);

var gg = new GlideRecord('import_log');

gg.addQuery('import_set', setsysid);

gg.query();

if(gg.next()){

var error = gg.message; 

gs.Info("hello sys id of import set is " + gg.import_set);

}

I am trying to import the data for user allocation table.