How to Show a Popup Message When Some Records Don’t Match During Transform Map Execution

[kurella swamy]

Hi
I have added some custom fields to the cmdb_ci table and want to update these fields using a Transform Map while loading data from an Excel file via the Load Data functionality.

My requirement is:

Custom fields should be updated only when the combination of name, ip_address, and serial_number from the Excel file matches an existing record in the cmdb_ci table.

If no match is found, the record should be skipped, and I want to notify the user after the Transform Map runs that some records did not match and were not updated.

Here’s the onAfter script I’m using in the Transform Map:

(function runTransformScript(source, map, log, target) {
var ci = new GlideRecord('cmdb_ci');
ci.addQuery('name', source.u_name);
ci.addQuery('ip_address', source.u_ip_address);
ci.addQuery('serial_number', source.u_serial_number);
ci.query();

if (ci.next()) {
ci.u_custom_field_1 = source.u_custom_field_1;
ci.u_custom_field_2 = source.u_custom_field_2;
ci.update();
} else {
gs.addInfoMessage("No CI match found for name, IP, and serial number for some records. Please check those records.");
}
})(source, map, log, target);

This script works for updating matched records. However, the gs.addInfoMessage() doesn't show up in a popup message after running the Transform Map. I want to alert the user immediately that some records didn't match.

How can I show a popup message or notification after the transform completes if some records were skipped?

Thanks in advance!

[Mohith Devatte]

@kurella swamy I think it is not possible to do it while you load it as thats OOB process. It can happen only when there is a Custom UI for loading the excel which again reqries lot of coding 

Hope this helps 

Thanks

Mohith Devatte

[Robert H]

Hello @kurella swamy ,

It's not possible to show popups or messages like that after an Import Set transformation completes, because this all happens in the background.

The typical solution for such a requirement would be to send an email notification.

The basic approach would be like this:

1. In the Transform Map, make sure that Coalesce is set to true for the name, ip_address, and serial_number Field Maps. This will ensure that, if no CI with that combination of fields is found the system would try to create a new CI.

2. Still on the Transform Map, create an "onBefore" Transform Script. It will make sure that all attempts to create new CIs will be skipped.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	if (action === 'insert') {
		ignore = true;
	}

})(source, map, log, target);

3. Create an "onComplete" Transform Script. It will look for all those skipped rows and trigger an Event, passing it the list of skipped row numbers. Replace "my.event.name" below with an appropriate name.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    var grRow = new GlideRecord(import_set.getValue('table_name')),
        skippedRows = [];
    grRow.addQuery('sys_import_set', import_set.getUniqueValue());
    grRow.addQuery('sys_import_state', 'ignored');
    grRow.addQuery('sys_import_state_comment', 'Row transform ignored by onBefore script');
    grRow.query();
    while (grRow.next()) {
        skippedRows.push(grRow.getValue('sys_import_row'));
    }

    if (skippedRows.length > 0) {
        gs.eventQueue('your.event.name', import_set,
            'No CI match found for name, IP, and serial number for rows:\n' + skippedRows.join('\n'));
    }

})(source, map, log, target);

Now just create that event with the chosen name via System Policy > Events > Registry. The Table needs to be set to Import Set [sys_import_set]. Finally, create a Notification that gets triggered by that event. You can insert the following in the email body to add the list of skipped rows:

${event.parm1}

Regards,

Robert