How can I do a contains search on 'name' as well as other fields for a cmdb_ci (Configuration) Reference variable?

[Shane J]

I can't do anything but a startswith search for the Display Value on cmdb_ci for a Reference variable.  The other columns also do a startswith search only.

Here's what I'm currently using for Attributes:

ref_auto_completer=AJAXTableCompleter,ref_ac_columns=name;asset_tag;category;model_number;serial_number,ref_ac_order_by=name,ref_ac_columns_search=true

I have a contains search working properly for sys_user Reference Variables but I believe that's due to having the appropriate System Poperty sys_user.autocomplete.contains .  Creating the same Property for cmdb_ci didn't have any effect.

[dianemiro]

Set the system property: glide.ui.ref_ac.startswith to false this will give you contains autocomplete. For more info, please see here.

[Shane J]

We've had that set for awhile now and it hasn't helped for this table.  

[Satwik]

In my case, I am working with a custom table and I achieved "contains" search on my display value of the table by following below steps.

1. Go to User Preferences 
2. Create a new with name "<table name>.autocomplete.contains"
3. select the "System" checkbox
4. Change the "type" to "true|false"
5. and Value to "true"

[codycotulla]

Hi Shane, it's been a year, so you've probably got this working the way that you need it to, but I found a way to do a CONTAINS search for a specific reference variable, so maybe it will help you or someone else.

You'll need a reference qualifier for the variable and a query business rule. We are going to put a "key" string in your reference qualifier and then look for that "key" string in your business rule. If the "key" string exists, the business rule will make the STARTSWITH queries CONTAINS queries.

Reference Qualifier

Add the following "key" string to your reference qualifier: sys_id!=doContainsSearch. I use this string because it won't impact the search results; no record's sys_id will ever be "doContainsSearch".

Business Rule

On the table your variable references (in this case cmdb_ci), create a Query business rule that has the following script:

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

	/*
		Checks if the current enncodedQuery contains sys_id!=doContainsSearch.
		If it does, it adds a query that changes the STARTSWITH query and makes it a CONTAINS query
		
	*/
	var encodedQuery = current.getEncodedQuery();
	if(encodedQuery.match(/sys_id!=doContainsSearch/) != null){
		encodedQuery = encodedQuery.replace(/STARTSWITH/g,"CONTAINS");
		encodedQuery = "^NQ" + encodedQuery;
		current.addEncodedQuery(encodedQuery);
	}
	//encodedQuery = current.getEncodedQuery();
	//gs.info("Monkey " + encodedQuery);
	
})(current, previous);

Now if the encodedQuery contains the key string sys_id!=doContainsSearch the encodedQuery will be duplicated and added as a new query effectively converting the STARTSWITH query to a CONTAINS query.

Uncomment the lines at the end of the script to see the resulting encodedQuery.

Examples Query

Here is an example using the Variable attributes in the image above (ref_ac_columns =asset_tag;serial_number;sys_class_name). I search for "238" and the following query string is created and sent to the business rule.

sys_id!=doContainsSearch^nameSTARTSWITH238^ORasset_tagSTARTSWITH238^ORserial_numberSTARTSWITH238^ORsys_class_nameSTARTSWITH238

After the business rule runs, the query string is (the part added is in bold):

sys_id!=doContainsSearch^nameSTARTSWITH238^ORasset_tagSTARTSWITH238^ORserial_numberSTARTSWITH238^ORsys_class_nameSTARTSWITH238^NQsys_id!=doContainsSearch^nameCONTAINS238^ORasset_tagCONTAINS238^ORserial_numberCONTAINS238^ORsys_class_nameCONTAINS238^EQ^ORDERBYname^ORDERBYasset_tag^ORDERBYserial_number^ORDERBYsys_class_name

Hope this helps.

Thanks,

Cody