Description
Two records from different tables with the same name is causing ambiguity
Release or Environment
All available releases
Cause
The number maintenance in the instance could be different from OOB ones. In out of the box instances, no two table would share the same prefix. Say prefix for the table task is TASK, SCTASK for catalog tasks, CHAT for chat queue entries etc. It might have been modified in the instance. Since these records are auto numbered this is expected. The user can verify the same by navigating to the following link and checking the prefixes for the tables.
https://<instance-name>.service-now.com/sys_number_list.do
Resolution
In order to resolve the issue, the user need to have unique prefixes set for the affected tables. They can set the out of the box prefixes to avoid other ambiguities. TO change the same, the user will need to edit the prefix values for the corresponding tables by going to the sys_number table.
In order to change the prefix of the existing records, one can make use of the following code in the background script.
For example, to change sc_task records' prefix from TASK to SCTASK, the following script can be used.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//Query for the table records
var rec = new GlideRecord('sc_task');
rec.query();
while(rec.next()){
//Change the number prefix from 'TASK' to 'SCTASK'
rec.number = rec.number.replace('TASK', 'SCTASK');
rec.setWorkflow(false); //Do not run business rules
rec.autoSysFields(false); //Do not update system fields
rec.update();
}
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
For large tables that will timeout using the above method (over 300k), there is an optional way using a snow command:
sudo snow mysql-updatetable --dry-run --task chg1234567 <instance name> --table task --set=“number=concat(‘SC’,number)” --where “sys_class_name=‘sc_task’”
Additional Information
CAUTION - This will need to be run by SRE if the table is larger than 200k records because otherwise a timeout may occur. Also, DO NOT RUN SCRIPT TWICE on records already containing the prefix 'SCTASK' because these will be altered to 'SCSCTASK'. Additionally, do not run the script on the production environment first. Ensure testing is first carried out on a developer instance. Only after verifying this is working as expected should you proceed to execute this in production.