Need to populate the bottom most department of a user

[Lak]

For any given user(Mike), We would need to show the bottom most department(XYZ).

Note: User could be associated to any department in the hierarchy, any help or suggestions

User: Mike

Department:

                      --ABC

                         --BCD

                             --CDE

                                 --XYZ

[AshishKM]

ok, so you already have user's department data from successfactors and same is inserted/or/updated in user profile [ sys_user ] table in ServiceNow then why you are looking form Nth level, and where exactly you are using this Nth level.

Share some screen shot from ServiceNow where it is intended to use. 

[Lak]

Hi @AshishKM ,

Yes, you are right that department is pulled and updated against the user profile. What the ask is if the same department field(reference field in sys_user table) can be used to show the nth level through advanced reference qualifier or should we create another field called  "Nth department" and populate the same.

Note: The business wanted the nth level and not the one which is updated from successfactors for the user profile.

regards,

Lakshmi 

[AshishKM · Accepted solution]

Ok , got it, you can use this code. you might required some minor adjustments for input and output parameters. 

created a new column on my PDI for this Nth level and used the calculated value to display Nth value based on current department.

updated the script code for this

you can copy exact code in calculated value, its generic as its taking the current record department for nth level

(function calculatedFieldValue(current) {

	var deptArray = []; 
	getLastLevel(current.department);

	function getLastLevel(dept){
		var grDept = new GlideRecord("cmn_department");
		    grDept.addQuery("parent",dept);
			grDept.query();
			if(grDept.next()){
					deptArray.push(grDept.sys_id);
					getLastLevel(grDept.sys_id);
				}
}
return deptArray[deptArray.length-1];
})(current);

result

[Lak]

Hi @AshishKM , @Amit Gujarathi 

My requirement is to create another reference field and update the nth level department into that field through advanced reference qualifier.

Tried the below code but didn't seems to be working,

var HRSDGetNthLevelDepartment = Class.create();

HRSDGetNthLevelDepartment.prototype = {

    initialize: function() {

    },

calculatedFieldValue: function(current) {

        var deptArray = [];

        this.getLastLevel(current.u_department, deptArray);

        return deptArray[deptArray.length - 1];

    },

    getLastLevel: function(dept, deptArray) {

        if (dept) {

            var grDept = new GlideRecord("u_department_hrsd"); //custom table for department

            grDept.addQuery("u_parent", dept);

            grDept.query();

            while (grDept.next()) {

                deptArray.push(grDept.sys_id);

                this.getLastLevel(grDept.sys_id, deptArray);

            }

        }

    },

    type: 'HRSDGetNthLevelDepartment'

};

This is my reference qualifier:    javascript:new HRSDGetNthLevelDepartment().calculatedFieldValue();

Regards,

Lakshmi

[AshishKM]

Hi @Lak , 

Please review my last reply , where I created a new reference variable pointing to Department table. Use the calculated value option and copy & past the given code, it will work. 

-Thanks