How to search through the hierarchy of the departments table in a script include

[matthew_hughes]

In ServiceNow, I've been writing the below function in my script include:

_getDepartmentData: function(ownerDepartment, ownerId) {

        var grUserID = new GlideRecord('sys_user');

        grUserID.get(ownerId);

        var departmentPlatform;

        var departmentBusinessUnitFunction;

        var grDepartmentOwner = new GlideRecord('cmn_department');

        grDepartmentOwner.addQuery('sys_id', grUserID.department);

        grDepartmentOwner.query();

        if(grDepartmentOwner.next()){

        }

    },

I'm wanting my script include to carryout the below logic:

1. Query from the user table

2. Query from the department table using the user's department ID as a preference

3. From there, I need to go up the department hierarchy until we've got a department with a type of 'Platform' and a department type of either 'Business Unit' or 'Function'

4. I want to get the values of the two departments with a type of 'Platform' and type of either 'Business Unit' or 'Function'

Does anyone what I need to do to get points 3 and 4 working?

[Ankur Bawiskar]

@matthew_hughes 

try this

_getDepartmentData: function(ownerDepartment, ownerId) {
    var grUserID = new GlideRecord('sys_user');
    if (!grUserID.get(ownerId) || !grUserID.department) {
        return { platformDept: null, businessUnitDept: null };
    }
    
    var result = { platformDept: null, businessUnitDept: null };
    
    // Start traversal from user's department
    this._traverseDepartmentHierarchy(grUserID.department, result);
    
    return result;
},

_traverseDepartmentHierarchy: function(deptSysId, result) {
    var grDept = new GlideRecord('cmn_department');
    if (!grDept.get(deptSysId)) {
        return; // No department found, stop traversal
    }
    
    // Check if this department matches criteria
    if (grDept.type.toString() == 'Platform') {
        var deptType = grDept.department_type ? grDept.department_type.toString() : '';
        if (deptType == 'Business Unit' || deptType == 'Function') {
            if (!result.platformDept) {
                result.platformDept = grDept.sys_id.toString();
            }
            if (!result.businessUnitDept && (deptType == 'Business Unit' || deptType == 'Function')) {
                result.businessUnitDept = grDept.sys_id.toString();
            }
        }
    }
    
    // Continue up hierarchy if parent exists and we haven't found both
    if (grDept.parent && (!result.platformDept || !result.businessUnitDept)) {
        this._traverseDepartmentHierarchy(grDept.parent.toString(), result);
    }
}

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[matthew_hughes]

Hi @Ankur Bawiskar Thanks very much

I also tried doing it the below as well:

_getDepartmentData: function(ownerDepartment, ownerId) {

        var departmentPlatform = 'Platform';

        var departmentBusinessUnit = 'Business Unit';

        var departmentFunction = 'Function';

        var maxLoop = 100;

        var startDeptId = ownerDepartment;

        // Fallback to user only if no department was passed in

        if (!startDeptId && ownerId) {

            var grUser = new GlideRecord('sys_user');

            if (grUser.get(ownerId)) {

                startDeptId = grUser.getValue('department'); // may be empty

            }

        }

        // If we still don't have a department, return null

        if (!startDeptId) {

            return {

                platformDepartmentId: null,

                buOFunctionDepartmentId: null

            };

        }

        // 3)If the owner's selected department doesn't exist in the departments table

        var grDepartmentOwner = new GlideRecord('cmn_department');

        if (!grDepartmentOwner.get(startDeptId)) {

            return {

                platformDepartmentId: null,

                buOFunctionDepartmentId: null

            };

        }

        //variables declared to help with walking up the department hierarchy-

        var loops = 0;

        var departmentsLooped = {}; //Used to prevent infite loops when looking up the department hierarchy

        var platformDepartmentId = null;

        var buOFunctionDepartmentId = null;

        while (grDepartmentOwner.isValidRecord() && loops++ < maxLoop) {

            var departmentId = grDepartmentOwner.getUniqueValue();

            if (departmentsLooped[departmentId]) break; // Stop the loop if the current department has already been seen before.

            departmentsLooped[departmentId] = true;

            var typeVal = grDepartmentOwner.getValue('u_department_type') || '';

            // Capture the nearest departments with the required matches

            if (!platformDepartmentId && typeVal == departmentPlatform) {

                platformDepartmentId = departmentId;

            }

            if (!buOFunctionDepartmentId &&

                (typeVal == departmentBusinessUnit || typeVal == departmentFunction)) {

                buOFunctionDepartmentId = departmentId;

            }

            // Stop the loop if both are found

            if (platformDepartmentId && buOFunctionDepartmentId) break;

            // Climb to the parent

            var parentId = grDepartmentOwner.getValue('parent');

            if (!parentId || !grDepartmentOwner.get(parentId)) break;

        }

        //return department sys IDs

        return {

            platformDepartmentId: platformDepartmentId,

            buOFunctionDepartmentId: buOFunctionDepartmentId

        };