question Re: Querying Related Tables sc_req_item and sc_cat_item in Developer forum

Querying Related Tables sc_req_item and sc_cat_item

kailashthiyagar — Wed, 27 Jul 2016 15:59:34 GMT

My requirement is to pass in the "Requested Item number" and "requested item name" and check whether any records exist...

Noticed that name field is present in different table sc_cat_item... I m passing in the Requested item number and name from my client script.. This is the below code i m using...

but do we have an option to do dot walking and do this validation.. i tried it but it doesnt work... could any suggest how we can do it in simplied way?

var reqDetails = new GlideRecord('sc_req_item');
   //var catItem = reqDetails.addJoinQuery('sc_cat_item');
   reqDetails.addQuery('number',g_form.getValue('request_number_2'));
   //catItem.addCondition('name', 'Hardware Exception Appeal');
   reqDetails.query();
   //create and return the array of environments
   while(reqDetails.next()){
   var catItemData=new GlideRecord('sc_cat_item');
   catItemData.addQuery('sys_id',reqDetails.cat_item);
   catItemData.query();
   while(catItemData.next()){
   //do the logic over here
   }
}

Re: Querying Related Tables sc_req_item and sc_cat_item

Abhinay Erra — Wed, 27 Jul 2016 16:03:57 GMT

I will recommend to use GlideAjax.

GlideAjax - ServiceNow Wiki

Re: Querying Related Tables sc_req_item and sc_cat_item

sourabhd87 — Wed, 27 Jul 2016 16:04:14 GMT

Can you please let me know the need of this script? I am unable to identify your aim.

Kind regards,

Sourabh

Re: Querying Related Tables sc_req_item and sc_cat_item

kailashthiyagar — Wed, 27 Jul 2016 16:22:56 GMT

Hi Sourabh,

My requirement is, if the user enters in the request no(the one which he has already raised), we will be validating whether its a valid request number by querying against the requested item table

Re: Querying Related Tables sc_req_item and sc_cat_item

sourabhd87 — Wed, 27 Jul 2016 16:29:29 GMT

So best bet would be to use GlideAjax. As gliderecord use in client scripts not recommended.

Kind regards,

Sourabh

Re: Querying Related Tables sc_req_item and sc_cat_item

kailashthiyagar — Wed, 27 Jul 2016 16:48:02 GMT

Yes Sourabh.. we will be implementing the glide record functionality in Script include and will use glide ajax in the client script .. So ultimately, we will need to query tables.. rit?

Re: Querying Related Tables sc_req_item and sc_cat_item

Abhinay Erra — Wed, 27 Jul 2016 16:50:26 GMT

Yes you ill have to do all the querying stuff in the script include. Let me know if you need any help in scripting.

Re: Querying Related Tables sc_req_item and sc_cat_item

sourabhd87 — Wed, 27 Jul 2016 16:54:16 GMT

Yes thats correct. Just that you can dot walk multiple times in script includes. As mentioned by Abhinay, let us know if any help is required.

Kind regards,

Sourabh

Re: Querying Related Tables sc_req_item and sc_cat_item

kailashthiyagar — Wed, 27 Jul 2016 17:20:32 GMT

Could you help me in how we can do this using dot walking for this scenario.. Just the code on the script include alone s enough

Re: Querying Related Tables sc_req_item and sc_cat_item

sourabhd87 — Wed, 27 Jul 2016 18:14:14 GMT

eg: you can do current.assigned_to.department.name to get the department name of the current assigned_to person on an incident form.

In the same way you can replicate your scenario:

var reqDetails = new GlideRecord('sc_req_item');

//var catItem = reqDetails.addJoinQuery('sc_cat_item');

reqDetails.addQuery('number',"RITM0000016");

//catItem.addCondition('name', 'Hardware Exception Appeal');

reqDetails.query();

reqDetails.next();

gs.addInfoMessage(reqDetails.cat_item.name); // This will print the catalog item name

gs.addInfoMessage(reqDetails.cat_item.vendor.name); // this will print out the vendor name which is on the catalog item

Please mark the answer helpful or correct if you fell so.

Kind regards,

Sourabh

Re: Querying Related Tables sc_req_item and sc_cat_item

kailashthiyagar — Wed, 27 Jul 2016 18:31:58 GMT

Yes Sourabh.. we are not able to get the below one working.. its coming as undefined... So we are making second glide record query... we thought this dot notation should fetch the value but somehow its not..

gs.addInfoMessage(reqDetails.cat_item.name);

"reqDetails.cat_item" gives the Sys ID....

Then with the Sys ID, we are making second query to get the name... Its round about option:(

Re: Querying Related Tables sc_req_item and sc_cat_item

sourabhd87 — Wed, 27 Jul 2016 18:38:33 GMT

Hi Kailash,

I have already tested this script in business rule and it works well for me. Try writing a script include, pass the request number to the script include, and try returning the name of the cat_item. It has to work. Make sure to print out all the values you passed so that you are sure that you are passing correct values.

IF possible paste your client script and script include here so that I can test.

Kind regards,

Sourabh Dhaygude

Re: Querying Related Tables sc_req_item and sc_cat_item

Abhinay Erra — Wed, 27 Jul 2016 18:42:03 GMT

If you can tell me your entire use case and what you are trying to achieve, I will code it for you. It is very straight forward.

Re: Querying Related Tables sc_req_item and sc_cat_item

kailashthiyagar — Wed, 27 Jul 2016 19:56:51 GMT

This is the script include and client script which i m using.. For testing purpose, i m just returning the name from the script include and adding an alert in the client script.. Hope that is clear.

Here is the script include

validateRequestNumber: function(){

var answer=false;

var reqNo = this.getParameter('sysparm_req_number');

var reqDetails = new GlideRecord('sc_req_item');

reqDetails.addQuery('number',reqNo);

reqDetails.query();

//create and return the array of environments

while(reqDetails.next()){

return reqDetails.cat_item.name;

}

Here is the client script

	var ga = new GlideAjax('EUTSCatalogRITMValidation');
		var reqNo=g_form.getValue('request_number_2');
		ga.addParam('sysparm_name','validateRequestNumber');
		ga.addParam('sysparm_req_number',reqNo);
		ga.getXMLAnswer(validateReqNo);

function validateReqNo(response) {

alert(response);

}

Re: Querying Related Tables sc_req_item and sc_cat_item

Abhinay Erra — Wed, 27 Jul 2016 20:00:28 GMT

Can you paste your entire script include here. You are missing class declarations here. Did you check the client callable checkbox on the script include?

Re: Querying Related Tables sc_req_item and sc_cat_item

Abhinay Erra — Wed, 27 Jul 2016 20:02:16 GMT

In your client script, the call back function should be this

function validateReqNo(response) {

var answer=response.responseXML.documentElement.getAttribute("answer");

alert(answer);

}

Re: Querying Related Tables sc_req_item and sc_cat_item

kailashthiyagar — Wed, 27 Jul 2016 20:06:49 GMT

Hi i have included client callable in my script include

var EUTSCatalogRITMValidation = Class.create();

EUTSCatalogRITMValidation.prototype = Object.extendsObject(AbstractAjaxProcessor, {

validateRequestNumber: function(){

var answer=false;

var reqNo = this.getParameter('sysparm_req_number');

var reqDetails = new GlideRecord('sc_req_item');

reqDetails.addQuery('number',reqNo);

reqDetails.query();

//create and return the array of environments

while(reqDetails.next()){

return reqDetails.cat_item.name;

}

return answer;

type: 'EUTSCatalogRITMValidation'

});

Re: Querying Related Tables sc_req_item and sc_cat_item

Abhinay Erra — Wed, 27 Jul 2016 20:10:39 GMT

Here is your updated script include. Also as mentioned earlier, change the call back function in your client script as well.

var EUTSCatalogRITMValidation = Class.create();

EUTSCatalogRITMValidation.prototype = Object.extendsObject(AbstractAjaxProcessor, {

validateRequestNumber: function(){

var answer=[];

var reqNo = this.getParameter('sysparm_req_number');

var reqDetails = new GlideRecord('sc_req_item');

reqDetails.addQuery('number',reqNo);

reqDetails.query();

//create and return the array of environments

while(reqDetails.next()){

answer.push(reqDetails.cat_item.name);

}

return answer.toString();

type: 'EUTSCatalogRITMValidation'

});

Re: Querying Related Tables sc_req_item and sc_cat_item

kailashthiyagar — Wed, 27 Jul 2016 20:11:02 GMT

Hi Abhinay.. We can do either way also... but as an FYI, i have tried your apprpoach but it give undefined only

Re: Querying Related Tables sc_req_item and sc_cat_item

Abhinay Erra — Wed, 27 Jul 2016 20:12:42 GMT

use the updated script include form my previous comment