How can I set a catalog reference variable up to use a CONTAINS search rather than a BEGINS search?

[Nathan Underwoo]

I have a catalog reference variable that I'm using as a form field for users to select an application from. The table itself is fairly small and I don't expect it to be bigger than a few hundred records in the long term. I would like users to have their search terms use a CONTAINS search rather than a BEGINS search by default. I've tried using the variable attributes beginning with ref_* but I was unable to come up with a working combination that does what I want. So far the only thing I seem to be able to do to make it work is to place an asterisk in front of my search term as I am using it. This isn't a great solution because this is a form that every user will need to use and not everyone is going to know that (or understand it even if we add some kind of annotation to indicate it can be done). It would be much simpler if I could just make this one field act as a CONTAINS search. I do know that there is a global property that controls this but I do not want to make a global change.

[codycotulla]

Hi Mayur,

I have a way to do this. It involves using the reference qualifier and a Query business rule.

In the reference qualifier for the variable add a "unique" string to your query that won't impact the query. This unique string will be used to tell the Query business rule to modify the query. For example, I used the ^sys_id!=poppy.

Then create a Query business rule on the table your variable references with the following content. 

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

	// Add your code here
	var encodedQuery = current.getEncodedQuery();
	if(encodedQuery.match(/sys_id!=poppy/) != null){
		encodedQuery = encodedQuery.replace(/STARTSWITH/g,"CONTAINS");
		encodedQuery = "^NQ" + encodedQuery;
		current.addEncodedQuery(encodedQuery);
	}
	
	
})(current, previous);

What this business rule does is take the existing encoded query and add a new query that replaces the STARTSWITH with CONTAINS.

For example, this query

nameSTARTSWITHpack^u_is_inactive=false^u_hide_in_catalog=false^topic_detail.topic_category!=2dbb429f870af8907cf464283cbb359f^ORtopic_detail.topic_category=NULL^topic_detail.topic_category!=06bc29ba53422200d901a7e6a11c0868^ORtopic_detail.topic_category=NULL^sys_id!=poppy

 becomes

nameSTARTSWITHpack^u_is_inactive=false^u_hide_in_catalog=false^topic_detail.topic_category!=2dbb429f870af8907cf464283cbb359f^ORtopic_detail.topic_category=NULL^topic_detail.topic_category!=06bc29ba53422200d901a7e6a11c0868^ORtopic_detail.topic_category=NULL^sys_id!=poppy^NQnameCONTAINSpack^u_is_inactive=false^u_hide_in_catalog=false^topic_detail.topic_category!=2dbb429f870af8907cf464283cbb359f^ORtopic_detail.topic_category=NULL^topic_detail.topic_category!=06bc29ba53422200d901a7e6a11c0868^ORtopic_detail.topic_category=NULL^sys_id!=poppy

It's not super elegant, but it does the job.

Good luck.

Let me know if you have any questions.

Thanks,

Cody

[alejandrova]

Many years later, this still works as advertised.  Thanks Cody 

[steadyeddy]

Thank you so much. Seems to be the best solution.