script include in the Reference Qualifier of a catalog variable lookup select box.

[Jeff316]

Hi All,

I think I need to use a script include in reference qualifier field on the catalog variable of a lookup select box.

We don't have any catalog variables to date that have done that, so I'm not sure the syntax in the script include and the ref qualifier field to return the results below. It's almost like I need an if/then/else in the ref qualifier field but I know I cannot do that.

I have a catalog variable where the user will select an “Environment” which could be Prod, test, uat, sand, play, other.

I have another Lookup Select Box variable called “Size” that pulls from the “Size” table with 2 columns “Desc” and “Env”. I want to use a reference qualifier on this menu to…

 Only return to the "Size" variable's menu the 2 “Desc” records where “Env=prod” if the Environment variable selected in the field above was “Prod” else return all 4 "Desc" into the "Size" variables's menu options.

Desc         Env

Large        prod

Medium   prod

Small        non-prod

Tiny           non-prod

[Pradeep Sharma]

Hi,

Thanks for the update. I have updated my original script i.e if(!a) should be if(!en). That said, can you please let me know what value is printed for en in the log table. Is it sys_id? Also, I'm assuming u_environment is a string field? can you confirm?

In case you have the above setup on your personal dev instance, let me know and I can take a look.

-Pradeep Sharma

[Jeff316]

Hello Pradeep,

Thanks. Yes I had figured out !a was actually !en but wasn't sure since the results aren't quite right yet.

Below is what I have currently that when Environment variable on the form is Prod or not Prod, the results in the Size variable are all sizes where the u_environment is "Prod". The u_environment column in the size table is a string. 

Yes, the value of 'en' is the sys_id for the value "Prod" in the Environment field (which is a reference field variable)

I did not do this first in my PDI, I'm in our DEV instance. I didn't know I was going to get stumped on a lookup.

Thanks again for your time and helping me.

var getSize = Class.create();
getSize.prototype = {
initialize: function() {
},

getSize:function() {
var gp = ' ';
var en = current.variables.environment;
gs.log(' var en is '+en);

//return everything if environment is not Prod
if(!en)
return;

var grp = new GlideRecord('u_fnf_azure_request_form_vm_tshirt_size');
if(en =='6dfc43451b03af000560a9bfbd4bcb55')
{
grp.addQuery('u_environment','Prod');
}
grp.query();
while(grp.next())
{
if (gp.length > 0)
{

gs.log( 'GRP is '+grp);
gs.log( 'GRP.NEXT is '+grp.next);
gp += (',' + grp.sys_id);
gs.log(' GP is '+gp);

}
else {
gp = grp.sys_id;
}
}
// return Sizes where assigned to is in those groups we use IN for lists
return 'sys_idIN' + gp;
},
type: 'getSize'
};

[Pradeep Sharma]

Thanks for the details. Could you please create a sample Table and 2 variables in one of the catalog item and try to reproduce this on your PDI and share ONLY instance details with me?

-Pradeep Sharma

[Jeff316]

Thanks Pradeep. You have been very helpful.

I created a catalog item on my PDI, called PRADEEP.

That catalog item has the 2 variables with the 2 back-end tables and the script include.

They are copies of the objects in my DEV instance.

https://dev29133.service-now.com

If the user selects"Prod" from the Environment variable then the "Size" variable lookup should only show records from the "Size" table where environment=Prod.

Else If the user selects anything but "Prod" from the Environment variable then the "Size" variable should show all records from the "Size" table.

[Pradeep Sharma · Accepted solution]

Thanks for sharing the details. This was not working correctly because you had  ref_qual_elements=u_description defined under variable attribute section whereas it should be  ref_qual_elements=environment (environment is the reference variable name). I have updated it now and the lookup select box is now working fine as per your requirement.

-Pradeep Sharma