Each of my forms has a 'List' page with a grid for navigating through the form's primary table. The first column is a seek column. Type in the first few characters and you are taken to that record. This is done with an index on this sort field and an incremental seek. The user can pick which order he wants the table (which is a local view) in.

Our applications require a lot of filling in of blank fields in records. One of the favorite ways to edit is to set the order to the column we are editing. The blank fields are then together at the top. When these fields are filled in of course the record jumps to its proper location. Fill in the vendor field with "Rexnord" and the goes to the location between "Pentex" and "Saveon". You then have to go back to the top of the list to find the next blank field.

What I want to do is to provide a switch to turn off this "Auto Sorting" to keep the record were it was.

What I need to do this is a parametric "Order By" in the create sql view. Something like :
Solution #1

public gcPublicOrderBy
gcPublicOrderBy = "eqno"
create sql view "equip_lv" as ;
   select eqno,* from equip order by ?gcPublicOrderBy
*** then
gcPublicOrderBy = "vendor"
= requery("equip_lv")

but this does not seem to work, giving me an error message like "Column " " not found".
neither does :
Solution #2

gcPublicOrderBy = "eqno"
create sql view "equip_lv" as ;
   select eqno,* from equip order by eval(gcPublicOrderBy)

what does work is
Solution #3

gcPublicOrderBy = "eqno"
create sql view "equip_lv" as ;
   select padr(left(eval(gcEQLVOrderBy),30),30) as SortField,;
     eqno,* from equip order by 1

However I am afraid of a speed problem using eval() in the SQL command since it evaluates for each record. Also it does not seem to allow me to order by columns from child tables I have in the view. For example the column "Vendor" is provided to the view from a join with table vendors. Setting gcEQLVOrderBy = "vendors.vn_name" does not seem to do it either.
Solution #4

gcPublicOrderBy = "eqno"
create sql view "equip_lv" as ;
   select padr(left(&gcEQLVOrderBy,30),30) as SortField,;
     eqno,* from equip order by 1

is the same as making the view

create sql view "equip_lv" as ;
   select padr(left(eqno,30),30) as SortField,;
     eqno,* from equip order by 1

and requires me to rebuild the view everytime the value gcPublicOrderBy changes. Since the view is stored in a shared database on the network there is the possibility that the User #1 would change the view, then User #2 changes the view and then User #1's requery() gets the result User #2 wanted. I do not know how to prevent this other than to build the view in a local DBC on the user's drive.

Does any one have any suggestions?