order a view

[AlastairOz]

I am trying to find out how to set the order of my view, (ascending or descending) with program code.
I know there is the option in the view designer, but I need to reorder the view with command buttons.
Can anyone help?

 

[Mike Lewis]

Alastair,

First, you need to create an index for each of the sequences. You have to do that after you open the view (because indexes are not retained when you close a view).

Then, in your command buttons, issue a SET ORDER command to select the appropriate index. Finally, refresh the form or grid so that it reflects the new order.

Be aware that this will slow down the loading of the form, because it takes time for the indexes to be created.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

You can index a view. However, keep in mind that to be able to create an index the view should not be 'table buffered'. Default buffering for views is 'row buffering', you can index if you haven't changed it, or you can switch to 'row', index and switch back to 'table' buffering. ie:

cursorsetprop('Buffering', 3, 'MyView')
index on myField tag myField && [descending]
cursorsetprop('Buffering', 5, 'MyView')

Cetin Basoz
MS Foxpro MVP, MCP

 

[Olaf Doschke]

Also be aware you can only index views in record buffering mode, you typically want it in table buffering mode to buffer all changes and only apply them at TABLEUPDATE() or revert them with TABLEREVERT().

That's another reason to index the view at the form load or init. Also it's unfortunately the reason you cannot create indexes on the fly only when the user wants to reorder, as you'd need to change buffermode and therefore will indirectly trigger saving all changes.

So what you do is:

Local lcViewname
lcViewname = "viewname"
USE (lcViewname) IN 0
SET MULTILOCKS ON
CursorSetprop("Buffering",3,lcViewname)
Select (lcViewname)
INDEX ON expression TAG tagname
INDEX ON otherexpression TAG tagname2
*...
CursorSetprop("Buffering",5,lcViewname)

Afterwards you can sort the view by SET ORDER TO TAG tagname
The last index you create is also set, so create the index for the standard order of the view as the last index.

You will want to keep the view small in size, only query records needed at the moment, to have a low overall intialisation time for query and indexing.

So instead of first showing all records you rather ask for a filter of data. Perhaps there is also a standard filter you can apply like data of the last month.

Bye, Olaf.

 

[AlastairOz]

Thanks for that,
The idea was to give the user the option of the way the records appear in the grid.
Some people like to have the new records added at the bottom while some like them added at the top, pushing the older records down.
What I might do then, is have a "set users preferences" screen, so when they login, the forms load the way they like.
I will just remove the option buttons from the form.
That way its set on form load.

Regards
Alastair

 

[Olaf Doschke]

Don't be afraid.

Offering the option to sort in whatever way is easy and fast once the indexes are there. Thee trick is rather to not pull in all data, if there is much data at all. If we talk about 10.000 records you already have more than users will browse through in a grid, but indexing in such a order of magnitude does not hurt much.

Bye, Olaf.