Transform Map Script not working properly

[Michael Galang]

 We have the following requirements for enhancement for Transform map process.  I already draft a script but it is not working as expected. Please see problem occurred below under Script Output Problems. Please also check the attached files that I'm trying to import(Amben Uzo and Morgana Sealman are the name of the managers that are not existing  in sys_user). I would appreciate all the help from the experts. Thank you in advance. 

Requirements:
1. After the records from the import table is transformed into sys_user table, it will check each newly transformed record if there is any null value on the manager field respectively. If there is a null value, it means the user's manager has no record yet in the sys_user table. 

2. If there is, It checks if there’s already an incident created for the missing manager. If there is, it will append each name of the user to the existing incident as long as the user's manager is the same as the missing manager based on the imported file during the transform map.

3. If there’s no existing incident or if the next user's manager is different compared to those that already checked, it creates a new incident requesting to create a record of the missing manager in the description and at the same time appends the next user records to the affected user field, as long as their manager are the same based on the imported file during the transform map. The new incident has its ‘short_description’ field set to 'Missing Manager: ’ followed by the missing manager name from the imported record, and its ‘caller_id’ field based on the first user record whose manager is different compared to those that already checked.

4. Apply these until to the last record of the newly transformed record.

Script Output Problems: 
A. New Incident: 
  1. The caller field has no value. 
  2. The user records keeps on appending affected user field and not stopping. 
  3. It is creating new incident even the user's manager has record already in sys_user table. 

B. Existing Incident
  1. The users' name are not appending to the existing Incident record. 

Please check what is something wrong on the below Script: 

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

    var importSetGr = new GlideRecord('imp_user');

    importSetGr.query();

    var previousManager = '';

    var affectedUsers = [];

    var incGr;

    while (importSetGr.next()) {

        var userGr = new GlideRecord('sys_user');

        userGr.addQuery('name', importSetGr.u_name);

        userGr.query();

        if (userGr.next() && userGr.manager.nil()) {

            // If manager is missing, check for an existing incident

            var existingIncGr = new GlideRecord('incident');

            existingIncGr.addQuery('short_description', 'Missing Manager: ' + importSetGr.u_manager);

            existingIncGr.query();

            if (existingIncGr.next()) {

                // If an incident exists, append the user to the "Affected users" list

                existingIncGr.u_affected_users += ',' + importSetGr.u_name; // Assuming "Affected users" is a comma-separated string

                existingIncGr.update();

            } else if (importSetGr.u_manager != previousManager) {

                // If manager has changed and no incident exists, create a new incident

                if (incGr) {

                    incGr.u_affected_users = affectedUsers.join(','); // Assuming "Affected users" is a comma-separated string

                    incGr.insert();

                }

                incGr = new GlideRecord('incident');

                incGr.initialize();

                incGr.short_description = 'Missing Manager: ' + importSetGr.u_manager;

                incGr.description = 'Please create a record for a Missing Manager: ' + importSetGr.u_manager;

                target.caller_id = importSetGr.u_name; // Set the first user record as the caller

                affectedUsers = [];

                previousManager = importSetGr.u_manager;

            }

            // Append user to the "Affected users" list

            affectedUsers.push(importSetGr.u_name);

        }

    }

    // Insert the last incident

    if (incGr) {

        incGr.u_affected_users = affectedUsers.join(','); // Assuming "Affected users" is a comma-separated string

        incGr.insert();

    }

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

[Peter Bodelier]

Hi @Michael Galang 

Try it like this:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
var importSetGr = new GlideRecord('imp_user');
importSetGr.query();

var previousManager = '';
var affectedUsers = [];
var incGr;

while (importSetGr.next()) {
	var userGr = new GlideRecord('sys_user');
	userGr.addQuery('name', importSetGr.u_name);
	userGr.addNotNullQuery('manager');
	userGr.query();

	if (userGr.next()) {
		// If manager is missing, check for an existing incident
		var existingIncGr = new GlideRecord('incident');
		existingIncGr.addQuery('short_description', 'Missing Manager: ' + importSetGr.u_manager);
		existingIncGr.query();

		if (existingIncGr.next()) {
			// If an incident exists, append the user to the "Affected users" list
			existingIncGr.u_affected_users += ',' + importSetGr.u_name; // Assuming "Affected users" is a comma-separated string
			existingIncGr.update();
		} else {
			// If no incident exists, create a new incident
			incGr = new GlideRecord('incident');
			incGr.initialize();
			incGr.short_description = 'Missing Manager: ' + importSetGr.u_manager;
			incGr.u_affected_users = importSetGr.u_name; // Assuming "Affected users" is a comma-separated string
			incGr.description = 'Please create a record for a Missing Manager: ' + importSetGr.u_manager;
			incGr.caller_id = importSetGr.u_name; // Set the first user record as the caller
			incGr.insert();
		}
	}
}
})(source, map, log, target);

[Michael Galang]

Hi Peter, 

Thank you for your reply. I tried the use the script but the result this time no Incident ticket was generated. I would appreciate alternative solution. Thanks 

[Peter Bodelier · Accepted solution]

Hi @Michael Galang,

Ah yes, I see I made a mistake in the script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
var importSetGr = new GlideRecord('imp_user');
importSetGr.query();

var previousManager = '';
var affectedUsers = [];
var incGr;

while (importSetGr.next()) {
	var userGr = new GlideRecord('sys_user');
	userGr.addQuery('name', importSetGr.u_name);
	userGr.addNullQuery('manager');
	userGr.query();

	if (userGr.next()) {
		// If manager is missing, check for an existing incident
		var existingIncGr = new GlideRecord('incident');
		existingIncGr.addQuery('short_description', 'Missing Manager: ' + importSetGr.u_manager);
		existingIncGr.query();

		if (existingIncGr.next()) {
			// If an incident exists, append the user to the "Affected users" list
			existingIncGr.u_affected_users += ',' + importSetGr.u_name; // Assuming "Affected users" is a comma-separated string
			existingIncGr.update();
		} else {
			// If no incident exists, create a new incident
			incGr = new GlideRecord('incident');
			incGr.initialize();
			incGr.short_description = 'Missing Manager: ' + importSetGr.u_manager;
			incGr.u_affected_users = importSetGr.u_name; // Assuming "Affected users" is a comma-separated string
			incGr.description = 'Please create a record for a Missing Manager: ' + importSetGr.u_manager;
			incGr.caller_id = importSetGr.u_name; // Set the first user record as the caller
			incGr.insert();
		}
	}
}
})(source, map, log, target);

[Michael Galang]

It works now. Thank you Peter for your help. 😊