Order by editor

In the Design environment, choose Order by... from the Records menu or from the menu associated with the “Query” button in the 4D tool bar.

The Order By editor contains the following areas and commands:

• Fields list: This area displays a hierarchical list of fields in the current table. Indexed fields are shown in boldface. You can sort on fields from subtables and related tables (provided the relation is automatic). To use a field from a related table or subtable, expand the foreign key field by clicking on the plus sign (on Windows) or arrow (on Macintosh) to display the fields from the related table.
  Note: Tables and fields with the Invisible property do not appear in the list. For more information about this property, refer to the “Attributes” sections in Table properties and UNREGISTER CLIENT.
• Sort area: This area displays the sort fields or sort formulas and the direction of each sort. The arrows on the right of this area are used to specify an ascending or descending sort.
• Add Formula... button: You use this button to write a formula as one of the sort criteria. You use a formula when you want to sort something that is not a field — such as a calculated value or a portion of a field. For instance, you might sort a calculated failure rate for parts or the last four digits of a Part Number field.
• Modify... button: When you click this button, it displays the selected sort criterion in the Formula editor. If the selected criterion is a formula, the formula is presented for editing. If the criterion is a field, the field name appears in the editing window of the Formula editor
• Button panel: These buttons are for adding or removing fields from the Sort list. You can also add fields by dragging and dropping them.
• Cancel button: You use this button to cancel the sort and return to the form you were using.
• Order By button: You use this button to perform the sort. When you click on this button, 4D performs the sort and displays the list of sorted records. If you launch the sort from an input form, the first record of the current sorted selection is displayed in this form.

Note: The Quick reports editor can sort records that appear in a quick report.

You can sort records in up to 30 different fields or formulas. Each field or formula you sort is referred to as a sort level. For example, the results of a two-level ascending sort of the last name and first name fields would produce a list such as this: 

Aardvark, Anthony
Aardvark, Artemis
Aardvark, Arthur
    [...]
Zygote, Elena
Zymosian, Elmer

When sorting the contents of fields, 4D is not case sensitive (i.e., “Smith” = “smith”) or diacritical sensitive (i.e., “Aá” = “Aa”). However, if you are using an international version of 4D and your operating system is diacritical sensitive (e.g., “Aá” ¦ “Aa”), sorting operations will also take diacritical marks into account

You can decide to modify the order in which the sort levels have been specified or to remove one or more level(s) at any time.

To reorder sort levels, use drag and drop:

1. In the sort area, click on the level to be moved then drag the level through the list and drop it where you want to place it.
   The level will be inserted just above the level where it is dropped.

To remove a sort level:

1. In the sort area, highlight the level you want to remove.
2. Press Backspace.
   OR
   Click on the deletion button .
   The sort level is removed from the area.

To remove all the sort levels:

1. Click on the global deletion button .
   All the sort levels are removed from the area.

You can specify either an ascending or descending order for each field or formula that you are sorting. Sorting from A to Z or smallest to largest is known as an ascending sort . Sorting in the reverse order is called a descending sort — largest to smallest, latest to earliest, and Z to A .

If you are sorting more than one level, you can freely mix ascending and descending sort orders. A multiple-level sort can mix fields and formulas, and ascending and descending sort orders. 

To modify the sort order of a level:

1. Click on the arrow to the right of the level in the sort area.

You can sort a field or sort based on a formula. For example, the following formula sorts the month of a birth date field. 

To create a sort formula: 

1. In the Order By editor, click the Add Formula button.
   4D displays the Formula editor.
   You use the Formula editor to create a formula that returns the values to be sorted. The formula can return values of any data type. For more information about how to use the Formula editor, see Formula editor.
2. Click OK when you are finished writing the formula.
   4D displays the formula in the Sort area. The formula appears with a sort direction arrow in the Sort area.

To modify a formula or create a formula using a field already placed in the sort area, you can select a formula or a field name in the sort area and click on Modify... or double-click on a formula or a field name in the sort area.