Powerful Scripted Text Search in ServiceNow

If you've ever searched from the list view of a given table in ServiceNow (AKA the "NOW Platform"), you may have seen the "for text" option next to the search box in the list header:

This search box can be configured to directly search in any field which is displayed in the list view, but you can accomplish that just by clicking the "magnifying glass" icon to the left of the column headers. 

You might notice though, that this text search isn't.... very... good. For example, if you search for multiple words like "blackberry configuration", the search assumes that you want all of the words in the search to be included in any of the results returned, so results like "blackberry setup" would not be shown. 

Let's explore why, and see if there might be a better way to do this kind of text search in our scripts. 


When you run a "for text" search in the manner described above, we use a query type called 123TEXTQUERY321. There is another query type which behaves identically to 123TEXTQUERY321, called IR_AND_QUERY. The name of this type of query gives a hint as to its functionality: It does an "AND" query using each of the words you enter as your query string. 

To help illustrate how this works, imagine that we have the following set of data:

  1. I like Apples
  2. I like Oranges
  3. I like Apples and Oranges
  4. I like Bananas

A "IR_AND_QUERY" query for the word "apples", will return the first and third items on that list. Searching for the word "oranges" will return the second and third.
Given this, you might suspect that performing a search with the two-word query: "apples oranges" will return the first three items on that list, since each of those items contains at least one of the words in our query. However, because IR_AND_QUERY takes an "all or nothing" approach to the query terms you've entered, it only returns results which contain all of the terms. In the case above, it would only return the third item in the list as a result, because the first and second items do not contain both terms that we searched for: Apples, and Oranges

There's got to be a better way! 


In fact, there is a better way. Two, actually. 
First, let's explore the closely related "IR_OR_QUERY". Imagine the same scenario as above, where we query using the terms "apples" and "oranges" against the list of four values above. Using an "OR" query (IR_OR_QUERY) will return the top three results, since each of them contains either "apples" or "oranges" (or both). 

To demonstrate this, I've created several Incidents with short_description values that match the four values above: "I like Apples", "I like Oranges", "I like Apples and Oranges", and "I like Bananas". I then ran the following background script: 

var results = '\n\n';
var gr = new GlideRecord('incident');
gr.addQuery('IR_OR_QUERY', 'apples oranges');
while (gr.next()) {
    results += gr.getValue('short_description') + '\n';

That code results in the following output: 

This is exactly what we're looking for, but there's one issue: Imagine you search for "I like apples". You might expect that a field with an exact match for "I like apples" should result in that record being shown at the top of the search results. However, this is not the case if you have another record returned with multiple instances of one of your search terms. For example, a record that has the word "apples" in it over and over.  

Pro Tip: You can determine the "query score" of a given result, by using gr.getValue('ir_query_score');. This will return a number for each query term entered, equal to +10 for each instance of that term in the result located. For example, three instances of the first query term and four of the second, would result in the query score: 3040.
You can make matches found in a specific field add more or less than 10 by adding a ts_weight attribute to the field's dictionary record.


With IR_AND_OR_QUERY, we can perform a text search that will actually return exact string matches and matches which contain all of the query terms (just like an IR_AND_QUERY would), or - if no records are found, then it falls back to providing the results of an IR_OR_QUERY

You may customize the minimum number of records to be returned from the "AND" query before falling back to the "OR" query, by using the system property "glide.ts.query.and_or_limit". You may even set this on a table-by-table basic, using the table dictionary attribute "text_search_and_or_limit". 

Because of its customizability and ability to return slightly more intuitive results from text queries, IR_AND_OR_QUERY is often the best way to perform a text search from within a GlideRecord query, or when building custom search functionality for your application.