Get choice/reference field display value in GlideQuery.

[Sandeep Rajput]

Recently, I came across an interesting question where the questioner asked how the display value of state field can be fetched in the GlideQuery.  Here is how a simply GlideQuery on incident table returns the values.

var query = new global.GlideQuery('incident')
    .orderBy('number')
    .limit(1)
    .select('priority', 'state','assigned_to') //Returns a stream of records wrapped in a Stream object.  
    .toArray(100); //Terminal method in the Stream class that executes the query and returns the result.

gs.info(JSON.stringify(query, null, 2));

Here is the output.

[
  {
    "priority": 1,
    "state": 7,
    "assigned_to": "46b87022a9fe198101a78787e40d7547",
    "sys_id": "9c573169c611228700193229fff72400"
  }
]

Here, the state and assigned_to field returns the value/sys_id and not the display value. 

To get the display value, you can use $DISPLAY Keyword concatenated with you column name to get the display value of a reference/choice field. For state you should 'state$DISPLAY' in select statement.

Here is the sample query to get State and assigned to display values from incident table.

var query = new global.GlideQuery('incident')
    .orderBy('number')
    .limit(5)
    .select('priority', 'state$DISPLAY','assigned_to$DISPLAY') //Returns a stream of records wrapped in a Stream object.  
    .toArray(100); //Terminal method in the Stream class that executes the query and returns the result.

gs.info(JSON.stringify(query, null, 2));

Here I have used $DISPLAY keyword with state and assigned_to field in the select statement.

    .select('priority', 'state$DISPLAY','assigned_to$DISPLAY') //Returns a stream of records wrapped in a Stream object.  

Here is the output.

Script: [
  {
    "priority": 1,
    "state$DISPLAY": "Closed",
    "assigned_to$DISPLAY": "Charlie Whitherspoon",
    "sys_id": "9c573169c611228700193229fff72400"
  },
  {
    "priority": 1,
    "state$DISPLAY": "On Hold",
    "assigned_to$DISPLAY": "Howard Johnson",
    "sys_id": "9d385017c611228701d22104cc95c371"
  },
  {
    "priority": 1,
    "state$DISPLAY": "In Progress",
    "assigned_to$DISPLAY": "Beth Anglin",
    "sys_id": "e8caedcbc0a80164017df472f39eaed1"
  }]

Hope this helps.

Source: https://docs.servicenow.com/en-US/bundle/vancouver-api-reference/page/app-store/dev_portal/API_refer...