Fetch records based on logged in user from a custom table

Rekha20
Tera Contributor

‎08-08-2025 11:11 AM

Hi All, 

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

Rekha20_0-1754676498769.png

 

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

 

Rekha20_1-1754676587964.png

 

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.
0 Helpfuls
  • 1,618 Views
13 REPLIES 13

Sunny3008
Tera Guru

‎08-08-2025 11:43 AM

 

Hello @Rekha20 ,

It seems you're passing the Catalog Item sys_id from the client script, but in your Script Include, you're querying the u_hr_service field on your custom table. This mismatch could be the reason why no valid request types are returned.

To fix this, please make sure your custom table sn_hr_core_request_type has appropriate reference fields (like u_catalog_item or u_record_producer), and then adjust your Script Include query accordingly.

For example, if you’re associating the request type with the Catalog Item, then you can update your GlideRecord query like this:

 

 
var gr = new GlideRecord('sn_hr_core_request_type'); gr.addQuery('u_catalog_item', hrServiceID); // Replace with the correct field gr.addQuery('u_active', true); gr.query();
 

Make sure the field u_catalog_item (or your appropriate reference field) is correctly populated with the Catalog Item or Record Producer references.

 

✅ Mark this as helpful if it resolves your issue.

 

Thanks,
Sunny

0 Helpfuls

Rekha20
Tera Contributor
In response to Sunny3008

‎08-10-2025 02:21 AM - edited ‎08-10-2025 02:38 AM

Hi @Sunny3008 

 

I have updated a code more direct:

 

Client Callable script include:

 

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

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

        var userID = this.getParameter('sysparm_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 fixed HR Service
        var validRequestTypes = [];
        var gr = new GlideRecord('sn_hr_core_request_type');
        gr.addEncodedQuery("u_hr_service=fe56b4ae3bdaa2502f83b50826e45ad9^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.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'
});
 
 
On Change Catalog Client Script: 
 
function onChange(control, oldValue, newValue, isLoading) {
//    if (isLoading || newValue == '') {
//       return;
//    }

   //Type appropriate comment here, and begin script below

    alert("Client script fired, newValue = " + newValue);
    var ga = new GlideAjax('sn_hr_core.HRCustomUtils1');
    ga.addParam('sysparm_name', 'getRequestTypeQualifier');
    ga.addParam('sysparm_user_id', newValue);

    ga.getXMLAnswer(function(response) {
        if (response) {
            alert("1");
            g_form.setValue('u_request_type', response);
            g_form.setValue('u_request_type', '');
        } else {
            alert("No valid request types returned.");
        }
    });  
}
 
Issue:
All script include logs are coming correctly, query result is correct but from client script alert is coming "1" but not updating drop down for Request Type on portal.
 
Could you please help me further? Much appreciate your input.
 
 

 

0 Helpfuls

Sunny3008
Tera Guru
In response to Rekha20

‎08-10-2025 09:22 AM

Hello @Rekha20 ,

 

Can you please let me know what is type of variable 'u_request_type'.

 

If it's select box then you can add choices using.

g_form.addOption('u_request_type', 'response', 'response', 1);

 

Kindly help with additional details.

 

 Mark this as helpful if it resolves your issue.

 

Thanks,

Sunny R

0 Helpfuls

Rekha20
Tera Contributor
In response to Sunny3008

‎08-10-2025 10:41 AM - edited ‎08-10-2025 10:54 AM

Hi @Sunny3008 

Yes, Request Type is Look up select box.

 

FYI- response from Script include is coming as: 

Rekha20_0-1754848468810.png

 

0 Helpfuls