@Sort (Formula Language)

Sorts a list.

Note: This @function is new with Release 6.

Syntax

@Sort( list ; [ order ]; customSortExpression )

Parameters

list

Text, number, or time-date list. The values to be sorted. Any alternate data types are returned unchanged.

[ order ]

Keyword. Optional. You can use the following keywords to specify the order of the sort:

[ACCENTSENSITIVE]

[ACCENTINSENSITIVE]

[ASCENDING]

[CASESENSITIVE]

[CASEINSENSITIVE]

[CUSTOMSORT]

[DESCENDING]

[PITCHSENSITIVE]

[PITCHINSENSITIVE]

Separate multiple order keywords with a colon(:). By default, the following keywords automatically format the sort order: 

[ASCENDING]:[CASESENSITIVE]:[ACCENTSENSITIVE]:[PITCHSENSITIVE].

You can override a default sort order keyword by specifying its opposite keyword. For example, to override [ASCENDING], specify [DESCENDING] in the @Sort function. If conflicting keywords are passed, the last one in the list affects the sort order.

customSortExpression

Formula. Required when the [CUSTOMSORT] keyword is specified. A formula that uses the temporary variables $A and $B to compare the values of elements in the list two at a time. Return @True or a number greater than 0 to specify that $A is greater than $B. Return @False or a number less than or equal to 0 to specify that $B is greater than $A.

An error is produced if the return value is a data type other than a number.

Return value

list

Text, number, or time-date list. The sorted values.

Usage

The ascending, case-, and accent-sensitive sort sequence for the English character set is as follows: the numbers 0-9, the alphabetic characters aA-zZ, the apostrophe, the dash, and the remaining special characters. Pitch-sensitivity affects double-byte languages.

Note: The case sensitive sort only matters when terms are identical except for case. In that instance, the lower case is sorted first. For example, cat, Cat, CAT. If terms are not identical except for case, they are sorted without regard to case.

If you set Unicode standard sorting as the sorting option, you cannot select the following keywords or combinations:

  • [PITCHINSENSITIVE]
  • [CASESENSITIVE]:[ACCENTINSENSITIVE]

You specify Unicode standard sorting by setting the notes.ini variable $CollationType to @UCA, or by selecting the "Unicode standard sorting" checkbox that displays in the following dialog boxes:

  • Sorting dialog box that displays when you choose File - Preferences - User Preferences - International - Sorting from the main menu
  • Database Properties box*
  • Design Document Properties box*

*The Unicode option is disabled in the Database and Design Document Properties boxes until you select a default sort order.

For more information on Unicode sorting, see http://oss.software.ibm.com/icu/

A date-time value with a wildcard time (no time specified) equals all date-time values for the same date. For example, the following dates are considered equal:

[12/12/2000] : [12/12/2000 1:00 PM] : [12/11/2000 - 12/13/2000]

These values are sorted in random order and may be ordered differently with each sort if multiple sorts are performed on them.

Examples

  1. This formula returns: Albany, New Boston, new york, San Francisco.
    @Sort(@ThisValue)
  2. Same as preceding.
    @Sort(@ThisValue; [ASCENDING])
  3. This formula returns: San Francisco, New Boston, new york, and Albany.
    @Sort(@ThisValue; [DESCENDING])
  4. This formula returns: Albany, New Boston, New York, San Francisco.
    @Sort(@ProperCase(@ThisValue); [ASCENDING])

These examples are used as the default values for form fields.

  1. This formula returns 1009;85;79 if the Price column (the 5th column in the Gear view) contains the prices 79, 85, and 1009 for three entries in the Ski Pants category:
    @Sort(@DbLookup("";"Server/Name/Notes":"Ski\\Clothing.nsf";"Gear";"Ski Pants";5);[DESCENDING])
  2. This formula returns the contents of the movies field in order from the shortest title to the longest; it returns ET;casablanca;The Great Escape when the movies field contains "casablanca":"The Great Escape":"ET":
    @Sort(movies;[CASESENSITIVE]:[CUSTOMSORT];@If(@Length($A) < @Length($B);@False;@Length($A) > @Length($B);@True;@False))

    Note that the custom sort keyword overrides the case-sensitivity keyword.

  3. This formula returns the following passwords in order from the strongest to the weakest: HE5ll+o;Hel$lo;hello, when the pswd1 field contains "Hello", pswd2 field contains "HE5ll+o", and the pswd3 field contains "Hel$lo."
    @Sort(pswd1:pswd2:pswd3;[CUSTOMSORT];@If(@PasswordQuality($A) < @PasswordQuality($B);@True;@PasswordQuality($A) > @PasswordQuality($B);@False;@False))
  4. This formula returns: cat, Cat, CAT, dog when the animals field contains "CAT, Cat, dog, cat".
    @Sort(animals;[CASESENSITIVE])