Hello @servicenow_devo
We also had similar requirement but on different variables. You can follow the below steps to achieve this requirement.
Pre-requisites :
1.Need below Variables on Catalog Item
A] Variable Name : Requested For
Type : reference
Referenced Table : sys_user
B] Variable Name : Request Type
Type : Select Box //I used a select box here
Referenced Table : question_choice
Dont add any choices in related list
Step 1 : Create a new Entry in 'Question'[question] table. I have created 2 Entries for specific Company for demo
E.g.,
For Company : ACME Italy created one entry
Type : Single line text
Question : ACME Italy
For Company : Acer created one entry
Type : Single line text
Question : Acer
Step 2 : Create a choices in 'question_choice' tables for each question
I'll show you for question 'Acer' just we have created in step 1
Select Question as a Acer in reference field as shown in below screen shot.
We can create a 'N' number of entries in "Question choice" table for question 'Acer' and 'ACME Italy'
For ACME Italy
Step 3 : Write a Below Client callable Script include and onChange client script to get choices
Script Include :
var getCompanyChoiceList = Class.create();
getCompanyChoiceList.prototype = Object.extendsObject(AbstractAjaxProcessor, {
getChoices: function() {
var reqFor = this.getParameter('sysparm_reqFor');
var company;
var choices=[];
var grUser = new GlideRecord('sys_user');
grUser.addQuery('sys_id', reqFor);
grUser.query();
if (grUser.next()) {
company = grUser.getDisplayValue('company').toString(); //will get Display name of company
}
//gs.log('getCompanyChoiceList:company=' + company);
//glide record on quetion choice table
var grChoices = new GlideRecord("question_choice");
grChoices.addEncodedQuery("question.question_text=" + company);
grChoices.query();
while (grChoices.next()) {
choices.push (grChoices.getValue('value').toString());
}
//gs.log('getCompanyChoiceList:choice=' + choices);
return JSON.stringify(choices);
},
type: 'getCompanyChoiceList'
});
onChnage Client script :
function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue == '') {
return;
}
var reqFor = g_form.getValue('requested_for');
var gaINCUsers = new GlideAjax('getCompanyChoiceList'); // Glide Ajax
gaINCUsers.addParam('sysparm_reqFor',reqFor ); // variable values passed to script include
gaINCUsers.addParam('sysparm_name', 'getChoices'); // function name in script include
gaINCUsers.getXMLAnswer(callback);
function callback(answer) {
var results = JSON.parse(answer);
var getValue = g_form.getValue('request_type');
for(var i=0; i<results.length ;i++){
g_form.addOption('request_type',results[i],results[i]);
}
}
}
Output :
Test Case 1 : Requested for has company : Acer
Test Case 2 : Requested for has company : ACME Italy
You can adjust code with your variable names and with best practices.
Hope this will work for you...!!!
