Notifications

1907 views

Locating duplicate variables for a requested item (RITM) 




Overview

When using a many-to-many relationship, finding and locating variables in the system can become an intricate task. Use the script below to locate duplicate variables for a specific requested item (RITM).

To run the script, navigate to the System Definition > Scripts Background module and copy the sample script into the script field using the sys_id of the RITM. Set the hideSysID and debugOn variables to true or false to determine if debug is active and to display/hide the output for the script. You can also use this script to locate all the variables. To do so, the debugOn variable must be set to true.

 


Script

//@author Lawrence Tse
// FInding Duplicate Variables for RITM
// Have to fill in the below 3 variable

var ritmSysID='0d2b4dcab1fb3800ce38de7ebbabfef9';      // Input Sys ID of RITM
var hideSysID = false;                                 // Input boolean to Hide sys id or not

var debugOn = true;                                    // turn Debug On or Off

//============================================
var message;
var dupArray = new Array();
var RITMName ="";

// Find all variables for the RITM
v = new GlideRecord('sc_item_option_mtom');
v.addQuery('request_item', ritmSysID);
v.orderBy('sc_item_option.order');
v.query();

var varInfoOut = "\n\n <b>---==== SHOWING ALL VARIABLES BELOW (DEBUG ON)====---</b>\n\n";
var dupOut = "\n\n <b>---==== Duplicate Source (DEBUG ON)====---</b>\n\n";
var resultOut = "\n\n<b> ---==== DUPLICATES BELOW FOR "
var sqlOut ="\n\n<b> ---==== SQL Statement ====--- </b>\n\n<font color=red><b><< WARNING >></b></font> Do not delete everything below but only the duplicate of each set. Usually the lower order one. \n\n";

// Loop thru array and print out Links to easy access to records
while(v.next()) {

RITMName = v.request_item.number;
var eOut = "<a href='/" + 'sc_item_option' + ".do?sys_id=" + v.sc_item_option + "'>Link</a> ";
var cOut = "<a href='/" + 'item_option_new' + ".do?sys_id=" + v.sc_item_option.item_option_new.sys_id + "'>Link</a> ";

dupArray.push( v.sc_item_option.item_option_new.sys_id );


if (hideSysID ) {
varInfoOut = varInfoOut + "|| Order :" + v.sc_item_option.order +" || Variable : " + eOut + " || Variable Source: " +cOut +" || Question : " + v.sc_item_option.item_option_new.question_text + "\n" ;
}
else {
varInfoOut = varInfoOut + "|| Order :" + v.sc_item_option.order +" || Variable : " + v.sc_item_option.sys_id + " " + eOut + " || Variable Source: " + v.sc_item_option.item_option_new.sys_id + " " +cOut +" || Question : " + v.sc_item_option.item_option_new.question_text + "\n";
}

}


// Sort the Array
dupArray = dupArray.sort();

// Print Array
for (var i=0; i<dupArray.length; i++) {
// gs.print( "Array [" + i + "] = " + dupArray[i] );
};

// Find Duplicate
var dupliate = [];
for (var i = 0; i < dupArray.length - 1; i++) {
if (dupArray[i + 1] == dupArray[i]) {
dupliate.push(dupArray[i]);
}
}

// Check if Duplicate is found, if so do all the things below, if not, just go to else and print "No Duplicate Found"
if( dupliate.length > 0 ) {

// Setting Print Duplicate
for (var i=0; i<dupliate.length; i++) {
dupOut = dupOut + "Duplicate [" + i + "] = " + dupliate[i] + "\n";
};

// Glide Record Query to print LINKS for duplicates

v = new GlideRecord('sc_item_option_mtom');
v.addQuery('request_item', ritmSysID);
v.orderBy('sc_item_option.item_option_new.sys_id');
v.orderBy('sc_item_option.order');
v.query();

var rOut = "<a href='/" + 'sc_req_item' + ".do?sys_id=" + RITMName + "'>";
resultOut= resultOut + rOut + RITMName + "</a></b> ====---\n\n";

// Creating the Delete SQL statement for easy copy and paste into SQL
while(v.next()) {

var eOut = "<a href='/" + 'sc_item_option' + ".do?sys_id=" + v.sc_item_option + "'>Link</a> ";
var cOut = "<a href='/" + 'item_option_new' + ".do?sys_id=" + v.sc_item_option.item_option_new.sys_id + "'>Link</a> ";

for (var i=0; i<dupliate.length; i++) {
if( dupliate[i] == v.sc_item_option.item_option_new.sys_id ) {

if (hideSysID) {
resultOut = resultOut + "|| Order :" + v.sc_item_option.order +" || Variable : " + eOut + " || Variable Source: " +cOut +" || Question : " + v.sc_item_option.item_option_new.question_text + "\n" ;
}
else {
resultOut = resultOut + "|| Order :" + v.sc_item_option.order +" || Variable : " + v.sc_item_option.sys_id + " " + eOut + " || Variable Source: " + v.sc_item_option.item_option_new.sys_id + " " +cOut +" || Question : " + v.sc_item_option.item_option_new.question_text + "\n" ;
}
sqlOut = sqlOut + "DELETE FROM sc_item_option_mtom WHERE sc_item_option='" + v.sc_item_option.sys_id + "'; // Order " + v.sc_item_option.order +"\n";
}
}
}

// Print information to the screen
if( debugOn ) {
gs.print(varInfoOut);
gs.print(dupOut);
}
gs.print(resultOut);
gs.print( sqlOut );

}
else {
// Print information to the screen
gs.print("<h3><font color=purple> NO DUPLICATE FOUND</font></h3>");
gs.print(varInfoOut);
}

Article Information

Last Updated:2016-03-15 09:24:39
Published:2013-04-16