Auto Populate choices inside MRV variables

[Nitin_NOW]

Hello - We have the following scenario on a Catalog item.

1) Variable A (type: Look up Select box, 'lookup field value' set to Column A)- Referencing to a custom table. The custom table has 4 columns (Column A, Column B, Column C).

2) Multi Row Variable Set (Variable B (type: look up select box, lookup field value set to Column B) and Variable C (type: look up select box, look up field value set to Column C), both refers to the same custom table  in which was in #1).

The Use case we are trying to fill is;

If a value is selected in Variable A, then the related Column B, Column C values should be displayed in the drop downs of Variable B and Variable C inside the Multi Row Variable. 

Example scenario

Table: u_department

Column A                            Column B                                 Column C

DeptA                                   Michael                               Mike@abc.com

DeptB                                    John                                     John@abc. 

So, if the User selects "DeptA" in Variable A, then Variable B and Variable C inside Multi Row Variable, should show choice values Michael and Mike@abc.com and hide other Department choices. 

Does anyone has come across this requirement and how to approach thus? 

Thanks in advance for any inputs. 

[SwarnadeepNandy]

Hello @Nitin_NOW,

You may need to use a catalog client script that will filter the multi-row variable set based on another variable. You can use the onChange type of script to trigger the filtering when the user selects a value in Variable A.

The basic steps are:

• Create a catalog client script for your catalog item and select the onChange type.
• In the Variable name field, select Variable A. This will make the script run when Variable A changes.
• In the Script field, write the code that will filter the multi-row variable set based on Variable A. You can use the g_form.getReference() method to get the selected value of Variable A and use it as a filter for the custom table. You can also use the g_form.getGlideUIElement() method to get the multi-row variable set element and use the setFilter() method to apply the filter. You can find more details and examples of these methods in the documentation.

Here is an example of how the script might look like:

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

  // Get the selected value of Variable A
  g_form.getReference('variable_a', function(variableA) {
    // Get the multi-row variable set element
    var mrvs = g_form.getGlideUIElement('multi_row_variable_set');
    // Set the filter for Column B and Column C based on Variable A
    mrvs.setFilter('column_b=' + variableA.column_b + '^column_c=' + variableA.column_c);
  });
}

Hope this helps. 

Kind Regards,

Swarnadeep Nandy

[Nitin_NOW]

Thanks @SwarnadeepNandy 

I tried your script but isn't working as expected. I mean the Variable B and Variable C inside MRV, does not update with the choices according to the the value selected in Variable A. I just see every choice value inside the MRV variables. 

[Nitin_NOW]

I did some research and tried the solution by writing a Script Include and onChange() Client Script, however I don't see it's working. 

Script Include:

var dsa_multirowvariable = Class.create();
dsa_multirowvariable.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    listcollector: function() {


        var listValuename = [];
        var dpt = this.getParameter('sysparm_data_department'); //Passing the Department variable value.
        var query = 'sys_idIN' + dpt; // Set a query to run only for Departments selected in the LookUp select box.
        if (dpt) {
            var gr = new GlideRecord('u_dsa_meta_data'); //Glide record on DSA Meta Data Table.
            gr.addEncodedQuery(query);
            gr.query();
            while (gr.next()) {
                //Push selected Department related values into multi row variable set variables.
                listValuename.push({
                    "data_set": gr.getValue('u_data_domain'),
                    "data_set_column": gr.getValue('u_data_set_1')

                });
            }
        }
        gs.info('DSA list JSON'+JSON.stringify(listValuename));
        return JSON.stringify(listValuename); //Storing display name & values and stringfy it to .

    },

type: 'dsa_multirowvariable'

I see nothing gets returned in the log, but empty JSON

fyi - I have also tried g_form.getDisplayValue() in place of g_form.getValue(), but no luck.

Client Script: This is a onChange() client script on the Variable present of Catalog form and NOT on MRV.

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }
    if (newValue == '') {
        g_form.clearValue('data_description'); // give name of MRVS variable set here
    }

    if (oldValue != newValue) {
	
        var ga = new GlideAjax('dsa_multirowvariable');
        ga.addParam('sysparm_name', 'listcollector');
        ga.addParam('sysparm_data_department', g_form.getValue('data_department')); // give here the requestor variable name
			alert("Selected Department-->" + g_form.getValue('data_department'));
        ga.getXML(listcolleValues);
    }
    function listcolleValues(response) {
        var val = response.responseXML.documentElement.getAttribute("answer");
        g_form.setValue('data_description', val); // give name of MRVS variable set here
		alert('Setting MRV Values to-->' + g_form.getValue('data_description'));
    }
}

While testing, I found it set empty JSON and the choice values are not sorted according to the variable choice selected on the Catalog Item.

Is anything wrong with the code?

Thanks,