Merge Duplicates

General Add comments
by:

Sometime ago I migrated data from one ServiceNow instance to another. Both instances had company and user data, which were not in sync with each other and the new instance ended up with a number of duplicate companies. Manually copying fields and references from one company to another is not very efficient. There has to be an easier way.
This is an example of how you can merge two company records with a UI action together with a GlideDialogWindow and a Script Include. I have made the setup as generic as I possibly could, so that it can be used on other tables without any/much customizations.

Example merge 2 companies

The UI Action calls a UI page and the UI page calls a script include, which is doing most of the work.The following data is copied from the record on which you trigger the UI Action (source) to the record, which you select in the GlideDialogWindow (target):

  • Fields (excluding system fields and empty fields)
  • References to the source record
  • Attachments
  • Field attachments

You can set a parameter to copy source fields to the corresponding target field when the target field is not empty. Default target fields, which are not empty, are not overwritten.
The UI action also does not delete the duplicate record (source), but sets the record to inactive if possible.

Script:
[cc lang=”javascript”]
function mergeDialog() {
//Get the values to pass into the dialog
var tableName = g_form.getTableName();
var sysID = g_form.getUniqueValue();
var tableLabel = getTableLabel();

//Initialize and open the Dialog Window
var dialog = new GlideDialogWindow(“merge_dialog”); //Render the dialog containing the UI Page ‘merge_dialog’
dialog.setTitle(“Merge ” + tableLabel); //Set the dialog title
dialog.setSize(600,400); //Set the dialog size
dialog.setPreference(“tableName”, tableName); //Pass in the table name for use in the dialog
dialog.setPreference(“sysID”, sysID); //Pass in sys_id for use in the dialog
dialog.setPreference(“viewName”, tableLabel); //Pass in view name for use in the dialog
dialog.render(); //Open the dialog
}

function getTableLabel() {
//Get the display name of the table from the form
var x = gel(‘sysverb_view_change’);
if (x) {
if (x.nextSibling) {
if (x.nextSibling.className == ‘pointerhand’) {
var tableLabel = x.nextSibling.innerHTML;
tableLabel = tableLabel.replace(/<[^>]+>| |\[[^\]]+]/g,””);
return tableLabel;
}
}
}
return “Unknown”;
}
[/cc]

UI Page


Client script:
[cc lang=”javascript”]
function validateTarget() {
//Gets called if the ‘OK’ dialog button is clicked
//Make sure targetID is not empty
var targetID = gel(“QUERY:sys_id!=” + gel(“sourceID”).value).value;
if (targetID == “”) {
//If targetID is empty stop submission
alert(“Please provide a ” + gel(“viewName”).value + ” to submit the dialog.”);
return false;
} else {
var x = confirm(“Are you sure you want to merge both companies!”);
if (!x) {
return onCancel();
}
}
GlideDialogWindow.get().destroy(); //Close the dialog window
return true;
}

function onCancel() {
var c = gel(‘cancelled’);
c.value = “true”;
GlideDialogWindow.get().destroy(); //Close the dialog window
return false;
}
[/cc]
HTML:
[cc lang=”javascript”]










Select the ${jvar_viewName} of which the current ${jvar_viewName} is a duplicate. After you click OK:

  • all fields, excluding system fields, are copied to the ${jvar_viewName} you have selected
  • all references to the current ${jvar_viewName} are moved to the ${jvar_viewName} you have selected
  • all attachments, including attachment fields, are moved to the ${jvar_viewName} you have selected




[/cc]
Processing script:
[cc lang=”javascript”]
if (cancelled == “false”) {
mergeSourceWithTarget();
}
//response.sendRedirect(tableName + “.do?sysparm_query=sys_id=” + sourceID);
var urlOnStack = Packages.com.glide.sys.GlideSession.get().getStack().bottom();
response.sendRedirect(urlOnStack);

function mergeSourceWithTarget() {
var targetID = request.getParameter(“QUERY:sys_id!=” + sourceID);
var merge = new MergeUtils(tableName);
merge.sourceWithTarget(sourceID,targetID);
}
[/cc]

Script include


The script include, MergeUtils uses 2 parameters:
1. System name of the table (mandatory).
2. Debug script include (default false). Set this property to true, if you want the script include writing debug messages to the system log.
You can use the methods in the script include separately, but the sourceWithTarget is the main method, which merges all data from source record to target record. The method uses 3 parameters:
1. System ID of the source record (mandatory).
2. System ID of the target record (mandatory). The data of the source record will be copied to the target record.
3. Copy fields even when the target field is not empty (default false).
Script:
[cc lang=”javascript”]
var MergeUtils = Class.create();

MergeUtils.prototype = {

initialize: function(sTable,bDebug) {
if(!gs.nil(sTable)){
this.sTable=sTable;
} else {
gs.addErrorMessage(“Nothing to merge, source/target table is not specified!”);
return;
}
if(gs.nil(bDebug)) {
this.bDebug=false;
} else {
this.bDebug=bDebug;
}
},

sourceWithTarget: function(sourceID,targetID,bEmpty) {
if(!gs.nil(sourceID) && !gs.nil(targetID)) {
//Overwrite target fields which are not empty? Default is no
if(gs.nil(bEmpty)) {
bEmpty=false;
}

var grSource = new GlideRecord(this.sTable);
var grTarget = new GlideRecord(this.sTable);
if(!grSource.get(sourceID) || !grTarget.get(targetID)) {
gs.addErrorMessage(“Nothing to merge, source and/or target do not exist!”);
} else {
this.copyFieldsToTarget(grSource,grTarget,bEmpty);
this.moveReferencesToTarget(grSource,grTarget);
if(grSource.hasAttachments()) {
this.moveAttachmentsToTarget(grSource,grTarget);
}
this.moveFieldAttachmentsToTarget(grSource,grTarget,bEmpty);
this._updateSource(grSource,grTarget,bEmpty);
}
} else {
gs.addErrorMessage(“Nothing to merge, source and/or target is not specified!”);
}
},

copyFieldsToTarget: function(grSource,grTarget,bEmpty) {
//Copy fields from source to target and delete source
this._debugLog(“1. Copy Fields from Source to Target.”);
if(gs.nil(bEmpty)) {
bEmpty=false;
}

var grDict = new GlideRecord(“sys_dictionary”);
grDict.addQuery(“name”,this.sTable);
grDict.addQuery(“internal_type”,”!=”,”collection”);
grDict.addQuery(“reference”,”!=”,this.sTable).addOrCondition(“reference”,””);
grDict.query();

var i = 0;
while(grDict.next()) {
i++;
if(grDict.element.toString().substr(0,4) == “sys_”) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” system fields are not copied”);
} else if(grSource[grDict.element].nil()) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” source is empty”);
} else if(grDict.name.toString() == “core_company” && grDict.element.toString() == “u_type”) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” target value is merged with source value, new value: ” + grTarget[grDict.element] + “, ” + grSource[grDict.element]);
grTarget[grDict.element] = grTarget[grDict.element] + ‘,’ + grSource[grDict.element];
} else if(grTarget[grDict.element].nil()) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” target is empty and value is copied from source, new value: ” + grSource[grDict.element]);
grTarget[grDict.element] = grSource[grDict.element];
} else if(bEmpty) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” target is not empty, but value is copied from source, new value: ” + grSource[grDict.element]);
grTarget[grDict.element] = grSource[grDict.element];
} else {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” target is not empty and value is not copied from source”);
}
}
if(grTarget.changes()) {
grTarget.update();
}
},

moveReferencesToTarget: function(grSource,grTarget) {
//Query all references to source record
this._debugLog(“2. Redirect References to Source to Target.”);

var grDict = new GlideRecord(“sys_dictionary”);
grDict.addQuery(“internal_type”,”reference”);
grDict.addQuery(“reference”,this.sTable);
grDict.query();

var i = 0;
while(grDict.next()) {
i++;
this._debugLog(“2. Reference ” + i + “. ” + grDict.name + ” – ” + grDict.element);

var sColumn = grDict.element.toString();
var grTable = new GlideRecord(grDict.name.toString());
grTable.addQuery(sColumn, grSource.sys_id.toString());
grTable.query();

var j = 0;
while(grTable.next()) {
j++;
this._debugLog(“2. Reference ” + i + “.” + j + “. ” + grTable.getDisplayValue() + ” Old value: ” + grTable[sColumn] + ” – New value: ” + grTarget.getDisplayValue());
grTable[sColumn] = grTarget.sys_id.toString();
grTable.setWorkflow(false); //disable business rules
grTable.update();
}
}
},

moveAttachmentsToTarget: function(grSource,grTarget) {
//Move attachement documents from source to target
this._debugLog(“3. Move Attachment Documnets from Source to Target.”);

var grAttD = new GlideRecord(“sys_attachment”);
grAttD.addQuery(“table_name”,this.sTable);
grAttD.addQuery(“table_sys_id”,grSource.sys_id.toString());
grAttD.query();

var i = 0;
while(grAttD.next()) {
i++;
this._debugLog(“3.1 Attachment ” + i + “. ” + grAttD.file_name + ” – ” + grAttD.content_type + ” moved to target”);
grAttD.table_sys_id = grTarget.sys_id.toString();
grAttD.setWorkflow(false); //disable business rules
grAttD.update();
}
},

moveFieldAttachmentsToTarget: function(grSource,grTarget,bEmpty) {
//Move attachement fields from source to target
this._debugLog(“4. Move Attachment Fields from Source to Target”);
if(gs.nil(bEmpty)) {
bEmpty=false;
}

var fileNames = [];
//Determine target fields
var gaAtt = new GlideAggregate(“sys_attachment”);
gaAtt.addQuery(“table_name”,”ZZ_YY” + this.sTable);
gaAtt.addQuery(“table_sys_id”,grTarget.sys_id.toString());
gaAtt.groupBy(“file_name”);
gaAtt.query();

var i = 0;
while(gaAtt.next()) {
i++;
this._debugLog(“4.1 Field Attachment ” + i + “. Existing Target Field ” + gaAtt.file_name + ” – ” + gaAtt.content_type);
fileNames.push(gaAtt.file_name.toString());
}

//Determine source fields to move
var grAttF = new GlideRecord(“sys_attachment”);
grAttF.addQuery(“table_name”,”ZZ_YY” + this.sTable);
grAttF.addQuery(“table_sys_id”,grSource.sys_id.toString());
if(!bEmpty) {
grAttF.addQuery(“file_name”,”NOT IN”,fileNames.join(‘,’));
}
grAttF.orderBy(“file_name”);
grAttF.query();

i = 0;
while(grAttF.next()) {
i++;
if(bEmpty) {
//Remove existing target field attachment when bEmpty is set to overwrite existing data in target record
var grAtt = new GlideRecord(“sys_attachment”);
grAtt.addQuery(“table_name”,”ZZ_YY” + this.sTable);
grAtt.addQuery(“table_sys_id”,grTarget.sys_id.toString());
grAtt.addQuery(“file_name”,grAttF.file_name.toString());
grAtt.query();
var j = 0;
while(grAtt.next()) {
j++;
this._debugLog(“4.2 Field Attachment ” + j + “. Existing Target Field attachment ” + grAtt.file_name + ” – ” + grAtt.content_type + ” removed from target”);
grAtt.table_sys_id = grSource.sys_id.toString();
grAtt.setWorkflow(false); //disable business rules
grAtt.update();
}
}
this._debugLog(“4.3 Field Attachment ” + i + “. Remaining Source Field attachment ” + grAttF.file_name + ” – ” + grAttF.content_type + ” moved to target”);
grAttF.table_sys_id = grTarget.sys_id.toString();
grAttF.setWorkflow(false); //disable business rules
grAttF.update();
}
},

_updateSource: function(grSource,grTarget,bEmpty) {
this._debugLog(“5. Update Source.”);
if(gs.nil(bEmpty)) {
bEmpty=false;
}

var sComm = “are copied from ” + grSource.getDisplayValue() + ” to ” + grTarget.getDisplayValue() + ” and all references and attachments of ” + grSource.getDisplayValue() + ” are moved to ” + grTarget.getDisplayValue() + “.\nThe ” + grSource.getClassDisplayValue() + “, ” + grSource.getDisplayValue() + ” can be deleted!”;
if(bEmpty) {
sComm = “All fields ” + sComm;
} else {
sComm = “Only fields which are empty in ” + grTarget.getDisplayValue() + ” ” + sComm;
}
if(grSource.isValidField(“notes”)) {
grSource.notes = sComm + “\n” + grSource.notes;
} else if(grSource.isValidField(“description”)) {
grSource.description = sComm + “\n” + grSource.description;
} else if(grSource.isValidField(“comments”)) {
grSource.comments = sComm + “\n” + grSource.comments;
} else if(grSource.isValidField(“comment”)) {
grSource.comment = sComm + “\n” + grSource.comment;
}
if(grSource.isValidField(“active”)) {
grSource.active = false;
}
if(grSource.changes()) {
grSource.update();
}
sComm = sComm.replace(grSource.getDisplayValue(),”” + grSource.getDisplayValue() + ““).replace(grTarget.getDisplayValue(),”” + grTarget.getDisplayValue() + ““);
gs.addInfoMessage(sComm);
},

_debugLog: function(sText) {
var sInitials = “MC”; //Developer Initials
var sName = “MergeUtils”; //Name of the script being debugged
var sType = “Script Includes”; //Type of the script being debugged
if(this.bDebug) {
gs.log(sInitials + ” – ” + sName + ” – ” + sType + “: ” + sText);
}
},

type: MergeUtils

};
[/cc]

Precaution

Be careful using this UI Action on your own instance. Always check if the statements in the script include work for your instance and do not interfere with existing business rules and requirements.

How to use it on other tables

To use the UI action on another table than company, you only have to create a UI action on the table and check the following lines:
• Script include:
[cc lang=”javascript”]
copyFieldsToTarget: function(grSource,grTarget,bEmpty) {
//Copy fields from source to target and delete source
this._debugLog(“1. Copy Fields from Source to Target.”);
if(gs.nil(bEmpty)) {
bEmpty=false;
}

var grDict = new GlideRecord(“sys_dictionary”);
grDict.addQuery(“name”,this.sTable);
grDict.addQuery(“internal_type”,”!=”,”collection”);
grDict.addQuery(“reference”,”!=”,this.sTable).addOrCondition(“reference”,””);
grDict.query();

var i = 0;
while(grDict.next()) {
i++;
if(grDict.element.toString().substr(0,4) == “sys_”) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” system fields are not copied”);
} else if(grSource[grDict.element].nil()) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” source is empty”);
} else if(grDict.name.toString() == “core_company” && grDict.element.toString() == “u_type”) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” target value is merged with source value, new value: ” + grTarget[grDict.element] + “, ” + grSource[grDict.element]);
grTarget[grDict.element] = grTarget[grDict.element] + ‘,’ + grSource[grDict.element];
} else if(grTarget[grDict.element].nil()) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” target is empty and value is copied from source, new value: ” + grSource[grDict.element]);
grTarget[grDict.element] = grSource[grDict.element];
} else if(bEmpty) {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” target is not empty, but value is copied from source, new value: ” + grSource[grDict.element]);
grTarget[grDict.element] = grSource[grDict.element];
} else {
this._debugLog(“1. Field ” + i + “. ” + grDict.name + ” – ” + grDict.element + ” target is not empty and value is not copied from source”);
}
}
if(grTarget.changes()) {
grTarget.update();
}
},
[/cc]
Add code to copy fields, which need extra attention, like for example the u_type field. In this case the source value has to be concatenated to the target value.
[cc lang=”javascript”]
_updateSource: function(grSource,grTarget,bEmpty) {
this._debugLog(“5. Update Source.”);
if(gs.nil(bEmpty)) {
bEmpty=false;
}

var sComm = “are copied from ” + grSource.getDisplayValue() + ” to ” + grTarget.getDisplayValue() + ” and all references and attachments of ” + grSource.getDisplayValue() + ” are moved to ” + grTarget.getDisplayValue() + “.\nThe ” + grSource.getClassDisplayValue() + “, ” + grSource.getDisplayValue() + ” can be deleted!”;
if(bEmpty) {
sComm = “All fields ” + sComm;
} else {
sComm = “Only fields which are empty in ” + grTarget.getDisplayValue() + ” ” + sComm;
}
if(grSource.isValidField(“notes”)) {
grSource.notes = sComm + “\n” + grSource.notes;
} else if(grSource.isValidField(“description”)) {
grSource.description = sComm + “\n” + grSource.description;
} else if(grSource.isValidField(“comments”)) {
grSource.comments = sComm + “\n” + grSource.comments;
} else if(grSource.isValidField(“comment”)) {
grSource.comment = sComm + “\n” + grSource.comment;
}
if(grSource.isValidField(“active”)) {
grSource.active = false;
}
if(grSource.changes()) {
grSource.update();
}
sComm = sComm.replace(grSource.getDisplayValue(),”” + grSource.getDisplayValue() + ““).replace(grTarget.getDisplayValue(),”” + grTarget.getDisplayValue() + ““);
gs.addInfoMessage(sComm);
},
[/cc]
Add or remove code to add the merge comments to the source record field of your choosing.
• UI page client script:
[cc lang=”javascript”]
function validateTarget() {
//Gets called if the ‘OK’ dialog button is clicked
//Make sure targetID is not empty
var targetID = gel(“QUERY:sys_id!=” + gel(“sourceID”).value).value;
if (targetID == “”) {
//If targetID is empty stop submission
alert(“Please provide a ” + gel(“viewName”).value + ” to submit the dialog.”);
return false;
} else {
var x = confirm(“Are you sure you want to merge both companies!”);
if (!x) {
return onCancel();
}
}
GlideDialogWindow.get().destroy(); //Close the dialog window
return true;
}

function onCancel() {
var c = gel(‘cancelled’);
c.value = “true”;
GlideDialogWindow.get().destroy(); //Close the dialog window
return false;
}
[/cc]
Change the message to the label of the new table or make the message more generic.

If you have any question you can send me an email on .img[at].img

Leave a Reply