Reference field to show Parent/Child department Users of logged in User

[V_31]

Hi All,

I have a reference field which sits within a catalog item and i need it to show the logged in user, users from his department and all child departments.

Struggling to achieve this but think it can be done from script include and then adding it to reference qualifier.

If you can help with the script it would be much appreciated.

Kind regards

[suvro]

Hi V,

You have to create 3 variables, 

Logged in User

Users in their Department ( advanced reference qualifier --> javascript: 'sys_id IN userDepartment().getUsers(current.variables.logged in User))

Other child departments 

( advanced reference qualifier --> javascript: userDepartment().getChildDepartments(current.variables.logged in User))

In the script include:

getUsers: function (userID){

var usr = new GlideRecord('sys_user');

usr.get(userID);

var userDepartment = usr.department;

var depUsers = new GlideRecord('sys_user');

depUsers.addquery('department', userDepartment );

depUsers.query();

var users = []

while (depUsers.next()){

users.push(depUsers.sys_id.toString());

}

return  'sys_idIN' + users;

Similarly do for the other as well

[V_31]

Hi Suvro,

Thanks for your feedback. The configuration has already been done and is within other bits of configuration but its not working properly. Hopefully you will be able to identify the issue.

Script include

var DepartmentUsers = Class.create();
DepartmentUsers.prototype = {
initialize: function() {},
getUsers: function(currentUserID) {

var users = [];

if (this.getIsHRAdmin(currentUserID)) {
// Retrieve all active Profiles
var gr = new GlideRecord('sn_hr_core_profile');
gr.addQuery('user', '!=', '');
gr.addQuery('user.active', 'true');
// gr.orderBy('user.name');
gr.query();

while (gr.next()) {
users.push(gr.user.toString());
}
} else {
// Retrieve Direct Reports
var currentUser = new GlideRecord('sys_user');
currentUser.get(currentUserID);
//gs.log('*Current user: '+currentUser.name);

var departments = [];

if (!currentUser.department.nil()) {
departments.push(currentUser.department.sys_id.toString());
//gs.log('*Current user department: '+currentUser.department.name);

if (currentUser.department.dept_head.sys_id == currentUser.sys_id) { //For department Head look on 1 level deeper in departments, not all tree
//gs.log('*Current user is a department head of the departmnets:');
var grDepartment = new GlideRecord('cmn_department');
grDepartment.addEncodedQuery('dept_head.sys_id=' + currentUser.sys_id);
grDepartment.query();
while (grDepartment.next()) {
departments.push(grDepartment.sys_id.toString());
//gs.log(grDepartment.name);
}
//gs.log('*This departments are parents for that departments:');
var grSubDepartment = new GlideRecord('cmn_department');
grSubDepartment.addEncodedQuery('parent.sys_idIN' + departments);
grSubDepartment.query();
while (grSubDepartment.next()) {
departments.push(grSubDepartment.sys_id.toString());
//gs.log(grSubDepartment.name);
}
} else {
var grHRProfile = new GlideRecord('sn_hr_core_profile');
grHRProfile.addEncodedQuery('user=' + currentUser.sys_id + '^position.positionLIKEpersonal assistant^ORposition.positionLIKEexecutive assistant');
grHRProfile.query();
if (grHRProfile.next()) {
//gs.log('*Current User in an assistant position: '+grHRProfile.position.position);
if (!currentUser.manager.department.nil() && currentUser.manager.department != currentUser.department) {
departments.push(currentUser.manager.department.sys_id.toString());
}
var grManagerEmployees = new GlideRecord('sys_user');
grManagerEmployees.addEncodedQuery('active=true^manager=' + currentUser.manager.sys_id + '^sys_id!=' + currentUser.sys_id + '^sys_id!=' + currentUser.manager.sys_id);
grManagerEmployees.query();

while (grManagerEmployees.next()) {
users.push(grManagerEmployees.sys_id.toString());
}
}
}
}

if (departments.length > 0) {
//gs.log('*Current User can manage this people: ');
var grUsers = new GlideRecord('sys_user');
grUsers.addEncodedQuery('active=true^departmentIN' + departments + '^sys_id!=' + currentUser.sys_id + '^sys_id!=' + currentUser.manager.sys_id);
grUsers.query();

while (grUsers.next()) {
//gs.log(grUsers.name);
users.push(grUsers.sys_id.toString());
}
}
}
return users;
},
getUsersIncLoggedInUser: function(currentUserID) {
var directReportsAndCurrentUser = (this.getUsers(currentUserID) == '') ? gs.getUserID() : this.getUsers(currentUserID) + ', ' + currentUserID;
return directReportsAndCurrentUser;
},
getManagerByHierarchy: function(currentUserID) {

var currentUser = new GlideRecord('sys_user');
currentUser.get(currentUserID);

var userManager = '';

var isCEO = currentUser.position.position.toString().toLowerCase().indexOf('chief executive officer') > -1;

if (!isCEO) {
if (currentUser.department) {

var statement = false;
var department = currentUser.department;
do {
var grDept = new GlideRecord('cmn_department');
grDept.get(department);
// gs.print('Checking - ' + grDept.name);
if ((currentUser.sys_id != grDept.dept_head) && grDept.dept_head) {
userManager = grDept.dept_head;
statement = false;
return userManager;
}
if (grDept.parent) {
department = grDept.parent;
statement = true;
}
} while (statement);
}
return userManager;
}

},
getIsManagerOrPA: function(userID) {

var isManagerOrPA = false; // Return value 0 indicates false

var gr = new GlideRecord('sn_hr_core_profile');
gr.addQuery('user', userID);
gr.query();

if (gr.next()) {
var checkManagerOrPA = gr.position.u_chief || gr.position.u_position_name == 'Personal Assistant' || gr.position.u_position_name == 'Executive Assistant';
}
if (checkManagerOrPA == true) {
isManagerOrPA = true;
}
return isManagerOrPA;
},

getIsHRAdmin: function(userID) {

var user = gs.getUser(); // Initialise the User Object
user = user.getUserByID(userID);

return user.isMemberOf('HR Admin');
},
type: 'DepartmentUsers'
};

I know its a lot to take in. I appreciate all your help. thanks

[Chetan Mahajan]

Hi,

                 You have to create onchnage() client script (*for user field) and then create 2 multiline text field, Users from Parent Department and Users from Child Department. Create Script include which fetch Users from Department (gliderecord sys_user to find current user dept. then agian glide record to fetch users from that Department and store in array and pass that array to client script and set value) 

Refer below code just chnage value and table name

Client Script :

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }
    var gajax = new GlideAjax('MyFavoritesAjax');
    gajax.addParam('sysparm_name', 'getUsersfromDept');
    gajax.addParam('sysparm_userID', newValue);
    gajax.getXML(getResults);
}

function getResults(response) {
    var answer = response.responseXML.documentElement.getAttribute("answer");
    g_form.setValue('training_required', answer + " "); // field of child dept then same for parent
}

 Script Include (Client Callable) :

var MyFavoritesAjax = Class.create();
MyFavoritesAjax.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    getUsersfromDept: function() { // from same Departmet in sys_user table

        var id = this.getParameter('sysparm_userID'); // to get parameter from client side
	
        var ChildDepartment;
        var Dept = new GlideRecord('sys_user');
        Dept.addQuery('sys_id', id);
        Dept.query();
        if (Dept.next()) {          
           ChildDepartment = Dept.getValue('department');
        }
        var usersChild = []; // child department which is department in sys_user table
        var encodedquery = 'department=' + ChildDepartment;
        var userGR = new GlideRecord('sys_user');
        userGR.addEncodedQuery(encodedquery);
        userGR.query();
        while (userGR.next()) {
            usersChild.push(userGR.name.getDisplayValue());
        }
        usersChild.sort();
        return usersChild.toString();

    },

    type: "MyFavoritesAjax"

});

Output : (use multiline type for populate users)

Kindly mark correct and helpful if applicable

[V_31]

Hi Chetan,

I cannot add anymore fields and think i have the necessary fields to achieve whats necessary. there is something wrong with the script include below.

var DepartmentUsers = Class.create();
DepartmentUsers.prototype = {
initialize: function() {},
getUsers: function(currentUserID) {

var users = [];

if (this.getIsHRAdmin(currentUserID)) {
// Retrieve all active Profiles
var gr = new GlideRecord('sn_hr_core_profile');
gr.addQuery('user', '!=', '');
gr.addQuery('user.active', 'true');
// gr.orderBy('user.name');
gr.query();

while (gr.next()) {
users.push(gr.user.toString());
}
} else {
// Retrieve Direct Reports
var currentUser = new GlideRecord('sys_user');
currentUser.get(currentUserID);
//gs.log('*Current user: '+currentUser.name);

var departments = [];

if (!currentUser.department.nil()) {
departments.push(currentUser.department.sys_id.toString());
//gs.log('*Current user department: '+currentUser.department.name);

if (currentUser.department.dept_head.sys_id == currentUser.sys_id) { //For department Head look on 1 level deeper in departments, not all tree
//gs.log('*Current user is a department head of the departmnets:');
var grDepartment = new GlideRecord('cmn_department');
grDepartment.addEncodedQuery('dept_head.sys_id=' + currentUser.sys_id);
grDepartment.query();
while (grDepartment.next()) {
departments.push(grDepartment.sys_id.toString());
//gs.log(grDepartment.name);
}
//gs.log('*This departments are parents for that departments:');
var grSubDepartment = new GlideRecord('cmn_department');
grSubDepartment.addEncodedQuery('parent.sys_idIN' + departments);
grSubDepartment.query();
while (grSubDepartment.next()) {
departments.push(grSubDepartment.sys_id.toString());
//gs.log(grSubDepartment.name);
}
} else {
var grHRProfile = new GlideRecord('sn_hr_core_profile');
grHRProfile.addEncodedQuery('user=' + currentUser.sys_id + '^position.positionLIKEpersonal assistant^ORposition.positionLIKEexecutive assistant');
grHRProfile.query();
if (grHRProfile.next()) {
//gs.log('*Current User in an assistant position: '+grHRProfile.position.position);
if (!currentUser.manager.department.nil() && currentUser.manager.department != currentUser.department) {
departments.push(currentUser.manager.department.sys_id.toString());
}
var grManagerEmployees = new GlideRecord('sys_user');
grManagerEmployees.addEncodedQuery('active=true^manager=' + currentUser.manager.sys_id + '^sys_id!=' + currentUser.sys_id + '^sys_id!=' + currentUser.manager.sys_id);
grManagerEmployees.query();

while (grManagerEmployees.next()) {
users.push(grManagerEmployees.sys_id.toString());
}
}
}
}

if (departments.length > 0) {
//gs.log('*Current User can manage this people: ');
var grUsers = new GlideRecord('sys_user');
grUsers.addEncodedQuery('active=true^departmentIN' + departments + '^sys_id!=' + currentUser.sys_id + '^sys_id!=' + currentUser.manager.sys_id);
grUsers.query();

while (grUsers.next()) {
//gs.log(grUsers.name);
users.push(grUsers.sys_id.toString());
}
}
}
return users;
},
getUsersIncLoggedInUser: function(currentUserID) {
var directReportsAndCurrentUser = (this.getUsers(currentUserID) == '') ? gs.getUserID() : this.getUsers(currentUserID) + ', ' + currentUserID;
return directReportsAndCurrentUser;
},
getManagerByHierarchy: function(currentUserID) {

var currentUser = new GlideRecord('sys_user');
currentUser.get(currentUserID);

var userManager = '';

var isCEO = currentUser.position.position.toString().toLowerCase().indexOf('chief executive officer') > -1;

if (!isCEO) {
if (currentUser.department) {

var statement = false;
var department = currentUser.department;
do {
var grDept = new GlideRecord('cmn_department');
grDept.get(department);
// gs.print('Checking - ' + grDept.name);
if ((currentUser.sys_id != grDept.dept_head) && grDept.dept_head) {
userManager = grDept.dept_head;
statement = false;
return userManager;
}
if (grDept.parent) {
department = grDept.parent;
statement = true;
}
} while (statement);
}
return userManager;
}

},
getIsManagerOrPA: function(userID) {

var isManagerOrPA = false; // Return value 0 indicates false

var gr = new GlideRecord('sn_hr_core_profile');
gr.addQuery('user', userID);
gr.query();

if (gr.next()) {
var checkManagerOrPA = gr.position.u_chief || gr.position.u_position_name == 'Personal Assistant' || gr.position.u_position_name == 'Executive Assistant';
}
if (checkManagerOrPA == true) {
isManagerOrPA = true;
}
return isManagerOrPA;
},

getIsHRAdmin: function(userID) {

var user = gs.getUser(); // Initialise the User Object
user = user.getUserByID(userID);

return user.isMemberOf('HR Admin');
},
type: 'DepartmentUsers'
};