How to build a filter that will combine all managers in the sys_user manager hierarchy into 1 field?

Jim_Keefover
Kilo Expert

Tuesday - last edited Tuesday

Hello.

Looking for some help to combine all manager names from sys_user into 1 field for a report filter.

 

Examples:

Employee Julie has managers in manager hierarchy as:

    Julie, Greg, Frank, Doug, Chad, Bob

 

Employee Greg has managers in manager hierarchy as:

   Greg, Frank, Doug, Chad, Bob

 

Employee Frank has managers in manager hierarchy as:

   Frank, Doug, Chad, Bob

 

Ideal result would be to filter where Manager Name contains Doug and return all workers who have Doug in their management hierarchy.  

--  Or  --

Filter for Manager Name Contains Greg and only return workers who have Greg in their management hierarchy.

 

Thank you in advance.

🙂

 

 

 

 

 

 

0 Helpfuls
  • 203 Views
1 REPLY 1

Itallo Brandão
Giga Guru

Tuesday

Hi Jim,

This is a classic reporting requirement!

To achieve exactly what you described—a single searchable text field containing the entire chain—you will need a Custom Field and a Script to populate it.

However, there is one major architectural caveat:

  • If you put a Business Rule on the User record, it works fine when the employee changes jobs.

  • BUT, if a manager higher up the chain (e.g., Frank) leaves, the users below him (Julie and Greg) won't automatically update because their records weren't "touched".

The Recommendation: Create the custom field, but populate it via a Scheduled Job (Nightly). This ensures the entire hierarchy is recalculated correctly without creating complex recursive updates that could slow down your system during the day.

Step 1: Create the Field

  • Table: User [sys_user]

  • Type: String (Max Length 4000)

  • Name: u_manager_hierarchy

Step 2: The Script (Scheduled Job) Create a Scheduled Script Execution to run daily.

 
var user = new GlideRecord('sys_user');
user.addActiveQuery(); // Only process active users
user.query();

while (user.next()) {
    var hierarchy = [];
    var currentMgr = user.manager;
    var depth = 0;
    var maxDepth = 25; // Safety brake for infinite loops
    
    // Walk up the tree
    while (currentMgr && depth < maxDepth) {
        // Get the manager record to grab the name
        var mgrGR = currentMgr.getRefRecord();
        if (mgrGR.isValidRecord()) {
            hierarchy.push(mgrGR.getDisplayValue()); // Adds "Greg", then "Frank"...
            currentMgr = mgrGR.manager; // Move one level up
            depth++;
        } else {
            break;
        }
    }
    
    // Convert array to string: "Greg, Frank, Doug, Chad"
    var finalString = hierarchy.join(', ');
    
    // Only update if changed (prevents unnecessary audit logs)
    if (user.u_manager_hierarchy != finalString) {
        user.u_manager_hierarchy = finalString;
        user.setWorkflow(false); // Do not trigger other BRs/Notifications
        user.autoSysFields(false); // Do not update 'Updated By'
        user.update();
    }
}

How to use it: Now, in your Report, you can simply filter:

  • Manager Hierarchy | contains | Doug

This will return everyone who has Doug anywhere in their reporting line.

If this response helps you solve the issue, please mark it as Accepted Solution.
This helps the community grow and assists others in finding valid answers faster.

Best regards, Brandão.