Fetch records based on logged in user from a custom table

Rekha20 · a month ago

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 · a month ago

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

Rekha20 · 4 weeks ago

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.

Sunny3008 · 4 weeks ago

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

Rekha20 · 4 weeks ago

Hi @Sunny3008

Yes, Request Type is Look up select box.

FYI- response from Script include is coming as: