How Do I Retrieve Records from a Database Table in a Predefined Order? (Magic xpa 4.x)

When you are using a database table in Magic xpa, you can specify the order in which you want those records to appear. There are two aspects to this:

• Specify the Index to use: When you select a data source, you will also select which index to use.

• Specify Ascending or Descending within that Index: For each 2-way index, you can specify the direction to search.

• Sort the records without defining an index: You can also specify that the records by sorted at runtime. This option is a bit slower than the predefined indexes, but not significantly unless the table is very large.

These are described in detail below.

Specifying the Index

When you select a data source in a program, you will have an Index column. Zoom from this column to select the index to use. For instance, in this example, we selected Index 2, which means the records will appear by employee Last Name.

This applies both to the Main Source and the linked sources. if you do not specify an index, the records will be fetched sequentially, or in whatever order the DBMS decides is most efficient.

Specifying direction for a Main Source

You can specify the direction for a Main source in Task->Range/Locate->Expressions (Ctrl+R), in the Range order and Locate order fields. The Range Order specifies the direction the records will be sorted for the display. The Locate Order is used when the user does a Locate or you are using the Locate column to position on a record.

Specifying direction for a linked source

When you are linking to a source, the direction isn’t usually as important because you can only position on one record. However, it does become important when you are trying to fetch the very first or very last record in the table. If you have selected an index with, say, the postal code in ascending order, then using a direction of Default will give you the smallest postal code in the table, while a direction of Reversed will give you the biggest postal code in the table.

Sort the records without defining an index

You can sort the records even without defining an index as follows:

1. Select Task->Sort (Ctrl+T).
   
   

2. For each item you want to participate in the Sort:

   • Press F4 to open up a line.

   • Zoom to select the variable you want to participate in the Sort.

   • Set the Direction for that variable (Ascending or Descending).

3. When you are finished, click OK.