Archiving with unique fields

General Add comments
by:

In the instances which have the Berlin or a later release installed, the archiving functionality is enabled. The application “System Archiving” is available. Archiving rules are used to determine which tables are archived and for which tables a corresponding archiving table should be created. All the archiving tables are named ar for example the incident table will have an arincident table if an active archiving rule is defined.
What happens if the “number” field on the task table is set as a unique field in the dictionary table?
kelvin1

It is possible to archive a record and manually restore it. For example when there is an incident with number INC000001 which is in closed state and an archiving rule to have all closed incident archived. The incident record will be moved to the arincident table. It is possible to create new incident record with number INC000001 now because the record with INC000001 does not exists anymore.

But what happens if the archived record is now restored?
The following error message will appear because of the uniqueness on the field number on the task table we applied earlier.
kelvin2

To prevent this from happening a possible solution could be to restrict the end-user from creating a new record with the same uniqueness criteria which already exists in the archived table.
How is it possible to implement such a rule in ServiceNow, to automatically verify if this is the case?
Below a step by step implementation guide is included on how to do this.

  1. Make a field unique for example the number field on task or the ci_name field on the cmdb_ci table by checking the “unique” checkbox on the dictionary entry.
  2. Create the following global business rule and dependent script include.
Field Value
Name Check uniqueness
Table Task [task]
Order 100
Client callable false
Active true
When Before
Insert true
Update true
Delete false
Query false
Condition <empty>
Script
var errormessage;errormessage = VerifyUniqueness(current);
if(errormessage != ”) {
gs.addInfoMessage(errormessage);
current.setAbortAction(true);
}

Script Include

Field Value
Name VerifyUniqueness
Active true
Client callable false
Description <empty>
Script
function VerifyUniqueness(grrecord) {var tablename = grrecord.getTableName();
var childtablename = grrecord.getRecordClassName();
var table = new TableUtils(‘ar_’ + childtablename);
var hasartable = table.tableExists();
if(hasartable) {
table = new TableUtils(tablename);
var tables = table.getTables();
for(var i=0; i<tables.size(); i++) {
var grdict = new GlideRecord(‘sys_dictionary’);
grdict.addQuery(‘name’, tables.get(i).toString());
grdict.addQuery(‘internal_type.name’, ‘!=’, ‘collection’);
grdict.addQuery(‘unique’, true);
grdict.query();
while(grdict.next()) {
var grarchive = new GlideRecord(‘ar_’ + childtablename);
grarchive.addQuery(grdict.element.toString(), grrecord[grdict.element.toString()]);
grarchive.query();
while(grarchive.next()) {
return ‘Record with value <a href=”‘ + grarchive.getLink() + ‘”>’ +grrecord[grdict.element.toString()] + ‘</a> for field ‘ + grdict.element.toString() + ‘ already exists in archive table ar_’ + tablename;
}
}
}
}
}

The script will take the extended tables into account, an example is the incident table which is extended from the task table.

Testing time!

  1. Open the archiving rule “Incident – Inactive and closed over 6 months”
  2. Activate the archiving rule by checking the active checkbox, and save the record.
    kelvin3
  3. Remove the filter on the Closed field, so it looks like
    kelvin4
  4. Note down one of the closed incidents, visible in the Closed module under the Incident application
    kelvin5
  5. Run the archiving rule manually, by using the link “Run Archive Now”
    kelvin5a
  6. The closed incident records are now moved to the archived table
  7. Try to create a new incident with the same number noted down in step 4
  8. A message should appear like the screenshot below.
    kelvin6
  9. The creation of the incident is cancelled and there are no duplicate records in the archive and ‘active’ table.

This is my resolution for this possible problem, please feel free to comment in case you have another idea or insight on the archiving functionality. In my opinion I find it a good solution in terms of:
– Improving the performance of the instance
– Giving the end-user a more user friendlier view of the records in the instance because only the ‘active’ records are visible.
– The number of record visible in the view contains less records which makes searching easier.

That’s all!
Enjoy and if you have any questions you can reach me at
.img[at].img.

Leave a Reply