How to create a dependent Reference Qual in Multi Row Variable Set?

[aryanjain27]

Hii,

I have a Multi-Row Variable Set (MRVS) in my ServiceNow catalog item that includes a reference variable called u_account. This variable references the accounts table, which has columns name and dept. Users use this variable to select account names.

Objective: I am trying to implement functionality where, if a user selects an account and adds a row to the MRVS, then for any additional rows, only those account names should be populated that belong to the same department as the initially selected account. This ensures that all rows a user selects and submits are from the same department, and no two rows should have different departments.

For this, I created an "onChange" client script for the u_account variable within the MRVS to update the reference qualifier to dept=<dept_from_selected_account>

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }
    
    var ga = new GlideAjax('AccountUtils');
    ga.addParam('sysparm_name', 'getDeptName');
    ga.addParam('sysparm_accountSysId', newValue);
    ga.getXMLAnswer(function(deptName) {
        filterAccountsByDept(deptName);
    });
}

function filterAccountsByDept(deptName) {
    var existingQualifier = 'disable=false'; 
    
    // Combine the existing qualifier with the new dept condition
    var newQualifier = existingQualifier + '^dept_name=' + deptName;
    
    g_form.getField('multi_row_variable_set').getRowCount().forEach(function (row) {
        var rowSysId = row.sysId;
        
        // Apply the combined reference qualifier
        g_form.setReferenceQualifier('u_account', newQualifier, rowSysId);
    });
}
}

I  have a GlideAjax call to get the department associated with the selected account. There is also an existing reference qualifier on the u_account variable to filter out disabled accounts.

Issue: When I am selecting an account in the MRVS, I am receiving the error: "There is a JavaScript error in your browser console."

Questions:

1. Is this the right approach to implement this functionality?
2. Are there alternative methods or best practices that could resolve the error?
3. How can I ensure that both the current filtering (for disabled accounts) and the new department-based filtering work together without conflicts?

Any insights or suggestions would be greatly appreciated!

Thank you in advance for your help!

[Shivalika]

Hello @aryanjain27 

To make the other fields depend dynamically on variables you need to use "variable attribute" - "ref_qual_elements". The value of this variable attribute is to be =variable names that you want it to dynamically depend on. 

Kindly mark my answer as helpful and accept solution if it helped you in anyway,

Regards, 

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwNeEISQCY

[Brad Bowman · Accepted solution]

Here's an approach I've used, adapted for your example.

The Reference qualifier will look like this to be able to select any active account in the first row, and only active accounts with the same department as the first row in subsequent rows:

Next, create an onLoad Catalog Client Script that applies to the MRVS, not the Catalog Item.  This will pass the first selected account (if there is one) to a Script Include each time a MRVS row is added or edited:

function onLoad() {
    var mrvs = g_service_catalog.parent.getValue('multi_row_variable_set'); //MRVS internal name
	var acct = '';
	if (mrvs.length > 2) { //MRVS is not empty
		var obj = JSON.parse(mrvs);
       	acct = obj[0].account_mrvs;
	}
	var ga = new GlideAjax('AccountUtils');
    ga.addParam('sysparm_name', 'setSessionData');
    ga.addParam('sysparm_account', acct);
    ga.getXMLAnswer(getResponse);
}

function getResponse(response) { 
    //do nothing 
}

Finally, the Client callable Script Include will look like this (using the out of box customer_account table).  This will populate the department name from the account in the session data for the reference qualifier to use:

var AccountUtils = Class.create();
AccountUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {
   
    setSessionData: function() {
        var acct = this.getParameter('sysparm_account');
		var dept = '';
		var acctGR = new GlideRecord('customer_account'); //reference table for Account variable
		if (acctGR.get(acct)) {
			dept = '^dept_name=' + acctGR.dept_name; //department field name on account table
		}
		
		var session = gs.getSession().putClientData('selected_dept', dept);
        return;
    },

    type: 'AccountUtils'
});

[aryanjain27]

Thnx a lot! This works! 

[Brad Bowman]

You are welcome!