Display records in catalog variable based on another catalog variable

[Szilard]

Hello Community,

There are 2 catalog variables. The source variable refers to the project code table, and the target variable refers to the cmdb_model table. The goal is that when I select a project code, only those records should appear from the cmdb_model table that are relevant. The two tables are not directly related, so a simple dependency cannot be established. I have written a script include that looks like this (client callable of course):

var GetProductData = Class.create();
GetProductData.prototype = Object.extendsObject(AbstractAjaxProcessor, {
getProductData: function() {
var projectCode = this.getParameter('sysparm_project_code');
var productArray = [];
var contractGr = new GlideRecord('ast_contract');
contractGr.addQuery('u_project_code', projectCode);
contractGr.query();
while (contractGr.next()) {
var serviceEntitlementGr = new GlideRecord('service_entitlement');
serviceEntitlementGr.addQuery('contract', contractGr.sys_id);
serviceEntitlementGr.query();
while (serviceEntitlementGr.next()) {
productArray.push(serviceEntitlementGr.product.toString());
}
}
return JSON.stringify(productArray);
},
type: 'GetProductData'
});

There is also a client script that looks like this (and runs on the target variable):

function onChange(control, oldValue, newValue, isLoading) {
// Exit the function if the form is loading or the new value is empty
if (isLoading || newValue == '') {
return;
}
if (control.id == 'FIG_project_code') {
var ga = new GlideAjax('GetProductData');
ga.addParam('sysparm_project_code', g_form.getValue('FIG_project_code'));
ga.getXMLAnswer(function(answer) {
var productArray = JSON.parse(answer);
g_form.setValue('product', productArray);
        });
    }
}

What could be the error that the target variable still shows the entire table, not just the filtered elements? Is there anything else that needs to be set under the variable?

Thanks in advance!

[Harsh_Deep]

Hello @Szilard 

Use this script-

function onChange(control, oldValue, newValue, isLoading) {
// Exit the function if the form is loading or the new value is empty
if (isLoading || newValue == '') {
return;
}
if (control.id == 'FIG_project_code') {
var ga = new GlideAjax('GetProductData');
ga.addParam('sysparm_name', 'getProductData');
ga.addParam('sysparm_project_code', g_form.getValue('FIG_project_code'));
ga.getXMLAnswer(function(answer) {
var productArray = JSON.parse(answer);
g_form.setValue('product', productArray);
        });
    }
}

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.

[Mark Roethof]

Hi there,

Unfortunately I cannot reproduce this since I don't have these tables.

Though just try starting debugging. To see up to which point your script is working, from which point is it failing, is the script include reached at all, is the json built at all, etcetera. That way you will spot the issue within seconds.

For the script include I do wonder why you are looking through 2 GlideRecord queries, I think this could be done in one.

[Siddhesh Gawade · Accepted solution]

Hello @Szilard ,

This is not the proper way to show list records in reference variable depeding on values of other variable value.

You need to use the reference qualifier So that depending on that you can show list of record from cmdb_model table in your target field.

1.Reference qualifier for your target variable which should be refering to cmdb_model table. the "FIG_project_code" it should be the backend name of  source variable refering to the project code table. Confirm that.

2. Create a script include as below:

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

    getProductData: function() {
        var productArray = [];
        var contractGr = new GlideRecord('ast_contract');
        contractGr.addQuery('u_project_code', projectCode);
        contractGr.query();
        while (contractGr.next()) {
            var serviceEntitlementGr = new GlideRecord('service_entitlement');
            serviceEntitlementGr.addQuery('contract', contractGr.sys_id);
            serviceEntitlementGr.query();
            while (serviceEntitlementGr.next()) {
                productArray.push(serviceEntitlementGr.product.toString());   //Make product is field which is refering to cmdb_model table.
				gs.info("productArray is "+productArray);    //make sure productArray returning an array with sys_id of cmdb_model records.
            }
        }
        return 'sys_idIN'+productArray;       
    },

    type: 'GetProductData'
};

Also please see the demonstration video for clarification on how to use reference qualifier.

ServiceNow Advanced Reference Qualifier | How to use a Script Include in a reference qualifier 

Kindly mark the answer ✔️Correct or Helpful ✔️If it addresses your concern.

Regards,

Siddhesh