How to query HR profile field value from a Transform entry script of a 'sys_user' Target table

[savitha5]

Hi All,

We have a requirement to set a sys_user account to Active/Inactive using a Web service Transform map based on the field Date Hired from source table.

There are chances that the Date Hired will be imported as empty from source table. In such case, we want to validate the Employment Start Date of the user's HR Profile record (from sn_hr_core_profile) and set the sys_user profile to active if it is a future date, if it is not and it is already active then no action required. Since we don't have the Date field on sys_user table directly, I am trying to use Script include on HR scope and GlideAjax on my transform map entry script (Global scope).

Can anyone tell me how to achieve this. How to call a Scoped application script include on Transform map entry script. My script include is not triggered at all 😞 .

I have also shared my scripts below:
Script include:

var hr_StartDate = Class.create();
hr_StartDate.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
    getHRempStartDate: function() {
        var emp_id = this.getParameter('sysparm_emp_id');
        
        var hr_profile_gr = new GlideRecordSecure('sn_hr_core_profile');
        hr_profile_gr.addQuery('employee_number', emp_id);
        hr_profile_gr.addQuery('user.active', 'true');
        hr_profile_gr.query();
        gs.log('script include query: '+ hr_profile_gr.employee_number);
        if (hr_profile_gr.next()) {
        //    hr_profile_gr.next();
            var user_detail = hr_profile_gr.employment_start_date;
            gs.log('Emp start date on Script include: '+user_detail);
            return user_detail;
        }
    },
});

Transform map entry script :
Source table: My import set table
Use source script: true
Target table: sys_user
Target field: Active
Script:
var DateHired;
            var todayDt = new GlideDate();
            if (source.u_employee_active_inactive == "no") {
                if (source.u_date_hired == "") {

                    //DateHired = new GlideDate();
                    gs.log("Test DateHired " + DateHired);
                    var empno = source.u_personnel_number;
                    //**if no Date hire value then check for the user profile under HR Profile
                    gs.log("Emp no: " + empno);
                    var ga = new GlideAjax('sn_hr_core.hr_StartDate');
                    ga.addParam('sysparm_name', 'getHRempStartDate');
                    ga.addParam('sysparm_emp_id', empno);
                    ga.getXML(parseUserResponse);
                }
            }

            function parseUserResponse(response) {
                var user_detail = response.responseXML.getElementsByTagName("user_detail");
                gs.log('Employee start date: ' + user_detail);
                DateHired.setDisplayValue(user_detail, "dd-MMM-yyyy");
                gs.log('Hired date: '+DateHired);
                if (DateHired > todayDt) {
                    return "true";
                }
}

[The SN Nerd]

Hey,

You do not need to extend GlideAjax for this, transform maps run server-side.

That is why your script is not working.

If you are trying to borrow your existing GlideAjax code, you can refactor as follows:

Script Include

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

    getHRempStartDate: function() {
        var emp_id = this.getParameter('sysparm_emp_id');
        
        return this._getHRempStartDate(emp_id)
    },


    _getHRempStartDate:function(emp_id) {
		var hr_profile_gr = new GlideRecordSecure('sn_hr_core_profile');
        hr_profile_gr.addQuery('employee_number', emp_id);
        hr_profile_gr.addQuery('user.active', 'true');
        hr_profile_gr.query();
        gs.log('script include query: '+ hr_profile_gr.employee_number);
        if (hr_profile_gr.next()) {
        //    hr_profile_gr.next();
            var user_detail = hr_profile_gr.employment_start_date;
            gs.log('Emp start date on Script include: '+user_detail);
            return user_detail;
        }
    },

});

Transform Script

var dateHired;
var todayDt = new GlideDate();
if (source.u_employee_active_inactive == "no") {
    if (source.u_date_hired == "") {

        dateHired = new GlideDate();
        gs.log("Test DateHired " + DateHired);
        var empno = source.u_personnel_number;

        gs.log("Emp no: " + empno);

        var hrStartDate = new sn_hr_core.hr_StartDate();	
        var user_detail = hrStartDate._getHRempStartDate();
        dateHired.setDisplayValue(user_detail, "dd-MMM-yyyy");

        if (dateHired > todayDt) {
        	return "true";
    	}
    }
}

Please note that I have not tested this code and it may have other bugs yet to uncover. Had to fix up a few lines already.

[savitha5]

Hi Paul,

Thanks for the reply.

I tried to do GlideRecord on HR profile table within the Transform map entry script directly, but the control always shows the query result to false. I mean it is not taking into if(gr.next()) .

I have updated my script according to your comments, but script include is not returning any value.

Thanks,
Savitha

[The SN Nerd]

Some considerations:

• Is your Transform in the same scope as your Script Include?
• What scopes can your Script include run in?
• Can you test your code outside of the transform to ensure it works?
• Any errors in the logs?

[Sukraj Raikhraj]

Use an Onbefore script , to validate Date hired and set target value as needed.