How to configure the records dynamically based on the another field selected value?

[Reddy4811]

Dear All, 

We have two tables, Instance and Alias, with a many-to-many (m2m) table managing their relationship. The Alias table includes a related list of Database records.

Requirement:

We are configuring a service catalog item with two reference fields:

1. Instance Field: Points to the Instance table.
2. Database Field: Points to the Database table.

We need to show only the related Database records in the "Database" field based on the selection made in the "Instance" field. Specifically, when a user selects an Instance, only those Database records related to the selected Instance (through the Alias table) should be available for selection in the "Database" field.

Request:

How can we configure the "Database" field to dynamically filter its options based on the value selected in the "Instance" field?

Thanks & Regards,

Reddy481.

[Mark Manders]

Please don't tag people in your questions. It's very annoying! And it can have other people ignore your question because it looks like you don't want their help.

[Sandeep Rajput]

@Reddy4811 You need to create an Advanced reference qualifier on your Database reference field. This Advanced reference qualifier will call a script include method by passing the instance variable as a parameter.

Inside your script include you will query the m2m table to find out the Alias associated with the instance. From the Alias record, you can query the database table to fetch those database sys_ids which are associated with Alias. Finally you will return the comma separated list of sys_ids with 

'sys_idIN'+<comma separated list of sys_ids>

Hope this helps.

[Sandeep Rajput]

@Reddy4811 The accepted solution follows the exact same approach I have described in my response.

[Tai Vu · Accepted solution]

Hi @Reddy4811 

Let's try the approach to retrieve related Database records for your scenario.

1. Query to the m2m table to get all associated Alias to the Instance (with Alias Sys Id, separated by comma).

2. Return an encoded query with the Alias contains the Sys Id above.

Sample function for the script include

getAliasByInstance: function(instanceSysId){
	var alias = [];
	var gr = new GlideRecord('<your_m2m_table>');
	gr.addQuery('instance', instanceSysId); //replace your instance field name in the m2m table
	gr.query();
	while(gr.next()){
		alias.push(gr.getValue('alias')); //replace your alias field name in the m2m table
	}
	return alias.join(',');
},

getDatabaseQueryByInstance: function(instanceSysId){
	var alias = this.getDatabaseQueryByInstance(instanceSysId);
	return 'aliasIN' + alias; //replace the alias field name in the Database table
},

Reference Qual:

//replace your script include name and your Instance variable/field name
javascript: new InstanceUtils().getDatabaseQueryByInstance(current.variable.instance);

Cheers,

Tai Vu