Defining the Sort Order for Query Results

The setSortBy() function takes a single string argument whose value can be a comma-separated list of one or more field names in the object.

To define the sort order for view object query results, call the setSortBy() method on the view object instance you're working with, before calling its executeQuery() method, to retrieve the results.

The following example shows how to use this method to sort by a single field:

def vo = newView('TroubleTicket')
// Use object function to simplify filtering by agent
applyViewCriteriaForSupportAnalyst(vo, analystId)
vo.setSortBy('Priority')
vo.executeQuery()
while (vo.hasNext()) {
  def curRow = vo.next()
  // Work with current row curRow here
}

By default, the sort order will be ascending, but you can make your intention explicit by using the asc or desc keyword after the field's name in the list, separated by a space.

The example below shows how to sort descending by the number of callbacks.

def vo = newView('TroubleTicket')
// Use object function to simplify filtering by customer
applyViewCriteriaForCustomerCode(vo, custCode)
vo.setSortBy('NumberOfCallbacks desc')
vo.executeQuery()
while (vo.hasNext()) {
  def curRow = vo.next()
  // Work with current row curRow here
}

The setSortBy() method doesn't let you specify how null values are sorted. Instead, null values are sorted according to whether the sort order is ascending or descending:

• If the sort order is ascending, then null values display last
• If the sort order is descending, then null values display first

Note:

If setSortBy() is called during the creation of a new row, then that new row isn't part of the sort and is always listed first.

As mentioned before, the string can be a comma-separated list of two or more fields as well. This example shows how to sort by multiple fields, including explicitly specifying the sort order.

def vo = newView('TroubleTicket')
// Use object function to simplify filtering by customer
applyViewCriteriaForCustomerCode(vo, custCode)
// Sort ascending by Priority, then descending by date created
vo.setSortBy('Priority asc, CreationDate desc')
vo.executeQuery()
while (vo.hasNext()) {
  def curRow = vo.next()
  // Work with current row curRow here
}

By default, when sorting on a text field, its value is sorted case-sensitively. A value like 'Blackberry' that starts with a capital 'B' would sort before a value like 'apple' with a lower-case 'a'. To indicate that you'd like a field's value to be sorted case-insensitively, surround the field name in the list by the UPPER() function as shown in the following example.

def vo = newView('TroubleTicket')
// Use object function to simplify filtering by customer
applyViewCriteriaForCustomerCode(vo, custCode)
// Sort case-insensitively by contact last name, then by priority
vo.setSortBy('UPPER(ContactLastName),Priority')
vo.executeQuery()
while (vo.hasNext()) {
  def curRow = vo.next()
  // Work with current row curRow here
}

Tip:

While it is possible to sort on a formula field or dynamic choice field by specifying its name, don't do so unless you can guarantee that only a small handful of rows will be returned by the query. Sorting on a formula field or dynamic choice list must be done in memory, therefore doing so on a large set of rows will be inefficient.