A
lmost any database will have situations where a record in a table can relate back to other records in that same table. This happens in ServiceNow in several places and it’s common enough that you may find yourself building additional functionality that works this way. The classic case is the ‘Parent’ field. A good example is the ‘Parent’ field on the ‘Location (cmn_location)’ table. Each location record in ServiceNow can be related to a ‘Parent’ location. This allows you to build a nice hierarchy of locations within your Service-now instance. Unless you work in one of those very rare places that implements a completely flat location structure, this parent-child relationship is critical for representing locations in your system.
This same type of setup is used in other places (such as task tables) where a given record can result in the generation of another record of the same type. You may have scenarios where a change request can be the parent of other change request or where a major incident becomes the parent of other child incidents. In this post, I’ll address the problem of circular relationships that can exist when you’re working with parent-child relationships in ServiceNow.
In order to represent a parent-child relationship, you simply have to set up a reference field (or use an existing one) on the table of your choice. The thing you really have to be careful of with these fields is that they also allow users to relate a record to itself, making itself its own parent! While that scenario seems like it would be obvious to avoid, it’s not so easy to see situations where you might be making a record its own parent a few levels up. You’ll encounter problems when you need to query the parent hierarchy to get a list of the parents of a particular record. What ends up happening if a record has been made a parent of itself at some level is that your query runs in an endless loop and you end up killing the performance of your system or potentially making it completely unusable.
In order to prevent this type of problem, I’ve created the following ‘Prevent circular relationship’ script that you can use in a business rule for any table/field combination where you might have this problem in your system. It’s very simple to use. Simply construct the business rule as shown below and indicate the name of the field to check for circular relationships.
Name: Prevent circular relationship
When: Before
Insert/Update: True
Condition: current.parent.changes() && !current.parent.nil()
Script:
var fieldName = 'parent'; //Specify the name of the field to check for circular relationship on
var firstParent = current[fieldName].getRefRecord();
//If we have a valid record in the parent field check circular relationships
if(firstParent && firstParent.isValidRecord()){
checkCircularRelationship(firstParent, current, fieldName);
}
})(current, previous);
function checkCircularRelationship(parentRec, child, fieldName){
var depth = 0;
//Safety check to prevent endless loop in the event that a circular relationship already exists
//Increase the current depth
depth++;
//If we have gone more than the allowed depth then abort
if(depth > 20){
current.setAbortAction(true);
gs.addInfoMessage('Possible circular relationship already exists. Aborting check to prevent endless loop.');
return null;
}
//If the current parent being evaluated matches the child then abort
if(parentRec.sys_id == child.sys_id){
//Abort the submission
gs.addErrorMessage('Relating to the chosen parent value creates a circular relationship.<br/> Please choose a different parent.');
current.setAbortAction(true);
}
else{
//Check for next parent
var nextParent = parentRec[fieldName].getRefRecord();
if(nextParent && nextParent.isValidRecord()){
//Test the next level
checkCircularRelationship(nextParent, child, fieldName);
}
}
}
Now that is Awesome. Thank Your for all of your contributions and well documented solutions and suggestions. Great Website.
Thanks for your comment Steve. It makes it much more worthwhile to share this stuff if I know that it’s helping somebody. 🙂
I was actually implementing something similar when I came across the RecursionTester object which is part of the business rule checking the sys_db_object table for recursion. Just thought i’d share it:
if (rt.isRecursive(current)) {
current.setAbortAction(true);
current.parent.setError('Invalid Parent');
gs.addErrorMessage('The selected parent loops back to this record (recursive loop)');
}
// Prevent duplicates
if (current.operation() == 'insert') {
var dup = new GlideRecord('sys_db_object');
dup.addQuery('name', current.name);
dup.query();
if (dup.hasNext()) {
current.setAbortAction(true);
current.name.setError('Object already defined for ' + current.name);
gs.addErrorMessage('Object already defined for ' + current.name);
}
}
Looks cool. I’ll have to check it out. Thanks for sharing!
Where has this been all my life? 🙂
I hope this solution can help me prevent circular CI relationships.
Why this isn’t OOB functionality is beyond me. Mark saves the day again!
I agree. Thanks for the feedback, I’m glad the solution helped you!
Hi Mark,
I have a requirement where in i need to insert the category values.
Example : App/Mobile/Laptop
1) App should be inserted as title
2) Mobile should be inserted as title and App should be parent of title App
3) Laptop should be inserted as title and App/Mobile should be parent of title Mobile
This is into sc_category table.
Please let me know feasible solution for this.
Hi Renuka,
It is easy to create custom category/subcategory structures in ServiceNow. Just navigate to Service Catalog >> Maintain Categories.
If you need more assistance you can review this ServiceNow Docs article or post your question on the ServiceNow Community Forum.