List ALL employees who tree up to a Manager

[jmiskey]

We are trying to create a Catalog Item where a Management-level person can fill out and select any employee who trees up to them (i.e. all their direct AND indirect reports).

For example, let's say that we have the following employee structure (an obviously simplified example that only shows one employee at each level):

Note that each employee listed is the manager of the employee listed just below them.  So if Bernice Burt were the one filling out this Catalog Item, she should be able to select from ALL the employees listed below her, as Carrie Carson reports to her directly, and everyone under that reports to her indirectly.

We already do a scaled-down version of something like this, where we list a person's direct reports, and first two levels of indirect reports.  We do that through a Script Includes, whose code looks like this:

If we used this on our example, it would return the first three employee under them, but not the 4th (Fran Frankel).

Now, we could keep building on to this Script Includes, to include "manger.manager.manager.manager", but the point is, we do not know how many levels down we might need to go to capture everyone (it could be 6 or 7).  It is kind of an unknown target.

Has anyone come up with any clever, efficient ways of doing something like this?  Or do we just need to keep adding "manager.manager.manager..." to our Script Includes to go as far as we think it might ever go?

It seems to me that there has to be a better way.

Thanks

[_ChrisHelming]

Create and call a script include that recurses through managers. The getDirectReports method is superfluous since you can just query manager = user but is in there as an example of limiting the number of levels.

var ManagerEmployees = Class.create();
ManagerEmployees.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getEmployees: function(user) {
        var userList = [];
        var maxLevel = 10; //Adjust for levels you want to recurse
        var level = 1;
        var grUser1 = new GlideRecord('sys_user');
        user = user === undefined ? gs.getUserID() : gs.getUser().getUserByID(user).getID();
        if (grUser1.get(user)) {
            this._recurseUsers(grUser1.sys_id.toString(), level, maxLevel, userList);
        }
        return userList.join(',');
    },
	getDirectReports: function(user) {
        var userList = [];
        var maxLevel = 1; //Adjust for levels you want to recurse
        var level = 1;
        var grUser1 = new GlideRecord('sys_user');
        user = user === undefined ? gs.getUserID() : gs.getUser().getUserByID(user).getID();
        if (grUser1.get(user)) {
            this._recurseUsers(grUser1.sys_id.toString(), level, maxLevel, userList);
        }
        return userList.join(',');
    },
    _recurseUsers: function(manager, level, maxLevel, userList) {
        var grUser2 = new GlideRecord('sys_user');
        grUser2.addQuery('manager', manager);
        grUser2.addActiveQuery();
        grUser2.query();
        while (grUser2.next()) {
            userList.push(grUser2.sys_id.toString());
            if (level + 1 <= maxLevel) {
                this._recurseUsers(grUser2.sys_id.toString(), level + 1, maxLevel, userList);
            }
        }
		return userList;
    },

    type: 'ManagerEmployees'
});

[jmiskey]

OK, looks promising.  I am just trying to figure out exactly how to use it, as you have written it.

Currently, I have the following fields on my Catalog Item:

The "submitter" field is hidden and defaults to the person completing the form, i.e.

So I tried putting the following Reference Qualifier on the "employee_name" field in order to use the Script Includes (which has been added, and I made available in all scopes, and made it Client Callable):

But it is listing ALL employees, not just the ones who "tree up" to the Submitter.  So it doesn't appear to be filtering anyone out.

Furthermore, I tried making a small addition to the GlideRecord query to remove all the inactive people, but that did not work either (here is a snippet of the part I updated).

It returned all Active and Inactive records.

What am I doing wrong?

How do I get your Script Include to work for me?

[Luiz Lucena]

Hi @jmiskey did you find a solution to this?
I'm having the same request here.

[jmiskey]

No, we never got it working, so we just stuck with the three level approach.  If you have a definitive number of levels you are working with (and it isn't too high), you could expand out my original solution in a type of "brute force" method, and it should work.

I was originally hoping for something more dynamic, but couldn't get the proposed solution to work properly for us.