Sorting Collections

When you retrieve a collection of records, you often sort them according to the contents of one field. The system lets you sort based on:

• The contents of a column in a grid, or
• The value of information displayed in a field

To sort a collection based on the contents of a grid column, double-click the column heading for the column. To toggle the sort between ascending order and descending order, repeat this action.

To sort a collection based on a field value:

1. In an open a form, display the collection you want to sort.
2. Click in the field on which you want to sort.
3. Select Edit > Sort Collection.
4. (Optional) By default, records are sorted on the field in the primary collection that currently has focus. If you are not sorting the primary collection, select a collection and field:
   • From the Collection list, select the name of the collection you want to sort. If you don't know the name of the collection, to see the name of the current collection, return to the form and click Help > About This Form.
   • In the By Property box, click the name of the field on which you want to sort.
5. (Optional) By default, records are sorted in ascending order, regardless of capitalization. To sort by descending order, select the Descending check box.
6. (Optional) To take into account capitalization when sorting, select the Case-sensitive check box.
7. Click OK.

Notes About Sort Order

When sorting collections, a few sort-order concepts must be kept in mind:

• Some fields that appear to be numeric (for example, Account) might actually be character-based. Thus, they would use text-character sort order.
• Character-based fields use text-character sorting. For example, if you attempt an ascending sort by account number, the accounts might be sorted like this:

(blank field)

#222

222

2220

@201

XL-64

xl-64

This is because, in text-character (ascending) order:

• Character strings are generally sorted from left to right. That is, the first character (furthest left) is evaluated, then the second, and so on.
• A blank field comes before any other character. Thus, any blank fields always display first.
• Special characters might come before numerals, between numerals and letters, or after letters. Thus, #222 comes before 222, but @201 comes after 222.
• Numerals display in correct numeric order, regardless of size.
• Numerals display before alphabetic characters.
• Upper-case characters display before lower-case characters. Thus, XL-64 comes before xl-64.

• Unicode strings typically are compared using a "word sort" technique, where all punctuation marks and other non-alphanumeric characters, except for the hyphen and the apostrophe, come before any alphanumeric character. The hyphen and the apostrophe are treated differently than the other non-alphanumeric symbols, in order to ensure that words such as "coop" and "co-op" stay together within a sorted list.

Related Topics

Working with Collections

Finding Records Using a Query Form

Finding Records with Filter-in-Place

Finding a Value in a Collection of Records