THURSDAY, JANUARY 19, 2017

Prevent Circular Relationships in ServiceNow

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.

‘Prevent circular relationship’ business rule
Name: Prevent circular relationship
When: Before
Insert/Update: True
Condition: current.parent.changes() && !current.parent.nil()
Script:

(function executeRule(current, previous /*null when async*/) { 
    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);
        }
    }
}

7 Comments

Steve Darity 16-03-2011, 06:41

Now that is Awesome. Thank Your for all of your contributions and well documented solutions and suggestions. Great Website.

Reply
Mark Stanger 16-03-2011, 06:47

Thanks for your comment Steve. It makes it much more worthwhile to share this stuff if I know that it’s helping somebody. 🙂

Reply
Wesley 12-05-2011, 03:38

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:

var rt = new RecursionTester('sys_db_object', 'super_class');
 
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);
 
}
 
}
Reply
Mark Stanger 12-05-2011, 03:48

Looks cool. I’ll have to check it out. Thanks for sharing!

Reply
Matt 26-06-2014, 08:42

Where has this been all my life? 🙂

I hope this solution can help me prevent circular CI relationships.

Reply
Dan 30-04-2015, 10:18

Why this isn’t OOB functionality is beyond me. Mark saves the day again!

Reply
Mark Stanger 30-04-2015, 10:19

I agree. Thanks for the feedback, I’m glad the solution helped you!

Reply

Leave a Reply


Latest Comments

  • David: It appears that I can hit sys_properties table with REST. This works, but I haven’t yet discovered the...
  • Mark Stanger: Hey David, It doesn’t surprise me that scoped apps have made this more difficult. I’m not...
  • David: Mark, do you have an example of how to do this in a scoped app? It seems there are many hoops to jump through...
  • Mark Stanger: The only possibility is to create a system property to override this in your application. Check out the...