question How to populate custom field on Request table with value from Requested Item table? in Developer forum

How to populate custom field on Request table with value from Requested Item table?

jaredholm — Tue, 21 Aug 2018 06:30:55 GMT

I would like to populate a custom string field (could be changed to a reference field if that is more appropriate?) called "u_catalog_item" I have created on the Request (sc_request) table with the value of the Item (cat_item) field on the Requested Items (sc_req_items) table. I need to filter REQs based on the Catalog Item from which they were created. For example, I have a catalog item "Request to Pull Email". The only place the name of this catalog item appears is on the RITM table. So, I have to populate the field on the REQ table with the "name" value on the RITM table. This requires matching the records based on REQ### and then populating the custom field. I have created a business rule to automatically populate the custom field, but am getting stuck on the GlideRecord query to grab the Request numbers and match them to copy the name data. I currently have an after Insert or Update BR that has a script that looks like this:

   var cat_item = current.u_catalog_item;
   var gs = new GlideRecord('sc_req_item');
   gs.addQuery('request');
   gs.query();
   while (request == sc_request.request){
cat_item = sc_req_item.name;
   }

Can somebody please tell me what is wrong with this script? Much appreciated

Re: How to populate custom field on Request table with value from Requested Item table?

Omkar Mone — Tue, 21 Aug 2018 06:35:22 GMT

Firstly you gave used gs which is the Global object provided by servicenow for GlideSystem.

Don't use that and use gr or any other name you wish to.

And if you want to retirve all the requests the remove the addQuery and just use query.

then

var gr = new GlideRecord('sc_req_item');

gr.query();

while(gr.next())

{

if(//here check you conditions)

{

sc_req_item.name = cat_item;

}

Mark correct if it helps.

Regards,

Omkar Mone

www.dxsherpa.com

Re: How to populate custom field on Request table with value from Requested Item table?

jaredholm — Tue, 21 Aug 2018 06:48:43 GMT

Hi Omkar,

I currently have this and it still is not working:

var cat_item = current.u_catalog_item;
var num = current.number;

var gr = new GlideRecord('sc_req_item');
gr.query();
while (gr.next){
if (sc_req_item.request == num){
cat_item = sc_req_item.name;
}
}

Is there anything else I should be doing?

Re: How to populate custom field on Request table with value from Requested Item table?

Omkar Mone — Tue, 21 Aug 2018 06:57:03 GMT

if (sc_req_item.request == num){
cat_item = sc_req_item.name;
}

This is wrong. You want the request feild value from sc_req_table??

the use gr.request and gr.name.

Thanks.

Re: How to populate custom field on Request table with value from Requested Item table?

jaredholm — Tue, 21 Aug 2018 07:14:07 GMT

var cat_item = current.u_catalog_item;

var num = current.number;

var gr = new GlideRecord('sc_req_item');
gr.query();
while (gr.next){
  if (gr.request == num){
   cat_item = gr.cat_item.name;
  }
}

This query ran for over 4 minutes and then did not even update the field.

Re: How to populate custom field on Request table with value from Requested Item table?

Omkar Mone — Tue, 21 Aug 2018 07:17:22 GMT

gr.cat_item.name -> wrong.

gr.name ->right

Re: How to populate custom field on Request table with value from Requested Item table?

Omkar Mone — Tue, 21 Aug 2018 07:19:45 GMT

Please send me the screenshots of the Br or whatever you have written because you are writing it wrong.

Thanks

Re: How to populate custom field on Request table with value from Requested Item table?

jaredholm — Tue, 21 Aug 2018 07:39:06 GMT

Re: How to populate custom field on Request table with value from Requested Item table?

jaredholm — Tue, 21 Aug 2018 07:40:48 GMT

Re: How to populate custom field on Request table with value from Requested Item table?

palmen — Tue, 21 Aug 2018 07:46:21 GMT

The issue is your if statement.

gr.request will get the sys_id of the request.
num is the number of the current record.

You'll never have a match between a number of a record and a sys_id.

Change var num = current.number to var reqID = current.getUniqueValue()
By doing this you get the sys_id of the current record and can compare it to another sys_id

Re: How to populate custom field on Request table with value from Requested Item table?

Omkar Mone — Tue, 21 Aug 2018 07:47:15 GMT

so do when you are saying name what exactly do you want to populate? Because there is no name feild available . Or do you want the display value to RITM ?

Re: How to populate custom field on Request table with value from Requested Item table?

jaredholm — Tue, 21 Aug 2018 08:00:24 GMT

I want the display value of the Item field on the RITM table because this is the name of the catalog item that I need to populate my custom field.

Re: How to populate custom field on Request table with value from Requested Item table?

palmen — Tue, 21 Aug 2018 08:05:32 GMT

To get the display value use this

gr.getDisplayValue('cat_item');

Re: How to populate custom field on Request table with value from Requested Item table?

palmen — Tue, 21 Aug 2018 08:11:25 GMT

Script should be something like this

var reqID = current.getUniqueValue()
 
var gr = new GlideRecord('sc_req_item');
gr.addQuery('request', reqID);
gr.query();
if (gr.next()){
   current.setValue('u_catalog_item', gr.getDisplayValue('cat_item'));
}

Re: How to populate custom field on Request table with value from Requested Item table?

jaredholm — Tue, 21 Aug 2018 08:20:32 GMT

I changed the code to what you have above, the update topped off at 300 seconds and did not complete. Not sure where to go from here.

Re: How to populate custom field on Request table with value from Requested Item table?

palmen — Tue, 21 Aug 2018 08:44:46 GMT

That's strange, shouldn't take that amount of time.

Try adding some logs to the script to see what values you actually get

var reqID = current.getUniqueValue()
gs.log('Request SYS ID: ' + reqID);
 
var gr = new GlideRecord('sc_req_item');
gr.addQuery('request', reqID);
gr.query();
gs.log('BEFORE IF');
if (gr.next()){
   gs.log('ITEM NAME: ' + gr.getDisplayValue('cat_item'))
   current.setValue('u_catalog_item', gr.getDisplayValue('cat_item'));
}

Do you have something else that triggers on the request that could cause this?

Another issue you have is if you have more than 1 request item related to a request, but that might not be the case for you?