I have duplicate entries in department table.can any one help me with script to delete duplicate records in department table.department number is the unique value

[swetha53]

I have duplicate entries in department table.can any one help me with script to delete duplicate records in department table.department number is the unique value

[marcguy]

you need to go through each record and look for duplicates

var dept = new GlideRecord('cmn_department'); //department table

dept.query(); //query all

while (dept.next()){ //while in one

var dept2 = new GlideRecord('cmn_department'); //department table again but different var dept2

dept2.addQuery('number',dept.number); //find all department records with same number

dept2.addQuery('sys_id','!=','dept.sys_id); //except for this specific one via !=sys_id

dept2.query(); //query for them

gs.print('number of dupes found for ' + dept.number + ' - ' + dept2.getRowCount());

//dept2.deleteMultiple(); //delete them all

}

run the scripts in scripts -background first with the deleteMultiple line commented, see what your getting, then run it in a DEV instance to test, then when completely happy run it in PROD.

One word of warning is any record that is referencing a department record that is going to get deleted will have a bad value in it, i.e. incident.department, user.department. so you want to think about that and whether you can't just mark these departments as active=false instead.

[williamsun]

Just to add to this, I would not do this through a script.

One of the entries is the real one and the other is the duplicate, I would need to validate that the members of the twin departments are in the "correct" department before deleting the other.   Also, unless you are sure of which of the two is not used, you might end up deleting the real one and leaving the duplicate (and probably empty) department leaving many users department-less.

Probably not what you want to hear, but I would do the following:

1. Create a list of the duped departments (maybe exporting to excel)

2. Go through the list and add an (X) to the end of the one you want to delete

3. Look for any users that belong to a department with an (X) at the end and change them to the X-less one

4. Delete all departments with an (X) at the end.

[Community Alums]

How about a different approach: instead of using a script to delete the duplicates here, you could use the platform's Field Normalization capabilities. You may need to activate the plugin if it is not already configured, but then you can set up normal values for the items you want to merge and configure the normalization to coalesce on one of the values. Select the record that has the most correct information to coalesce on.

This will avoid any issues with orphaned record pointers that Marc describes above.