Can you pass a value from another field into an encoded query?

[Sam Ogden]

Hi All,

I am in the middle of creating a script include to be used as part of a reference qualifier to filter the available values in a reference field.

As part of my script I am using the following encoded query:

operational_status!=3^name!=CST (Client Specific Testing)^RLQUERYu_m2m_companies_configuratio.u_configuration_item,>=1,m2m^u_companyDYNAMICc1c7b721db43830085ce56d6dc9619ff^ENDRLQUERY

For most of our customers this works fine as the users are part of a single company.  However some of our customers have multiple brands (companies).  These users are made part of  the parent company and the various brands are child companies related to this.

On the customer portal we ask them which brand they are reporting the issue for.

I was wondering if there is anyway to not use the companyDYNAMIC and instead use the value from the 'which brand' field within the encoded query?

Is this possible?

Thanks

sam

[Michael Ritchie]

Sam, not sure I completely follow without seeing the entire script, but if this encoded query is part of your script you can absolutely change it.  Reference qualifier scripts also have access to the "current" record so you can add in code to use the current.brand or whatever field stores the "which brand" value.

[Sam Ogden]

Hi Michael,

On the record producer on our customer portal we have 2 fields 'which brand is affected?' (u_brands) & 'which environment is affected?' (u_envi).

On the u_Envi field I have a reference qualifier as:

javascript: new My_Env().getEnv(current.variables.u_brands);

This uses the My_Env script includes:

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

getEnv : function(u_brands){
var sysid = '';
var brand = u_brands;
if(brand == ''){
return 'sys_idIN';
}
	else{
	
var env = new GlideRecord('cmdb_ci_environment');
env.addEncodedQuery(
"operational_status!=3^name!=CST (Client Specific Testing)^RLQUERYu_m2m_companies_configuratio.u_configuration_item,>=1,m2m^u_companyDYNAMICc1c7b721db43830085ce56d6dc9619ff^ENDRLQUERY");	
env.query();
while(env.next()){
   sysid += "," + env.sys_id;
}
return 'sys_idIN'+sysid.substring(1);
}

},
	
    type: 'My_Env'
});

This is currently got the encoded query:

operational_status!=3^name!=CST (Client Specific Testing)^RLQUERYu_m2m_companies_configuratio.u_configuration_item,>=1,m2m^u_companyDYNAMICc1c7b721db43830085ce56d6dc9619ff^ENDRLQUERY

As this is using companyDYNAMIC so is using the logged in users company, however we have a couple of clients who have multiple brands(companies) so the users company is a parent, but they need to select which brand on the record producer which is a list of all their brands (child companies). 

On our environment table we have around 20 different environments and on the form their is a related list of 'companies' which stores all the companies who have that environment:

Ideally what I want is instead of using the the DynamicCOMPANY in the encoded query I want to pass in the value tat the user selects in the u_brands field.

I hope this makes sense.  Let me know if you need anything else.

[Michael Ritchie · Accepted solution]

What type of variable is current.variables.u_brands?  Is it a single reference or multiple values?  If single, maybe the following script will work.  Notice I created an encodedQuery variable so its a little easier to read and I inserted the value of the brand variable into the query.  Its also good practice to use an array to push values into versus creating a string of SysIDs.  I modified that as well.

var My_Env = Class.create();
My_Env.prototype = Object.extendsObject(AbstractAjaxProcessor, {
	
	getEnv : function(u_brands){
		var brand = u_brands;
		if(brand == ''){
			return 'sys_idIN';
		} else {
			var answer = [];
			var encodedQuery = "operational_status!=3^name!=CST (Client Specific Testing)^RLQUERYu_m2m_companies_configuratio.u_configuration_item,>=1,m2m^u_company
			=" + brand + "^ENDRLQUERY";
			
			var env = new GlideRecord('cmdb_ci_environment');
			env.addEncodedQuery(encodedQuery);
			env.query();
			while(env.next()){
				answer.push(env.sys_id.toString());
			}
			return 'sys_idIN' + answer.toString();
		}
	},
	
	type: 'My_Env'
});

[Sam Ogden]

Hi Michael,

Thanks for the above.  I've just tried and this seems to be what I was after.  Just need to do a few more tests, but thanks for all your help.

I'm still learning the scripting, was just wondering if you could explain the advantage of using the array over pushing a string of SysIds?

Thanks

Sam