How to get values from other tables in CatalogItem to narrow down reference choices

[LeiM]

In the ServiceNow catalog item, there are 3 variables named us_headquarters, us_office, and us_shop, all of type Reference, that refer to the table "u_info_table".

The table "u_info_table" contains variables u_headquarter_cd, u_office_cd, and u_shop_cd, all of String type.

I would like to get the u_headquarter_cd of the record selected by us_headquarters in the application, and narrow down the choices so that only u_office_cd and u_shop_cd of the same data are displayed in the drop-down.

As an example, if the records in the table look like the following, the choices for each will be narrowed down as follows

Example : 

If 27 is selected for us_headquarters:

- us_office is narrowed down to 1

- us_shop is narrowed down to 12 and 8

If 19 is selected in us_headquarters:

- us_office is narrowed down to 8

- us_shop is narrowed down to 35

u_headquarter_cd	u_headquarter_nm	u_office_cd	u_office_nm	u_shop_cd	u_shop_nm
27	Headquarter	1	officeA	12	shopR
27	Headquarter	1	officeA	8	shopG
19	Other	8	officeB	35	shopX

Here is Script I tried.

I think getting values from the table succeeded, but still unable to narrow it down properly.

Script Include

var FilterCD = Class.create();
FilterCD.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getFilteredOptions: function() {
        var sysId = this.getParameter('sysparm_headquarter_cd');

        var headquarterCdString = this._getHeadquarterCdString(sysId);

        var data = { us_office: [], us_shop: [] };

        if (headquarterCdString) {
            var gr = new GlideRecord('u_info_table');
            gr.addQuery('u_headquarter_cd', headquarterCdString);
            gr.query();

            while (gr.next()) {
                if (data.us_office.indexOf(gr.u_office_cd.toString()) == -1) {
                    data.us_office.push(gr.u_office_cd.toString());
                }
                if (data.us_shop.indexOf(gr.u_shop_cd.toString()) == -1) {
                    data.us_shop.push(gr.u_shop_cd.toString());
                }
            }
        }

        return JSON.stringify(data);
    },

    _getHeadquarterCdString: function(sysId) {
        var gr = new GlideRecord('u_info_table');
        if (gr.get(sysId)) {
            return gr.u_headquarter_cd.toString();
        }
        return null;
    }
});

onChange "us_headquarters" Catalog Client Script

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue === '') {
        return;
    }
    var ga = new GlideAjax('FilterCD');
    ga.addParam('sysparm_name', 'getFilteredOptions');
    ga.addParam('sysparm_headquarter_cd', newValue);
    ga.getXMLAnswer(function(response){
        
        var data = JSON.parse(response);
        g_form.clearOptions('us_office');
        if (data.us_office && data.us_office.length > 0) {
            data.us_office.forEach(function(cd) {
                g_form.addOption('us_office', cd, cd);
            });
        } 
        
        g_form.clearOptions('us_shop');
        if (data.us_shop && data.us_shop.length > 0) {
            data.us_shop.forEach(function(cd) {
                g_form.addOption('us_shop', cd, cd);
            });
        } 
    });
}

[Brad Bowman]

Since these are three reference type variables referring to the same table, you can filter the results for office and shop, but the display value(s)/column(s) will be the same for all three.  So for 27, you'll still see two rows for 1.  It sounds like you want to change office and shop to select box type variables, on which you can use addOption to build a list like you are trying to do with your scripts, or even better would be to use lookup select box type variables where you can specify the lookup table and value, and add a Reference qualifier and Variable attributes like this:

[LeiM · Accepted solution]

Thanks for the answer!

I wanted to add other controls, so I used Client Script and Script Include instead of RQ and solved myself.

Created 2 Client Script.

Part of Client Script

```

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue === '') {
return;
}

var headquarter = g_form.getValue('us_headquarters');
var ga = new GlideAjax('ScriptIncludeName');
ga.addParam('sysparm_cd', headquarter);
ga.getXMLAnswer(function(response) {
var data = JSON.parse(response);
g_form.clearOptions('us_shop');
data.forEach(function(item) {
g_form.addOption('us_shop', item.displayValue, item.displayValue);
});
});
}

```

Part of Script Include

```

getFilteredOptions: function(fieldName, headquarter) {
var jsonData = [];
var gr = new GlideRecord('u_info_table');
if (headquarter) {
gr.addQuery('u_headquarter_nm', headquarter);
}

gr.query();
while (gr.next()) {
var data = {};
data.sys_id = gr.sys_id.toString();
data.displayValue = gr[fieldName].toString();
jsonData.push(data);
}
return JSON.stringify(jsonData);
},

```