question Populating a Variable on sc_req_item table on update of catalog variable in Developer forum

Populating a Variable on sc_req_item table on update of catalog variable

AlexC1256158230 — Wed, 21 Dec 2016 15:46:02 GMT

I have a need to be able to populate a variable located on the request item table (u_tra_amnt) when a catalog variable (tra_amnt_given) is updated from the task level. I know I need a business rule and I have been able to run a couple and successfully populate that variable on submit, but if any changes are made to that variable, it doesn't update. I am using after update functionality, but it's not working.

The script I am using is current.u_tra_amnt = current.variables.tra_amnt_given;

That's a BR on the sc_req_table

Any thoughts??

Thank you all in advance!

Re: Populating a Variable on sc_req_item table on update of catalog variable

drjohnchun — Wed, 21 Dec 2016 15:50:49 GMT

Hi Alex - you can't dot walk into variables, so your "current.variables.tra_amnt_given" wouldn't work. Below is a reply I provided for another post that helped resolve the issue.

Using GlideRecord to Query Tables 3.6 Querying Service Catalog Tables reads:

You cannot directly query the variables of the Service Catalog Request Item table [sc_req_item]. Instead, query the Variable Ownership table [sc_item_option_mtom] by adding two queries, one for the variable name and another for the value. The query returns the many-to-many relationship, which you can dot-walk to the requested item. The following example finds the request items that have the variable named 'item_name' with a value of 'item_value' and displays the request item numbers:

and provides this example:

var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('sc_item_option.item_option_new.name','item_name');
gr.addQuery('sc_item_option.value','item_value');
gr.query();

while (gr.next()) {
       gs.addInfoMessage(gr.request_item.number);
}

Hope this helps.

Please feel free to connect, follow, mark helpful / answer, like, endorse.

John Chun, PhD PMP

Winner of November 2016 Members' Choice Award

Re: Populating a Variable on sc_req_item table on update of catalog variable

AlexC1256158230 — Wed, 21 Dec 2016 15:59:34 GMT

Alright, so I think I see what you are saying in this but I might need a little more hand holding as I want it to populate a variable and not just give me the item numbers that have this variable.

So... if I were to make a business rule....

Table: sc_req_item

when: after

update: true

script:

var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('sc_item_option.item_option_new.name','tra_amnt_given');
gr.addQuery('sc_item_option.value','tra_amnt_given');
gr.query();

while (gr.next()) {
??????????????????????????;
}

Re: Populating a Variable on sc_req_item table on update of catalog variable

drjohnchun — Wed, 21 Dec 2016 16:44:36 GMT

Here's what can be done first to make sure the query is working. Can you run this in Background Script and tell me what the output is? Use the sys_id of a sc_req_item record that you want to test with and also set the item_name and item_value variables to the correct values before running it:

var sys_id = current.sys_id, item_name = 'tra_amnt_given', item_value = '???';   // set correct values here
var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('request_item',sys_id);
gr.addQuery('sc_item_option.item_option_new.name',item_name);
gr.addQuery('sc_item_option.value',item_value);
gr.query();

gs.info(gr.getRowCount());

Re: Populating a Variable on sc_req_item table on update of catalog variable

purbali1 — Wed, 21 Dec 2016 16:58:44 GMT

Why to write BR on Request item table when your variable is getting updated on mtom table.

From what I got your requirement, you need to write BR sc_item_option table

BR table- sc_item_option

condition- item_option_new.name==tra_amnt_given&& current.value changes()

script-

var gr = new GlideRecord('sc_item_option_mtom');

gr.addEncodedQuery('sc_item_option.value='+current.value+'^sc_item_option.item_option_new.name=tra_amnt_given'); //double check my encoded query string syntax

gr.query();

if(gr.next()) {

gr.request_item.u_tra_amnt= current.value;

gr.update();

}

Re: Populating a Variable on sc_req_item table on update of catalog variable

AlexC1256158230 — Wed, 21 Dec 2016 17:12:12 GMT

it says there is an error at line one. I assume I am supposed to just put a test value in there for an item that I have already entered?

Re: Populating a Variable on sc_req_item table on update of catalog variable

drjohnchun — Wed, 21 Dec 2016 17:14:27 GMT

Can you post the script you're using? You'll need to set all 3 variables to correct values.

Re: Populating a Variable on sc_req_item table on update of catalog variable

AlexC1256158230 — Wed, 21 Dec 2016 17:18:20 GMT

var sys_id = current.sys_id
var item_name = 'tra_amnt_given'
var item_value = 23;

var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('request_item',sys_id);
gr.addQuery('sc_item_option.item_option_new.name',item_name);
gr.addQuery('sc_item_option.value',item_value);
gr.query();
gs.info(gr.getRowCount());

Re: Populating a Variable on sc_req_item table on update of catalog variable

drjohnchun — Wed, 21 Dec 2016 17:23:28 GMT

You need to replace current.sys_id with the sys_id of the test record from sc_req_item - do you know how to get that? "current.sys_id" works in your BR, but not in Background Script.

Re: Populating a Variable on sc_req_item table on update of catalog variable

AlexC1256158230 — Wed, 21 Dec 2016 17:23:38 GMT

That didn't seem to work. and I wish I could double check your encoded query but I'll admit, I don't know what the sytax is supposed to be for that. I don't ever use encoded query.

Re: Populating a Variable on sc_req_item table on update of catalog variable

AlexC1256158230 — Wed, 21 Dec 2016 17:27:38 GMT

Ah gotchya. Now with this:

var sys_id = '9391db4a0fb3a680ac50305be1050e9f';
var item_name = 'tra_amnt_given';
var item_value = '23';

it is returning: *** Script: 0

Re: Populating a Variable on sc_req_item table on update of catalog variable

AlexC1256158230 — Wed, 21 Dec 2016 17:29:33 GMT

wait wait wait! I put the wrong number in - I was reading a different field! I got it to return 1, which is correct.

Re: Populating a Variable on sc_req_item table on update of catalog variable

drjohnchun — Wed, 21 Dec 2016 17:33:38 GMT

Can you run this and see what you get?

var sys_id = '9391db4a0fb3a680ac50305be1050e9f';
var item_name = 'tra_amnt_given';
var item_value = '23'; 

var gr = new GlideRecord('sc_item_option_mtom');   
gr.addQuery('request_item',sys_id);   
//gr.addQuery('sc_item_option.item_option_new.name',item_name);   
//gr.addQuery('sc_item_option.value',item_value);   
gr.query();   
gs.info(gr.getRowCount());

and

var sys_id = '9391db4a0fb3a680ac50305be1050e9f';
var item_name = 'tra_amnt_given';
var item_value = '23'; 

var gr = new GlideRecord('sc_item_option_mtom');   
gr.addQuery('request_item',sys_id);   
gr.addQuery('sc_item_option.item_option_new.name',item_name);   
//gr.addQuery('sc_item_option.value',item_value);   
gr.query();   
gs.info(gr.getRowCount());

Re: Populating a Variable on sc_req_item table on update of catalog variable

AlexC1256158230 — Wed, 21 Dec 2016 17:36:18 GMT

for the first one I get

*** Script: 93

and for the second one I get

*** Script: 1

Re: Populating a Variable on sc_req_item table on update of catalog variable

drjohnchun — Wed, 21 Dec 2016 17:44:19 GMT

So looks like you don't have the variable value of '23' for 'tra_amnt_given' in the sc_item_option table - can you confirm?

I also read purbali's comment and need to confirm what's triggering what change. Do you want

Changes to a variable value in sc_item_option will update a field value in sc_req_item

If so, you need to have this BR in sc_item_option as purbali said. Can you confirm?

Re: Populating a Variable on sc_req_item table on update of catalog variable

AlexC1256158230 — Wed, 21 Dec 2016 17:59:11 GMT

No, I do. That's why I thought the script was throwing back a value of one.

And I think so?

What will happen is a user will input a request and one of the variables on the form (tra_amnt_given) will be left blank. Then a task will be assigned to Jane and while in that task, Jane will input a price in tra_amnt_given. After she has saved/closed the task, I want the value she put in that variable to populate a variable on the requested item table called u_tra_amnt.

Re: Populating a Variable on sc_req_item table on update of catalog variable

drjohnchun — Wed, 21 Dec 2016 20:02:00 GMT

Do you agree that the BR should be added to the sc_item_option table and when the value of the tra_amnt_given variable changes, the new value is written to the u_tra_amnt field in the sc_req_item table?

If so, you can still use the sc_item_option_mtom table to locate the record to update in the sc_req_item table you need to update.

Please let me know if this is the case and how I may help.

Re: Populating a Variable on sc_req_item table on update of catalog variable

SHA3 — Fri, 03 Mar 2017 15:59:43 GMT

Hi Alex,

I have similar requirement. Did you get a solution to this?