Fetch records based on logged in user from a custom table

[Rekha20]

Hi All, 

I have created a custom a table sn_hr_core_request_type, having fields like below

I have created an HR Service which associated with an Record Producer, record producer is having variable:

I need to show Request type choices based on available for country field based on who is this request for variable changes.

I have craeted script include: 

var HRRequestTypeQualifier = Class.create();

HRRequestTypeQualifier.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {

    getRequestTypeQualifier: function() {

        var userID = this.getParameter('sysparm_user_id');

        var hrServiceID = this.getParameter('sysparm_hr_service_id');

        if (!userID || !hrServiceID)

            return "sys_idINnone";

        // Get user's country via HR Profile

        var hrProfileGR = new GlideRecord('sn_hr_core_profile');

        hrProfileGR.addQuery('user', userID);

        hrProfileGR.query();

        var userCountry = '';

        if (hrProfileGR.next() && hrProfileGR.user.u_country)

            userCountry = hrProfileGR.user.u_country.toString();

        var validRequestTypes = [];

        var gr = new GlideRecord('sn_hr_core_request_type');

        gr.addQuery('u_hr_service', hrServiceID);

        gr.addQuery('u_active', true);

        gr.query();

        while (gr.next()) {

            var available = gr.u_available_for_country.toString();

            var notAvailable = gr.u_not_available_for_country.toString();

            var include = true;

            if (available && available.indexOf(userCountry) === -1)

                include = false;

            if (notAvailable && notAvailable.indexOf(userCountry) !== -1)

                include = false;

            if (include)

                validRequestTypes.push(gr.getUniqueValue());

        }

        if (validRequestTypes.length === 0)

            return "sys_idINnone";

        return "sys_idIN" + validRequestTypes.join(',') + "^ORDERBYu_order";

    },

    type: 'HRRequestTypeQualifier'

});

Catalog client script: 

function onChange(control, oldValue, newValue, isLoading) {

    if (isLoading || newValue === '') return;

    var ga = new GlideAjax('HRRequestTypeQualifier');

    ga.addParam('sysparm_name', 'getRequestTypeQualifier');

    ga.addParam('sysparm_user_id', newValue);

    ga.addParam('sysparm_cat_item', g_form.getUniqueValue()); // record producer sys_id

    ga.getXMLAnswer(function(response) {

        if (response) {

            g_form.setValue('u_request_type', response);

        } else {

            alert("No valid request types returned.");

        }

    });

}

but this is not working. Please help with the code.

[Sunny3008]

Hello @Rekha20 ,

Thanks for clarification regarding variable.

As you're using lookup select box, Kindly update reference qualifier using following code.

javascript: sn_hr_core.HRCustomUtils1().getRequestTypeQualifier(current.variables.User_id_variable_name);

Kindly change sn_hr_core if script include belong to different scope and update 'User_id_variable_name' with your request for variable name. After javascript keyword there should be colon and then script include name.

 and update variable attributes with : 

ref_qual_elements =User_id_variable_name

update 'User_id_variable_name' with your request for variable name.

and update script include function initial part like following with parameter in function

  getRequestTypeQualifier: function(user_id) {
        gs.info("[HRCustomUtils1] === Method getRequestTypeQualifier() TRIGGERED ===");

        var userID = user_id;
        gs.info("[HRCustomUtils1] Incoming userID: " + userID);

Disable client script and Kindly try this way as it's loop up select box.

 Mark this as helpful if it resolves your issue.

Thanks,

Sunny R

[Rekha20]

Hi @Sunny3008 

Thanks for your response.

I have updated Script Include: Client Callable-

var HRCustomUtils1 = Class.create();

HRCustomUtils1.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {

 getRequestTypeQualifier: function(user_id) {

        gs.info("[HRCustomUtils1] === Method getRequestTypeQualifier() TRIGGERED ===");

        var userID = user_id;

        gs.info("[HRCustomUtils1] Incoming userID: " + userID);

        if (!userID) {

            gs.info("[HRCustomUtils1] No userID provided.");

            return "sys_idINnone";

        }

        // Get user's country from HR Profile

        var userCountry = '';

        var hrProfileGR = new GlideRecord('sn_hr_core_profile');

        hrProfileGR.addQuery('user', userID);

        hrProfileGR.query();

        if (hrProfileGR.next() && hrProfileGR.user.u_country) {

            userCountry = hrProfileGR.user.u_country.toString();

        }

        gs.info("[HRCustomUtils1] User country: " + userCountry);

        // Query request types for Other Benefit Inquiry HR Service

        var validRequestTypes = [];

        var gr = new GlideRecord('sn_hr_core_request_type');

        gr.addEncodedQuery("u_hr_service=9e414c3c2bd3a2907564f0486e91bfc6^u_active=true");

        gr.query();

        while (gr.next()) {

            var availableCountries = gr.u_available_for_country.toString();

            gs.info("[HRCustomUtils1] Checking: " + gr.getDisplayValue('name') +

                   " | Available for: " + availableCountries);

            if (!availableCountries || availableCountries.indexOf(userCountry) !== -1) {

                validRequestTypes.push(gr.getUniqueValue());

                gs.info("[HRCustomUtils1]  -> Added");

            }

        }

        if (validRequestTypes.length === 0) {

            gs.info("[HRCustomUtils1] No matches found.");

            return "sys_idINnone";

        }

        var query = "sys_idIN" + validRequestTypes.join(',') + "^ORDERBYu_order";

        gs.info("[HRCustomUtils1] Final query: " + query);

        return query;

    },

    type: 'HRCustomUtils1'

});

Reference Qualifier:

Variables Name:

This way it is not triggering any logs. nothing. 

Should it not be server side code if calling in advance reference qualifier?

Please suggest me further.

[Rekha20]

Hi @Sunny3008 

Seems working after making script Include sever side. One issue I see- Drop down choices are not in order defined in Request Type table as we have u_order field on this table.

[Sunny3008]

Hello @Rekha20 ,

Kindly try to update variable attribute as

ref_ac_order_by=u_order,ref_qual_elements=subject_person

 Mark this as helpful if it resolves your issue.

Thanks,

Sunny R

[Rekha20]

Hi @Sunny3008 

Tried but no luck.

Tried: 

ref_auto_completer=AJAXTableCompleter;ref_order_by=u_order still no luck