Prevent Circular Relationships in ServiceNow

//, Script includes/Prevent Circular Relationships in ServiceNow

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);
        }
    }
}
By | 2018-07-09T15:00:00-06:00 March 16th, 2011|Categories: Business rules, Script includes|9 Comments

About the Author:

9 Comments

  1. Steve Darity March 16, 2011 at 6:41 am - Reply

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

    • Mark Stanger March 16, 2011 at 6:47 am - Reply

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

  2. Wesley May 12, 2011 at 3:38 am - Reply

    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);
     
    }
     
    }
    • Mark Stanger May 12, 2011 at 3:48 am - Reply

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

  3. Matt June 26, 2014 at 8:42 am - Reply

    Where has this been all my life? 🙂

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

    • Dan April 30, 2015 at 10:18 am - Reply

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

      • Mark Stanger April 30, 2015 at 10:19 am - Reply

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

  4. Renuka May 23, 2017 at 7:43 am - Reply

    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.

Leave A Comment