How to order a query with a Document ID field

[joost2633]

See this snippet:

var elemsGR = new GlideRecord("cert_element");
elemsGR.addQuery("cert_task", task.sys_id);
elemsGR.query();
while(elemsGR.next()) {
       // field 'id' is of type Document ID
       var doc = elemsGR.id.getRefRecord();
       // in this case it is known beforehand that the referenced record
       // has a field 'user', that is a reference to sys_user table
       var userName = doc.user.getRefRecord().getValue('name');
}

It is selecting all certification elements that belong to a certain task (task.sys_id). The 'cert_task' table has a field called 'id' that is of type Document ID, and references another table. I retrieve that record in line 6. That record has a regular reference field to the 'sys_user' table, called 'user'. I'm getting the name of referenced user in line 11.

Considering all this: how do I construct the same query, but ordered by the referenced user's name?

[mig]

Hi Joost,

not sure if tat might help.

I remember that I was using in the past something like:

gs.sql("select * from incident;")

for a background script. could be that it would work gs.sql("select * from cert_task;")

The where-clause was also within the string.

see also https://community.servicenow.com/community/develop/blog/2016/10/07/gql-glide-query-language-part-1-t...

That is what I found around this topic.

regads

Miguel

[joost2633]

Thanks for that, Miguel. Though it seems GlideSystem SQL has been sunset: End of GlideSystem SQL — ServiceNow Elite

[mig]

Thanks for the head up.

The last time I have used the gs.sql() was under Eureka ... 😄

[Deepak Ingale1]

addJoinQuery Conditions

There is addJoinQuery which does internal join operation.

If you have user_name as display value, then one optimization i can think of is to use

elemGr.id.getDisplayValue("id") // get ID field display value in format of TABLE NAME : DISPLAY VALUE

You can store these values inside an array by doing string manipulation and sort array. You will get all user_name in ascending order.

gs.sql calls are blocked nowaday from execution