How Can I Dynamically Set the Order of the Retrieved Records in a Task? (Magic xpa 3.x)
When a Main Source is specified, the task, by default, will retrieve every record in the database. The order in which they are retrieved is set by the programmer. There are three ways to do this:
-
A hard-coded Index
-
An Index Expression
-
A Studio Sort
Each of these is explained in more detail below.
![](/PublicKnowledge/servlet/rtaImage?eid=ka2D0000000ThQr&feoid=00ND0000005rAjf&refid=0EMD0000000JHW1)
The easiest way to specify an Index is to simply specify it in the Index column of the Main Source. In this example, we sort the DVD titles by Title, which happens to be Index 2.
-
Go to Data View->Main Source.
-
Zoom (F5 or double-click) from the Index column. You will see a list of the available Indexes.
-
Press Enter to select the Index you want to use.
If the Index number should happen to change in the Studio (because someone added a new index, for instance), to the Index specified here would automatically change.
![](/PublicKnowledge/servlet/rtaImage?eid=ka2D0000000ThQr&feoid=00ND0000005rAjf&refid=0EMD0000000JHW3)
If you want to choose the index at runtime, you can use an Index Expression to choose the index. This is a good way to allow the user to choose the sort order, or you can use some logic to choose the correct sort order based on selection criteria chosen by the user.
![](/PublicKnowledge/servlet/rtaImage?eid=ka2D0000000ThQr&feoid=00ND0000005rAjf&refid=0EMD0000000JHW0)
-
From your Main Source, press Alt+Enter to go to the Properties of the Main Source.
-
Go to Details->Index. The second column is the Expression column. Zoom from here to enter an Expression that, at runtime, evaluates to a valid Index number.
In our example, we are choosing an index based on the selection criteria that the user entered. Note that while we could have just used the value the user selected (1, 2 or 3) directly, we use an IF to map the choice to INDEX literals. This is important, because if the index numbers should change in the future, the INDEX literals will change automatically.
![](/PublicKnowledge/servlet/rtaImage?eid=ka2D0000000ThQr&feoid=00ND0000005rAjf&refid=0EMD0000000JHW2)
If there is no index that does what we need, it is still a simple matter to display the records in the order you require. Magic xpa has a built-in Sort facility. You can specify any variable in this sort, and Magic xpa will use an SQL Order by, or, for an ISAM table, build a temporary index into the table on the fly at runtime.
-
Open the task you want to sort.
-
Select Task->Sort (Ctrl+T). This will open the Sort Indicator window.
-
The left side of the window will either be blank (if no sort currently exists) or will contain a list of variables (if a sort already is being done for this task). This list will determine the sort order of the table. In our example, the list will be sorted first by “Starring”, then by “Title”.
-
To add a variable to the list:
-
Some variables are too long to sort efficiently. You can cut down the size of the alpha field that is used to sort by, by entering the number of characters in the Size column. In our example, the “Starring” and “Title” fields are each 100 characters long, but we are only going to use the first 20 characters.
-
To delete a variable from the list, press F3 (Edit->Delete Line).
-
When you are done, click OK.
Hint: For ISAM tables, the Studio Sort is executed after the Range is executed. It makes sense to limit the range of records to some reasonable number when doing a Studio Sort, since an alternate table is being built on the fly.
The Online and Rich Client Samples projects (program DV03 and RDV03)