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?

[Deepak Ingale1]

Hi,

Document ID field is dependent on table type field

1. var elemsGR = new GlideRecord("cert_element");  
2. elemsGR.addQuery("cert_task", task.sys_id);  
3. elemsGR.query();  
4. while(elemsGR.next()) {  
5.       // field 'id' is of type Document ID  
6.       var doc = elemsGR.getValue("id");
7.       var tableName = elemsGR.getValue("tableTypeFieldOnThisCertElementTable");

                                                          var t = new GlideRecord(tableName);

                            t.get(doc);

                            if(t){

                                      // write logic here

                            }

                  }

This is sample code, where GlideRecord is put inside While.

Best practice is to get all sys_ids (in this case "doc" variable ) in an array and then iterate outside while loop.

For sake of understanding, you can use above code.

[joost2633]

Thanks for the reply! But I'm not sure how it relates to the question?

[Deepak Ingale1]

As said, you need to grab all sys_ids in Array.

One done, do var usr = GlideRecord("sys_user");

usr.orderBy("user_name"); // This will sort users by their ascending user_name

usr.addQuery("sys_id", "IN" , ArrayWhatYouGot);

usr.query();

[joost2633]

Ok I get what you mean. Yes I can do some iterations and additional queries in code. But I'm looking for a more efficient solution: have the initial query (line 1-3) return the sorted result that I need. In regular SQL I'd just join a few tables based on the Document ID and User references. But not sure how to do that in this case in ServiceNow. If it's even possible, but I suppose so, in some way or another...