Changing “GOTO” Filter Default Query

There’s a new feature I’ve just learnt about in Kingston.

Using the GOTO feature for searching (image below), the out of the box query that is run is a greater or equal to search.

Screen Shot 2018-05-30 at 16.08.00

ie, if I was on the sys_user table, and for the field “name” I searched for Ahmed, it would find all names >= Ahmed. This would return “Ahmed Hmeid” but would also return “Bob Smith” and “Carla Jones” just because they’re alphabetically greater.

I’ve always had issues with this because

1) I didn’t search for Bob so don’t want Bob in my results

2) it’s not very efficient
Pre-Kingston, you could change the default query to be “greater or equal to”, to “contains”.
While this gave better results, it produced far more performance issues because “contains” queries cannot utilise the indexes at all.

Now in Kingston, you can change the default query to “starts with” (finally). This is generally what people expect when they type in the GOTO field. This approach is far more efficient for a better user experience. It also will give more accurate results based on what the user expected to see.


To do this, find the column you want to change to a “starts with” query in the dictionary then navigate to the “Attributes” related list. From here, add a new attribute:

Attribute: Default GOTO search Starts with

Value: true

As you can see, it can be set for specific fields only. So the next question, is what fields do you want to pick?

To help with this, I have written the below script. It goes through the syslog_transaction table and searches for all GOTO searches and pulls out the table and the field it was run on.
Note, it can take a LONG time to run because it has to do a full table scan to find the data (it ran for one hour on my current customer who has 93 million records in the syslog_transaction table).
At the end of the scan, it will attach a CSV file to your user profile record called GOTOAnalysis.csv which you can download and, in Excel, simply create a pivot chart to find which tables and columns are most used, and therefore, which fields would benefit the most from for adding the new “Starts With” attribute.

var u = gs.getProperty("glide.servlet.uri") + "/syslog_transaction.do?sys_id=";
var gr = new GlideRecord("syslog_transaction");
gr.addEncodedQuery("client_transaction=true^urlLIKEGOTO");
gr.query();
var csv = "Table,Field,Link\n";
while (gr.next()) {
var str = gr.url + "";
var link = u + gr.sys_id;
var regex = /(?!.*(goto_url|123TEXTQUERY321).*)\/(.*?)(_list)?.do.*GOTO([^=|LIKE|>=|STARTSWITH|ENDWITH]*)/gm;
var m;
while ((m = regex.exec(str)) !== null) {
if (m.index === regex.lastIndex) {
regex.lastIndex++;
}
csv += m[2] + "," + m[4] + "," + link + "\n";
}
}
var rec = new GlideRecord("sys_user");
rec.addQuery("sys_id", gs.getUserID());
rec.query();
rec.next();
GlideSysAttachment().write(rec, "GOTOAnalysis.csv", "text/csv", csv);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.