Service Catalog items with dependent lookup select variable that itself references a dependent dictionary item

[PLakin1]

I've been attempting to create Category and Subcategory fields within a Service Catalog item that references the sc_request table. For that matter, I've been attempting to do the same thing with the incident table. In both tables, the Subcategory field is a dictionary item that is dependent on the Category selection.

I've read several threads with similar questions about creating dependencies in the Service Catalog using Variables similar to Dictionaries and all of them suggest using a ref_qual_element Variable attribute referencing the dependent variable. I understand this is to send the value of this field to the server and refresh the selection in the second variable.

They also utilize a Reference qualifier that uses javascript to pull the initial selection back in to filter the values of the dependent variable. If I understand how these work correctly, the following is true.

Variable 1) A Select Box, Lookup Select Box or a Reference.

In my catalog item I'm using a Select Box with a Choice table of sc_request and a Choice field of Category and I've named it category.

Variable 2) A lookup select box.

In my catalog item I'm using a Lookup table of sc_request and a Lookup field of Subcategory and I've named it subcategory.

Variable 2 has the following Reference qualifier:

javascript: 'subcategory='+current.variables.category

Variable 2 also has the following Variable attribute:

ref_qual_elements=category

The Category field returns the correct categories from the sc_request table, but the Subcategory field returns every possible option from the sc_request table subcategory. In fact, if I do not check the Unique values only checkbox it gives them to me about a dozen times each.

I know that the Variable attribute is working as, although there is a few seconds delay, if I open the drop-down selection of Subcategory directly after changing the Category, it closes the drop-down and forces me to reopen it, due to the Category value refreshing. The main problem is why the Subcategory is not properly filtered, and I believe this is because the dictionary I'm looking up already utilizes an existing dependency to filter itself.

Any help you can provide would be very much appreciated!

[Inactive_Us1957]

Hi,

Have you check "Unique values only" checkbox.

Thanks

[PLakin1]

Yes, and it performs as advertised, but my primary concern is that the Subcategories aren't filtering based upon the Categories. That being said, I undersold the number of duplicates when I explained that originally. I just took a closer look and there are probably about 600 copies of each if I leave unique values unchecked.

[Inactive_Us1957]

Then you have to write the onChange client script and call the script include in client script using GlideAjax. In the script include you have to glide on "sys_choice" table and do the query like dependent value is same as category value. This will return the choices and with the help of addOption() function, you can add the choices.

Client script:

function onChange(control, oldValue, newValue, isLoading) {
if (/*isLoading ||*/ newValue ==='') {
g_form.clearOptions('u_sub_category');
g_form.addOption('u_sub_category','none','--None--');
return;
}

g_form.clearOptions('u_sub_category');
g_form.addOption('u_sub_category','none','--None--');

var g= new GlideAjax('SCRIPT INCLUDE NAME');//script include name
g.addParam('sysparm_name','get_subcategory');//script include functio name
g.addParam('sysparm_cate_name',newValue);
g.getXML(getDetails);
}
function getDetails(response)
{
var values = response.responseXML.documentElement.getAttribute('answer'); // parsing the JSON response.

if(values == '')
{
g_form.clearOptions('u_sub_category');
g_form.addOption('u_sub_category','none','--None--');
return;
}

else
{
var val = JSON.parse(values);
for (var i=0; i< val.length; i++) {
g_form.addOption('u_sub_category',val[i],val[i]);
}
}
}

Script include function name(client callable check box should be true):

get_subcategory: function()
{
var category_name = this.getParameter('sysparm_cate_name');
var category =[];
gs.addErrorMessage('cat'+category_name);
var g = new GlideRecord('sys_choice');
g.addEncodedQuery('name=sc_request^dependent_value='+category_name);
g.query();
while(g.next())
{
gs.addErrorMessage('hello'+g.getValue('u_sub_category'));
category.push(g.getValue('value'));
}
var json = new JSON().encode(category);
return json;
},

[PLakin1 · Accepted solution]

Kanchan,

Thank you for your response. I'm sure the script would work very well, but you inspired me to find an easier way.

You reminded me about the sys_choice table and that the dependent checkbox in Dictionary entries is smoke and mirrors that effectively covers up the dependent_value column on the sys_choice table.

Note: For those that don't want a detailed explanation I added screenshots at the bottom.

First, the Category variable is still a reference to the Category column within the sc_request table, but I've changed the Subcategory variable to reference the Label column within the sys_choice table. However, rather than using a script, I'm using the very effective ServiceNow Reference qual attribute from the Type Specifications tab to simplify the filter process.

javascript: 'inactive=false^element=u_subcategory^dependent_value=' + current.variables.category

This attribute filters the results by removing inactive entries (inactive=false), limiting the results to the Subcategory Dictionary entry(element=u_subcategory), which was created in the Request table (sc_request) and returning only the Labels that contains a specific dependent_value.

The dependent_value is a column in the sys_choice table used to create symbolic links between the choices of one Dictionary entry with a dependency on another Dictionary enty. In this example, the Subcategory Dictionary entry we created in our Request table (sc_request) has a dependency on the Category Dictonary entry we created, so all our subcategory choices will have a dependent_value that matches a category choice.

Because the dependent_value column isn't part of our Subcategory Dictionary entry, if we try to reference it directly in the Lookup Select Box, it will return every subcategory instead of only returning the ones from a specific category.

Next, I used the following Variable attribute from the Default Value tab:

ref_qual_elements=category

This attribute causes the the list our Reference qual returns from the sys_choices table to refresh whenever the Category variable is updated.

Sorry for the long explanation, but I wanted to try to prevent others from going through the same difficulty I did. Without further ado, here are the pictures I promised: