Bulk data upload with import set from excel

[ektamestri]

Hi,

I need to update bulk data which is present in spreadsheet.  Here, I am updating existing incidents (major incidents), in which I also need to upload data related to affected locations, impacted services etc.  I have created a transform map for affected locations separately because affected locations (task_location) which is present on incident table as related list.   I need to update multiple locations per task in task_location for which I have crated onAfter transform script and I have only mapped incident number to task.   How should I go about this scenario.  I am pasting onAfter script as well.  Can you please give any suggestions.

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

//GET INCIDENT SYS_ID

        var incGR = new GlideRecord('incident');

        incGR.addQuery('number', source.u_incident_number);

        //incGR.setLimit(1);

        incGR.query();

        if (!incGR.next()) {

            log.error('Incident not found: ' + source.u_incident_number);

            return;

        }

        //split multiple locations

        var locations = source.u_affected_locations.toString().split(',');

        for (var i = 0; i < locations.length; i++) {

            var locName = locations[i].trim();

            if (!locName) continue;

        }

        //find location record

        var locGR = new GlideRecord('cmn_location');

        locGR.addQuery('name', locName);

        locGR.query();

        if (locGR.next()) {

            var checkGR = new GlideRecord('task_location');

            checkGR.addQuery('task', incGR.sys_id);

            checkGR.addQuery('location', locGR.sys_id);

            checkGR.query();

        }

        if (!checkGR.next())

        {

            var taskLocGR = new GlideRecord('task_location');

            taskLocGR.initialize();

            taskLocGR.task = incGR.sys_id;

            taskLocGR.location = locGR.sys_id;

            taskLocGR.insert();

    } else {

        log.error("Location not Found: " + locName);

    }

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

[MAHAMKALI · Accepted solution]

Hi @ektamestri
In your onAfter Script, the for loop ends after  

 if (!locName) continue;

that is why only one location is getting created. the for loop should end after 

else {
        log.error("Location not Found: " + locName);
    }

Please mark it helpful, if it helps.

Regards,

Kali

[Ankur Bawiskar · Accepted solution]

@ektamestri 

here is your updated script, your script had issues with the loop

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


    //GET INCIDENT SYS_ID

    var incGR = new GlideRecord('incident');
    incGR.addQuery('number', source.u_incident_number);
    //incGR.setLimit(1);
    incGR.query();

    if (!incGR.next()) {
        log.error('Incident not found: ' + source.u_incident_number);
        return;
    }
    //split multiple locations

    var locations = source.u_affected_locations.toString().split(',');
    for (var i = 0; i < locations.length; i++) {

        var locName = locations[i].trim();
        if (!locName) continue;
        //find location record

        var locGR = new GlideRecord('cmn_location');
        locGR.addQuery('name', locName);
        locGR.query();
        if (locGR.next()) {
            var checkGR = new GlideRecord('task_location');
            checkGR.addQuery('task', incGR.sys_id);
            checkGR.addQuery('location', locGR.sys_id);
            checkGR.query();
            if (!checkGR.hasNext()) {
                var taskLocGR = new GlideRecord('task_location');
                taskLocGR.initialize();
                taskLocGR.task = incGR.sys_id;
                taskLocGR.location = locGR.sys_id;
                taskLocGR.insert();
            }
        } else {
            log.error("Location not Found: " + locName);
        }
    }

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

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[vaishali231 · Accepted solution]

hey @ektamestri 

try this : 

(function runTransformScript(source, map, log, target) {

    // Get Incident
    var incGR = new GlideRecord('incident');
    incGR.addQuery('number', source.u_incident_number);
    incGR.query();

    if (!incGR.next()) {
        log.error('Incident not found: ' + source.u_incident_number);
        return;
    }

    // Split locations
    var locations = source.u_affected_locations.toString().split(',');

    for (var i = 0; i < locations.length; i++) {

        var locName = locations[i].trim();
        if (!locName) continue;

        // Find location
        var locGR = new GlideRecord('cmn_location');
        locGR.addQuery('name', locName);
        locGR.query();

        if (locGR.next()) {

            // Check if already exists
            var checkGR = new GlideRecord('task_location');
            checkGR.addQuery('task', incGR.sys_id);
            checkGR.addQuery('location', locGR.sys_id);
            checkGR.query();

            if (!checkGR.next()) {

                var taskLocGR = new GlideRecord('task_location');
                taskLocGR.initialize();
                taskLocGR.task = incGR.sys_id;
                taskLocGR.location = locGR.sys_id;
                taskLocGR.insert();

            }

        } else {
            log.error("Location not found: " + locName);
        }
    }

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

*************************************************************************************************************************************

If this response helps, please mark it as Accept as Solution and Helpful.
Doing so helps others in the community and encourages me to keep contributing.
Regards
Vaishali Singh

[MAHAMKALI · Accepted solution]

Hi @ektamestri
In your onAfter Script, the for loop ends after  

 if (!locName) continue;

that is why only one location is getting created. the for loop should end after 

else {
        log.error("Location not Found: " + locName);
    }

Please mark it helpful, if it helps.

Regards,

Kali

[Ankur Bawiskar · Accepted solution]

@ektamestri 

here is your updated script, your script had issues with the loop

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


    //GET INCIDENT SYS_ID

    var incGR = new GlideRecord('incident');
    incGR.addQuery('number', source.u_incident_number);
    //incGR.setLimit(1);
    incGR.query();

    if (!incGR.next()) {
        log.error('Incident not found: ' + source.u_incident_number);
        return;
    }
    //split multiple locations

    var locations = source.u_affected_locations.toString().split(',');
    for (var i = 0; i < locations.length; i++) {

        var locName = locations[i].trim();
        if (!locName) continue;
        //find location record

        var locGR = new GlideRecord('cmn_location');
        locGR.addQuery('name', locName);
        locGR.query();
        if (locGR.next()) {
            var checkGR = new GlideRecord('task_location');
            checkGR.addQuery('task', incGR.sys_id);
            checkGR.addQuery('location', locGR.sys_id);
            checkGR.query();
            if (!checkGR.hasNext()) {
                var taskLocGR = new GlideRecord('task_location');
                taskLocGR.initialize();
                taskLocGR.task = incGR.sys_id;
                taskLocGR.location = locGR.sys_id;
                taskLocGR.insert();
            }
        } else {
            log.error("Location not Found: " + locName);
        }
    }

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

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[vaishali231 · Accepted solution]

hey @ektamestri 

try this : 

(function runTransformScript(source, map, log, target) {

    // Get Incident
    var incGR = new GlideRecord('incident');
    incGR.addQuery('number', source.u_incident_number);
    incGR.query();

    if (!incGR.next()) {
        log.error('Incident not found: ' + source.u_incident_number);
        return;
    }

    // Split locations
    var locations = source.u_affected_locations.toString().split(',');

    for (var i = 0; i < locations.length; i++) {

        var locName = locations[i].trim();
        if (!locName) continue;

        // Find location
        var locGR = new GlideRecord('cmn_location');
        locGR.addQuery('name', locName);
        locGR.query();

        if (locGR.next()) {

            // Check if already exists
            var checkGR = new GlideRecord('task_location');
            checkGR.addQuery('task', incGR.sys_id);
            checkGR.addQuery('location', locGR.sys_id);
            checkGR.query();

            if (!checkGR.next()) {

                var taskLocGR = new GlideRecord('task_location');
                taskLocGR.initialize();
                taskLocGR.task = incGR.sys_id;
                taskLocGR.location = locGR.sys_id;
                taskLocGR.insert();

            }

        } else {
            log.error("Location not found: " + locName);
        }
    }

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

*************************************************************************************************************************************

If this response helps, please mark it as Accept as Solution and Helpful.
Doing so helps others in the community and encourages me to keep contributing.
Regards
Vaishali Singh

[ektamestri · Accepted solution]

Thank you everyone.  Also, onAfter did not work, I used run script for this scenario and it worked fine