Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.

About two years ago, I was approached by one of my officemates who happened to be one of my application users just in time when I was having one of those “black” moods. A black mood, for me, is an emotional state oftentimes, infuriating, when after a hundred or so tries I still cannot get the whole damn routine to work right. Its usual evident signs are keyboard pounding and head banging against the computer screen.

Anyway, this user asked me if he could change the order of a particular list presented on a form based on a particular field. My application (written in Foxpro 2.6 for Windows) provided them -- the users, the option to change the order of the list according to my predefined index tags as any developer would do. Unfortunately, the field that he wanted was not in any of those index tags whether being a primary or a secondary key.

I took a deep breath and asked him what is the frequency of this peculiar report. Is it always officially needed like weekly, monthly or quarterly? None of the above. It’s by whimsical demand by one of the boys who holds office “up there”. It could be daily, hell, even every minute, twice a year or once in three years. The question of frequency, I deemed, was out of the issue. What matter at that time was one of my users was there standing in front of me asking if my application could do what he wanted.

I looked at him sternly, not that I was angry with him but just to make sure that he got my message right and told him I could not do it right away because it would require me to modify my system housekeeping hardcode routine and the rest of the users who are connected to the network to terminate their own sessions since re-building indexes must have exclusive use of files. With all these hassles for somebody’s caprice, I thought, it’s not worth all the effort. He said, okay and slowly walked away.

It was not a confrontation. I could not even call it an argument since I had it my way for less than a couple of minutes. That was two years ago. It left a pang of realization deep inside me that I should improve my indexing technique the next time around. Whether the basis of the request is sound or not, our users are our clients and as developers we should give them total satisfaction since we got paid for to make their computing lives easier. For most users, they demand flexibility and it’s our job to give it to them without jeopardizing the compliance to the rules of the business that has been translated into the systems we wrote.

In the days of procedural programming, the power is in the hands of the programmers and the users just blindingly abide when applications tell them “before you do that, you have to do this trivial procedure first”. Or to be vivid, before you can run one of those menu items, you should close your current form first by pressing Esc key n times, then do the blah-blah-blah… and select Enter. In the advent of OOP, users want more freedom. They want to open multiple forms, jumping from one to another because they want multiple presentations of their data. They want to go to step 6 from step 2 without passing through steps 3, 4 and 5. They want to go on with their data entry routine while the same application is crunching a batch process in the background at the same time feeding the printer the information it needs to produce a hardcopy the user requested. Users know what they want and they want it done and delivered. If you cannot do it, they’ll find somebody who will and don’t tell them that your app cannot do a simple re-ordering of the list based on their choices. Now that freedom is equated into power and OOP only masks the complexity of our ordeal as developers. Procedural programmers sneer at how the BASIC computer language faithfully features SUBROUTINES and GOTO dubbed as the “spaghetti code”. Now those same programmers drool over OOP which, for me, is like “spaghetti with more lots of sweet sauce and lots and lots of cheese toppings”. Oh, nice world.

In this article, I am going to tackle one question: how will you be able to let your user build indexes based on his or her own index expression that he/she has defined in a shared environment? Note that you don’t have a way of knowing what fields and field types the user would choose at runtime and by a click of the mouse, the list will be sorted accordingly. One of the most common approaches I encountered is through the headers of a grid. Double-clicking a header makes the entire column the primary key for sorting the grid. Clicking it again toggles the mode of the order from ascending to descending or the other way around. This approach, however, forces you to provide each field as the primary key of a tag which you know is not neat when you have, say, 8 to 15 columns. That’s why some programmers resort to using a listview for automatic sorting of columns but the bottleneck arises when you have a sizeable data source since the retrieving of rows slows down dramatically in a listview. And what if you have two fields combination instead of one, like CODE and DATE?

An article from Foxpro Advisor (March 2000 issue) uses grid but implements cursors instead. Since cursors are always exclusive to the local machine, the user is free to build indexes at runtime. What if you are working on a shared and live table? So, Isn’t it a fact that you cannot build indexes of a shared file in a shared (LAN) environment? The answer is yes and… no.

1. Yes, if the index file you want to build or to re-build is already shared. That leads us to the obvious: don’t share the index file!
2. No, because you can create an index file exclusive for your own use from an open shared table and even when there are other shared indexes open.

When INDEX ON … or REINDEX commands are issued, it’s the index file is being updated, not the table! Therefore you can build exclusive index files from a shared table in a shared environment.

I discovered this trick before the coming of the new millennium, last quarter of 1999. Sorry Foxite fanatics if you are not the first ones to know since I already sent this tip to Visual FoxPro Users Group (VFUG) for which they posted it on April 2000. This was long before Foxite was ever born. Anyway, it was just a short and quick tip. To make it up to you, I’ll expound it here with a complete and comprehensive sample for you to improve.

VFP has Sys(2015) function that returns a unique ten character string preceded by an underscore (_). We can use this to build a unique filename for our temporary index files. For a start, you can try the following in the command window:
USE AnyTable ORDER AnyExistingOrder
INDEX ON AnyField To Sys(2015) Additive
Close All

As I’ve said, it’s our job to disguise the complexity of our work from our users and let them use a friendly interface. The sample contains two forms: the first one – GRID.SCX, opens a table and display its records on a grid and the second form, NDXBUILDER.SCX, which is called by GRID.SCX, allows the user to select the table’s fields to be used as index keys. It is where the actual sorting takes place.

GRID.SCX contains three objects:
1. grdTable – a grid where records are displayed
2. cmdTable – a command button to open a table
3. cmdSort – a command button to activate NDXBUILDER.SCX, a modal form.

If there is already an open table prior to calling GRID.SCX, its records will auto-populate the grid right away. Otherwise, you may use cmdTable to open a table interactively.

GRID.SCX uses only two methods, INIT and UNLOAD and two custom properties, EXCLMODE to handle the current exclusive setting and OPENEDTABLE that captures the ALIAS() of the current open table.
*** GRID.SCX Init Method ***
With ThisForm
    .OpenedTable = Alias()
    .cmdSort.EnAbled = !Empty(Alias())
    .ExclMode = Set('Exclusive')
Set Exclusive Off
*** END of GRID.SCX Init *** 

I see to it that everytime I write an app, I always apply my own general rule and that is: AS MUCH AS POSSIBLE, NEVER ALLOW YOUR USER TO COMMIT MISTAKES. So the state of cmdSort button as explicitly set in the line .cmdSort.EnAbled = !Empty(Alias()) is dependent to the condition of whether there is an open table. If there is, enable the button. Otherwise, disable it since there is no candidate table for indexing.
*** GRID.SCX Unload Method ***
Local laFiles, lnCounter, lnArrayLen, lcTempFile, lcExclMode

Close Indexes
Close Tables All

Store 0.0 To lnArrayLen,lnCounter
Declare laFiles(1)
lnArrayLen = aLen(laFiles,1)
For lnCounter = 1 To lnArrayLen
    If Type('laFiles(lnCounter,1)') = 'C' &&array not empty
        lcTempFile = laFiles(lnCounter,1)
        Erase &lcTempFile

lcExclMode = ThisForm.ExclMode

Set Exclusive &lcExclMode
*** END of GRID.SCX Unload *** 

Given that indexes generated for this purpose are basically transient in nature, our app should be responsible enough to clean-up the mess it created. Our hint here is to delete all files that begin with the underscore character (as returned by SYS(2015)) and restore the previous EXCLUSIVE setting after that. Likewise, we are only concerned to remove temporary indexes so CLOSE INDEXES is a must command because you cannot delete an open or USEd file.
*** cmdOpen Click Method ***
Local lcTable

With ThisForm
    lcTable = .OpenedTable
    .OpenedTable = GetFile('DBF')
    .cmdSort.EnAbled = !Empty(.OpenedTable)
    If !Empty(.OpenedTable)
        Use (.OpenedTable)
        .grdTable.Recordsource = Alias()
        If !Empty(lcTable)
            Use In (lcTable)
        .grdTable.Recordsource = ''
*** END cmdOpen Click ***

*** cmdSort Click Method ***
Do Form NDXBuilder
*** END of cmdSort Click *** 

The Index Builder
Among other stuff inside NDXBUILDER.SCX, the important objects are two combo boxes, cmbPrimary and cmbSecondary to handle the primary and secondary key fields selection, respectively; a checkbox (chkDescend, initialized as .F.) to mark the mode of the index order (ascending or descending) and a command button (cmdGo) to commence the index process.

The form has three custom properties: 2 sets of arrays (aPrimary and aSecondary) that serve as the ROWSOURCEs of the two combos and a boolean (Continue, defaults to .F.) in case the current open table has no valid field types to sort. In the example, I excluded memo and general field types as key candidates because it’s very unlikely that one finds a practical reason to sort a grid based on these two kinds of fields. You can easily modify the line If !InList(Type(Field(lnX)),'M','G') below if you don’t agree with my thoughts.
*** NDXBUILDER Init Method ***
Local lnX,lnCounter

lnCounter = 0
For lnX = 1 To FCount()
    If !InList(Type(Field(lnX)),'M','G')
        lnCounter = lnCounter + 1
        With ThisForm
            Dimension .aPrimary(lnCounter,2),.aSecondary(lnCounter + 1,2)
            Store Field(lnX) To .aPrimary(lnCounter,1),.aSecondary(lnCounter,1)
            Store Type(Field(lnX)) To .aPrimary(lnCounter,2),.aSecondary(lnCounter,2)

If lnCounter =0
    MessageBox('No candidate column for index.',16)
    With ThisForm
        .aSecondary(lnCounter + 1,1) = Space(1)
        Store 5 To .cmbPrimary.RowSourceType,.cmbSecondary.RowSourceType
        .cmbPrimary.NumberOfElements = lnCounter
        .cmbSecondary.NumberOfElements = lnCounter + 1
        .cmbPrimary.RowSource = 'ThisForm.aPrimary'
        .cmbSecondary.RowSource = 'ThisForm.aSecondary'
        .cmbPrimary.ListItemID = 1
        .Continue = .T.
*** END of NDXBUILDER Init Method ***

Both arrays, aPrimary and aSecondary, have two subscript elements per row. The first contains the name of the field while the other contains the corresponding type. Also note that the array aSecondary has one more extra pair of subscript elements compared to aPrimary array. Since the secondary key field is optional, we got to have a safety provision should our user has no need for it, thus, the line .aSecondary(lnCounter + 1,1) = Space(1).

After initialization, the form’s Show method follows. This is where we guide the index builder to continue or not using our custom Continue property. The form will terminate early if the open table has no other fields except memo and/or general types.
*** NDXBUILDER Show Method ***
If !ThisForm.Continue
*** END of NDXBUILDER Show *** 

The Valid event of cmbPrimary likewise bears an interesting object manipulation.
*** cmbPrimary Valid event ***
Local lnX,llMatched

llMatched = (This.Value == ThisForm.cmbSecondary.Value)

If This.NumberOfElements > 1
    With ThisForm
        For lnX = 1 To This.NumberOfElements
            .aSecondary(lnX,1) = StrTran(.aSecondary(lnX,1),'\')
        .aSecondary(This.ListItemID,1) = '\' + .aSecondary(This.ListItemID,1)
        If llMatched
            .cmbSecondary.RowSource = 'ThisForm.aSecondary'
            .cmbSecondary.Value = Space(1)
*** END cmbPrimary Valid *** 

Before the focus leaves the cmbPrimary combo box, it checks for the value of cmbSecondary combo if the latter has the same value with the former. This could occur if a user is dumb enough to select one and the same field both for primary and secondary keys which is not a farfetched thing to happen. If it does happen, we force cmbSecondary to take the blank value. In between we mark the field under the cmbSecondary list that matches with cmbPrimary value as unselectable as expressed by the line .aSecondary(This.ListItemID,1) = '\' + .aSecondary(This.ListItemID,1). Never allow your users to commit mistakes.

The actual generation of index occurs when cmdGo is clicked. Here, we need a little conversion game.
*** cmdGo Click ***
Local lcKeyFields,lcIndexFile,lcNDXExpression,lnRecNo
lnRecNo = RecNo()
lcIndexFile = Sys(2015) 
With ThisForm
    lcKeyFields = AllTrim(.cmbPrimary.Value)
    If !Empty(.cmbSecondary.Value) 
        Do Case
        Case .aPrimary(.cmbPrimary.ListItemID,2) = 'T' && time
            lcKeyFields = 'TToC(' + lcKeyFields + ')'
        Case .aPrimary(.cmbPrimary.ListItemID,2) = 'D' && date
            lcKeyFields = 'DToS(' + lcKeyFields + ')'
        Case InList(.aPrimary(.cmbPrimary.ListItemID,2),'N','F','I','B','Y') && numeric
            lcKeyFields = 'Str(' + lcKeyFields + ",30,15)"
        Case .aPrimary(.cmbPrimary.ListItemID,2) # 'C' && logical and other types
            lcKeyFields = 'Transform(' + lcKeyFields + ')'
        Do Case
        Case .aSecondary(.cmbSecondary.ListItemID,2) = 'C'
            lcKeyFields = lcKeyFields + ' + ' + AllTrim(.cmbSecondary.Value)
        Case .aSecondary(.cmbSecondary.ListItemID,2) = 'T'
            lcKeyFields = lcKeyFields + ' + TToC(' + AllTrim(.cmbSecondary.Value) + ')'
        Case .aSecondary(.cmbSecondary.ListItemID,2) = 'D'
            lcKeyFields = lcKeyFields + ' + DToS(' + AllTrim(.cmbSecondary.Value) + ')' 
        Case InList(.aSecondary(.cmbSecondary.ListItemID,2),'N','F','I','B','Y') 
            lcKeyFields = lcKeyFields + ' + Str(' + AllTrim(.cmbSecondary.Value) + ',30,15)' 
            lcKeyFields = lcKeyFields + ' + Transform(' + AllTrim(.cmbSecondary.Value) + ')'
If !Empty(lcKeyFields)
    lcNDXExpression = 'Index On ' + lcKeyFields + ' To "' + lcIndexFile + '" Compact Additive'
    If ThisForm.chkDescend.Value
        Set Order To Order() Descending
    If _Tally > 0
        Locate Record lnRecNo
Release ThisForm
*** END of cmdGo Click *** 

A few more notes:
1. Watch out for the peculiarity of Transform() function between VFP5 and VFP6. If I remember right, at least two parameters are required in VFP5 while VFP6 Transform() can do well with one parameter as exhibited above.
2. The double quotes (“) before and after lcIndexfile in the line lcNDXExpression = 'Index On ' + lcKeyFields + ' To "' + lcIndexFile + '" Compact Additive' are included in case your current directory contains a space in between, like C:\My Documents. Our good Eric (den Doop) uses parentheses () instead so it’s a matter of preference.
3. The “Additive” clause is needed so that open indexes will not be closed. Only the current and newly built index takes precedence over the others. See HELP for more info.
4. The sample creates temporary index files in the current directory. If you are quite conservative concerning file management, you may edit the command and course these temporary files through a different location with lcIndexFile = “C:\TempDir\” + Sys(2015). Personally I use AddBs(Sys(2023)) + Sys(2015).

Figure 1 shows the sample output.

Fox And Theories
Obviously, Field() function returns a cryptic field name which our users may not be fond of. I’m not sure if this function has evolve in VFP7 and if it can accept a parameterized switch to return the field’s caption which is more English-like… that is, if you diligently supplied one for each field. If you are already comfortable with this practice – which I’m not, one of the workarounds is via the DBGETPROP(“MyTable.MyField”,”Field”,”Caption”) function but you have to extend the subscript length accordingly of the aPrimary and aSecondary arrays to capture the return value of DBGETPROP(). However, DBGETPROP() only works if the table is bound to a database. You’re out of luck if you are using free tables since calling the function without a ready open database triggers an error. The second one though requires more effort, is safe regardless you are using free tables or database-bound tables. This is what I use in my applications (Figure 2), although I’m not saying that this is the best approach. I just love the flexibility that it offers. It’s through the use of a data dictionary. You create a separate table (whether free table or not, doesn’t matter) to hold all references of other tables (again, database-bound or not) such as plain English field names, types, widths, input masks, formats and best of all, you can control the transparency of fields from your users. In real world, it doesn’t mean that if a field is of character type, users are free to exploit it whenever they could. There will always be exceptions and being a clever developer, you will know when to show all, limit, or hide your app’s data resources.

As you can see on the above figure, my own innovation includes a re-setting of order back to the default index tag and an option to save the index key definition so that the user doesn’t have to set it again in the future.

Before I Retreat Graciously Towards My Foxhole…
You can create a class out of our sample NDXBUILDER form and if your app requires you to have a tertiary index key, why not? Just add it in. But I’m sure many will be glad including myself if somebody out there can think of a cool idea on how to seamlessly implement two separate modes for each key like a descending primary key and an ascending secondary key, all at once.

Download code
Click here to download the code that is discussed in this article. The download is a zipfile. Its size is 5.954 bytes.


Dale Dedoroy Dale is a poor boy who finds joy in programming. He is currently the I.T. Officer of C.W. Mosser Environment Corporation.


Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: