Order! Order! Order in the Files!

by Steve Parker

Published 1999-05-01    Printer-friendly version

NOTE: Clarion Magazine was not able to obtain an archive of the original source code for this article. If you have the source, we'd very much appreciate it if you would email it to us so we can post it for other readers.

Users can be awfully ... ah, odd, creatures. Sometimes the tiniest little thing thrills them beyond belief. Sometimes you just can't do enough for them. Either there's always another request or they desperately need reality therapy. A poem, titled "Users" I picked up on-line several years ago ends "It's just what I asked for, but not what I wanted."

One of the most frequent, if not the most frequent, requests is "I want to see my data in a different order," i.e., "I need a new key."

Perhaps because of my years working with TopSpeed's DOS products, requests for new keys fill me with a sense of dread. A new key means more disk space, more memory, more I/O. A new key means a file conversion. New keys are something to be avoided whenever possible.

Newer PCs by and large alleviate disk and memory concerns. File Manager 2 makes file conversion much less of a headache. Still, large dictionaries or numerous structure changes can significantly impact program loading.

However modern the PC, however much disk space and memory, adding a key still impacts I/O. Nothing can stop that. That's one of the consequences of using a key: it is maintained dynamically. Any time the Enter Key is pressed, even if you haven't actually changed anything, there is I/O. Enough keys can grind the best planned application to a halt. Add networking into the equation ....

Dynamic Indices can bail you out of adding keys for reporting. Yes, Dynamic Indices have to be built each time they are used. Yes, Dynamic Indices across a network can be very slow. However, they do work and they do allow you to create a new sort order for a report (or offer multiple, user selectable sort orders for a report). Dynamic Indices can be built while other threads or other users also have the file open.

But, use a Dynamic Index on a browse? No, I don't think so.

A standard index, if you are using TopSpeed files, can be built incrementally, dramatically shortening build time. But, BUILD() requires exclusive access to the file (or that the file be locked). This means that no one else may access the file while the index is built.

If you can be relatively confident that build times will be reasonable, you will not lock out others for intolerable amounts of time. For example, a full BUILD on 12.000+ records took 66/100 of a second on my 266 notebook. Subsequent builds were instantaneous.

Unfortunately, you cannot do the BUILD if the file is opened on another thread or workstation. Nope, too dicey; ain't gonna work.

Browse Basics

A Clarion browse is an extremely complex object. It is composed of one or more disk files, a View constructed from those files, and a queue.

The View is a logical structure, residing in memory, much like a queue. Unlike a queue, a View can and does have access to file fields. The View does not have its own file buffer(s) but refers to the buffers of its component files. (Thus, it can also affect the underlying files.)

The queue is what the user sees on screen. Typically referenced in code as something like Queue.Browse:1, it is constructed from the View.

Thus, the View is the important element. The queue is just for display.

Views have the important characteristic that they can be sorted. Views have a property, Prop:Order for just this purpose. Because Order is a property, it can be set and re-set. If re-set, the queue built from it will also be in a different order and, therefore, what the user sees will be in a different order.

In other words, the Order property gives us something like a Dynamic Index. But unlike a Dynamic Index, a View can include related files, which a Dynamic Index cannot.

In other words, there is a property of a browse object that allows us to emulate as many keys as we want without actually declaring any of them.

In other words, with a bit of proper prior planning, "key anxiety" (as I once labeled it) can be a thing of the past. The key to Keys is in the View.

Checking the documentation on "View" implies that we can change orders with something like:

MyView{Prop:Order} = '+Cus:FirstName,-Inv:Date'
Set(MyView)
Next(MyView)

Not only does this seem like a bit of work (in fact, I don't think it will work), the documentation indicates that Prop:Order takes an expression, not a variable. But, a variable is what is needed for the kind of flexibility needed here. So, Prop:Order is out.

If you don't need the flexibility offered by using a variable, you can still safely ignore Prop:Order. The Additional Sort Fields prompt is all you need (see Figure 1, below).

When you think about this a bit, you just have to figure that there is an ABC method that will do all of this in one or two statements.

ABC to the Rescue

"You asked for it, you got it" SetOrder!

Introduced in Clarion 4, SetOrder "replaces the active sort order for the ViewManager object." Re-read that.

SetOrder re-sets the active sort order. This means that the SetOrder method sorts the View and activates the new order, allowing rebuilding the queue. Moreover, it can take a variable. Just what we need.

Because SetOrder can take a variable, something like:

LOC:MySort = '+INV:Date,-CUS:PostalCode'
BRW1.SetSort(LOC:MySort)

suggests itself.

After LOC:MySort is primed, ThisWindow.Reset(1) to refresh the window. Voila! Ok, not "voila" just yet; fix the 801 error by binding LOC:MySort and then "voila!"

I wish I could say that I discovered this by analysis. I wish I could say I discovered this by applying the methodology suggested in "Making the Transition to ABC" in the on-line help.

In fact, traversing the class browser might have lead to discovery of the SetSort method. Understanding that the foundation for the solution lays in a View, there are very few ViewManager methods that even appear worthy of research. AddOrder, AppendOrder, SetSort, SetOrder is about the full list.

Based on the simple meanings of the terms, SetSort and SetOrder would be my first candidates. SetSort eliminates itself by requiring a previous AddOrder.

I wish I could say I discovered SetOrder by research or insight into the ABC way of doing things

But, I didn't.

I discovered SetOrder in the People example, PrintPEO:KeyID procedure, ThisWindow (Report manager) OpenReport embed.

Implementation

Since SetOrder does virtually the entire job, implementing it is quite easy. Assuming you have bound and primed the variable you're using (all variables referenced in SetOrder must be bound):

BRW1.SetOrder(LOC:SortOrder)
ThisWindow.Reset(1)
Select(?Browse:1)

Listing 1

is all that is required. Placement of this code will depend on where LOC:SortOrder is or can be changed.

Since ABC does all the hard work, you are free to spend your design time implementing ways of priming the variable.

In the demo application accompanying this article, I have hard-coded a pair of variables (for example, check ?CurrentTab ... Accepted ... NewSelection). A Tab is created for each (one dynamically at runtime). So, to all intents and purposes, the browse appears to provide four keyed orders (though only two actually are Keys). Note that I have left my experimental notes in place.

Hard-coding values for the variables may seem like a bit of a "cheat." But, if you think about it for a moment, it emulates real situations. So long as the variable has a value by the relevant embed, it will work.

How can you prime the variable at runtime? My specific need is to retrieve a file record containing the string value. In this case, priming the variable would be a simple matter of getting a record from a file. A file loaded drop placed on the Tab will do (in which case, the code in Listing 1 should be moved to the Accepted embed for the FLD).

Similarly, you could get a sort order record by a lookup type procedure called from a button or a hot key. In this case, Listing 1 would go after the procedure call.

It is even possible to create Tabs for each file record, one for each sort order retrieved. In this case (similar to the implementation in the demo app), the Tab's NewSelection embed is where the code goes.

Radio buttons or check boxes ... well, you've got the idea.

If you want something completely dynamic, you can pop up a procedure to construct an expression and load it into your variable. Even easier would be Dennis Evan's Run Time Browse Sort Order Class (available at CWICWEB's download site, www.cwicweb.com/apps/cwlaunch.dll/download.exe.0).

Dennis' class and template wrapper supports completely dynamic runtime browse sorting though not storing the "queries." A small EXE I made using this tool is also attached. You will find it well thought out and quite thorough.

But what about performance?

One of the advertised facts about the View engine is that it will automatically use existing keys whenever possible. So, to test the worst possible scenario, I tested three files with ad hoc sort orders referring to no key components and mixed ascending and descending somewhat randomly. All selected fields were strings, including non-required fields. As I said, "worst case."

A 200 record TopSpeed file re-sorted instantaneously. A 12,400 record TPS file took seven seconds to re-sort. Worst case: a 70,000 record Clipper file, three non-key strings in the new sort took about 75 seconds. The reference machine is a 266 laptop (roughly equivalent to a 200-233 desktop).

To test the "automatically uses existing keys" claim, I added a fourth field to the Clipper file sort order and made it the first "node" in the sort order. This string field is a non-unique key field.

While the three node version of this order took over a minute, the new four node sort order took 28 seconds (with quite a bit of disk activity though less than a full BUILD). Claim confirmed and the moral of the story is to do what you can to "assist" your users in picking a key field for the first ad hoc node.

The 12,400 record TPS file went from seven seconds to 2-3. Interestingly, this figure did not change when using a string which exactly duplicated an existing key. So, the price you pay for this flexibility is speed; SetOrder simply isn't as fast as a real key.

But, by way of contrast, I have 4000 record Btrieve file with a Dynamic Index. A three node sort (all fields are key fields), on the same machine, takes 20 minutes to build and produce a report.

What C5A Brings to the Table

The code in Listing 1 is three lines. In fact only the first two are required to implement dynamic sort orders. The third simply makes sure that the list is selected so I can use the cursor keys in the browse box, continuing to ignore the mouse. The only "work" is in ensuring that the variable has a value.

How much easier could things be? How much easier can you want it?

How about no code?

In C5A, the Additional Sort Fields prompt supports variables (it supported only constants before).

Now you can simply create a fixed Tab. Instead of naming a Key on the Conditional Behaviors Tab, name a variable in the Additional Sort Fields prompt:

Now the templates automatically implement a variable-based sort order in the same way that Additional Sort Fields are implemented. This is because the method used to implement Additional Sort Fields (specifically, AddSortOrder plus AppendOrder) accepts both strings and variables.

This means that all you, as the designer, must do is ensure that the variable has a value. I doubt that even the Wizatrons, when complete, can make it any easier than this.

Summary

Dynamic browse orders couldn't be easier. You can either use SetOrder or the template's built in facilities. The "hard" part is priming a variable.

Slower than genuine Keys or current Indices, dynamic orders are flexible. If your customer is constantly asking for new viewing orders or you have multiple customers with different viewing needs, the speed-flexibility trade-off must be given serious consideration.

Oh, did I mention that SetOrder also works in reports?

Printer-friendly version

Copyright © 1999-2009 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942