Partial matching of reference type field values with addQuery()

[N_Y_1014]

Thank you for your help.

I am trying to accomplish a requirement to display a record in a custom table when the department name in the record is a partial match to the logged in user.

An example would be as follows
If the department name in the record and the department name of the logged in user contain "ABC", then the record should be displayed to the logged in user.
Example:
Department name in which the record is registered: ABC DDD
Department name of the logged-in user: ABC EEE

I am also trying to employ a pre query business rule as a function to use.

I have taken the following approach, but am stumped by the means to partially match the department name in the custom table with the department name of the logged in user.

Best regards.

(function executeRule(current, previous /*null when async*/) {

	// Add your code here
	//Get department name of logged-in user
	var depSysid = gs.getUser().getDepartmentID();
	
	//Query with the department's sys_id in the cmn_department table matching the department's sys_id of the logged-in user
	var dep = new GlideRecord('cmn_department');
	dep.addQuery('sys_id', depSysid);
	dep.query();
	
	var depName;
	
	//Stores department records matching the depName
	if(dep.next()){
		
		//Department name "ABC EEE" is stored
		depName = dep.name;
	}
	
	//Cut out the division name "ABC"
	var dep_cut = depName.substr(0, depName.indexOf('C') + 1);
	
	//I want to run a query with a partial match between the custom_department_name field and the depName value in a custom table
	var ctm = new GlideRecord('custom_table');
	ctm.addQuery('custom_department_name', //Processing here is unclear);
	ctm.query();
	
})(current, previous);

[Sandeep Rajput · Accepted solution]

@N_Y_1014 Please update your BR script as follows and let me know if it works for you.

(function executeRule(current, previous /*null when async*/) {

	// Add your code here
	//Get department name of logged-in user
	var depSysid = gs.getUser().getDepartmentID();
	
	//Query with the department's sys_id in the cmn_department table matching the department's sys_id of the logged-in user
	var dep = new GlideRecord('cmn_department');
	dep.addQuery('sys_id', depSysid);
	dep.query();
	
	var depName;
	
	//Stores department records matching the depName
	if(dep.next()){
		
		//Department name "ABC EEE" is stored
		depName = dep.name;
	}
	
	//Cut out the division name "ABC"
	var dep_cut = depName.split(" ")[0].trim(); //splits the department name with a space and pics the first part ABC
	
	//I want to run a query with a partial match between the custom_department_name field and the depName value in a custom table
	var ctm = new GlideRecord('custom_table');
	ctm.addQuery('custom_department_name', 'STARTSWITH',dep_cut);
	ctm.query();
	if(ctm.next()){
       gs.info(ctm.getValue('sys_id'));
       }
})(current, previous);

Hope this helps.

[N_Y_1014]

@Sandeep Rajput 

Thank you for your response.

I tried the script you gave me, but the value of gs.info returns null.
By the way, the department name split in dep_cut is successful.

*** Script: null

[Sandeep Rajput]

This is bit strange as ideally the sys_id should have been printed in the logs. Would it be possible for you to share the snapshot of the record in table custom_table where custom_deparment_name is ABC? 

[N_Y_1014]

@Sandeep Rajput 

I think it is because the custom_deparment_name field created in custom_table has the type as reference, so the string in dep_cut is matched against the sys_id in the custom_deparment_name field.

I was able to solve this problem by using a dot walk through trial and error.

Specifically, I was able to confirm that changing custom_department_name in addQuery to custom_department_name.name would bring the sys_id of the department in a partial match.

(function executeRule(current, previous /*null when async*/) {

	// Add your code here
	//Get department name of logged-in user
	var depSysid = gs.getUser().getDepartmentID();
	
	//Query with the department's sys_id in the cmn_department table matching the department's sys_id of the logged-in user
	var dep = new GlideRecord('cmn_department');
	dep.addQuery('sys_id', depSysid);
	dep.query();
	
	var depName;
	
	//Stores department records matching the depName
	if(dep.next()){
		
		//Department name "ABC EEE" is stored
		depName = dep.name;
	}
	
	//Cut out the division name "ABC"
	var dep_cut = depName.split(" ")[0].trim(); //splits the department name with a space and pics the first part ABC
	
	//I want to run a query with a partial match between the custom_department_name field and the depName value in a custom table
	var ctm = new GlideRecord('custom_table');
	ctm.addQuery('custom_department_name.name', 'STARTSWITH',dep_cut);
	ctm.query();
	while(ctm.next()){
       gs.info(ctm.getValue('sys_id'));
       }
})(current, previous);