ORDER BY FORMULA

Home

4D v16.3

ORDER BY FORMULA


ORDER BY FORMULA ( aTable ; formula {; > or <}{; formula2 ; > or <2 ; ... ; formulaN ; > or <N} )

Parameter	Type	Description
aTable	Table	Table for which to order selected records
formula	Expression	Expression on which to set the order for each level (can be of type Alphanumeric, Real, Integer, Long Integer, Date, Time or Boolean)
> or <	Operator	Ordering direction for each level: > to order in ascending order, or < to order in descending order

Description

ORDER BY FORMULA sorts (reorders) the records of the current selection of aTable for the current process. After the sort has been completed, the new first record of the selection becomes the current record. Note: you must specify aTable. You cannot use a default table.

You can sort the selection on one or several levels. For each sort level, you specify an expression in formula and the sorting order in > or <. If you pass the “greater than” symbol (>), the order is ascending. If you pass the “less than” symbol (<), the order is descending. If you do not specify the sorting order, ascending order is the default.

The formula parameter can be of type: Alphanumeric, Real, Integer, Long Integer, Date, Time or Boolean.

No matter how a sort has been defined, if the actual sort operation is going to take some time to be performed, 4D automatically displays a message containing a progress indicator. These messages can be turned on and off by using the MESSAGES ON and MESSAGES OFF commands. If the progress indicator is displayed, the user can click the Stop button to interrupt the sort. If the sort is completed, OK is set to 1. Otherwise, if the sort is interrupted, OK is set to 0 (zero).

4D Server: This command is executed on the server, which optimizes its execution. Note that when variables are called directly in the formula, the sort order is calculated with the value of the variable on the client machine. For example, the statement ORDER BY FORMULA([mytable];[mytable]myfield*myvariable) will be executed on the server but with the contents of the client machine's myvariable.

Compatibility note: Until 4D Server v11, this command was executed on the client machine. For reverse compatibility, this behavior is maintained in converted databases. However, a compatibility property or a selector of the SET DATABASE PARAMETER command enables server-side execution in converted databases.

Example

This example orders the records of the [People] table in descending order, based on the length of each person’s last name. The record for the person with the longest last name will be first in the current selection:

ORDER BY FORMULA([People];Length([People]Last Name);<)