Grid sorting

Hi;

I have a grid which is populated with the results of a SQL query.
I need to sort the grid depending on which column header the user clicks on.

I have not done this ever and I was wondering which is the correct approach:

- After I run the SQL query, run a series of 'index on' commands so I can use the indexes.
- Run a secondary query on the results of the query to produce the proper order when user click a header.
- Some other way - Which is probably the case :-)

Thank you all.

Visual FoxPro2

Hi Aleniko,

For me the best approach is to use a series of INDEX ON commands on the cursor then just use a Header class that has some sort of SET ORDER command in its click event and use that class as my columns' header class.

Hi Aleniko,
Neither I've tried it... But I shall suggest you a approach. You can give it a try.

Have a table with different tags (CDX). Mark this as the recordsource for the grid.
Run your query to fetch the data to populate a cursor.
Append the data to the 'recordsource' table from the cursor
on the Header's click event, add
set order to <field_tag_name>
this.parent.parent.refresh

I think this will work out....

Regards,
Markish

Not a very good idea because there may be some columns which have no index tag.

Hi,

I do agree upon that, still instead of indexing / sorting at the time of clicking, it would serve better.

Regards,

Markish

Does anyone here think that the query of a query solution is a good idea

Yes, I use the query version all the time with a safe select. See tightlinecomputers.com for info on safe selects.

If you are using VFP 9, you can use BindEvent() like this in the grid's Init():

FOR EACH loColumn IN This.Columns

FOR EACH loControl IN loColumn.Controls

*** Now make sure we call te sortgrid method when we click on a header

IF LOWER( loControl.BaseClass ) = 'header'

BINDEVENT( loControl, 'Click', This, 'SortGrid' )

ENDIF

ENDFOR

Then, in the SortGrid() method, you can figure out which header was clicked and take appropriate action like so:

*** First of all, see which column fired off this event

AEVENTS( laEvents, 0 )

loHeader = laEvents[ 1 ]

IF VARTYPE( loHeader ) = 'O'

*** First See if a ControlsSource was set for the column

WITH loHeader.Parent

lcField = ''

IF NOT EMPTY( .ControlSource )

*** Cool. Use it to decide how to sort the grid

IF NOT EMPTY( .ControlSource ) AND ( '.' $ .ControlSource ) AND NOT( '(' $ .ControlSource )

lcField = JUSTEXT( .ControlSource )

ENDIF

ENDWITH

ENDIF

You can use code like this to do the actual sorting:

LOCAL llAllowCellSelection, lcFrom, lnBuffering

*** There seems to be a refresh issue here

*** because even though the data is in the cursor

*** it is not showing up in the grid after the sort

*** and it looks like it is related to AllowCellSelection being .F.

llAllowCellSelection = This.AllowCellSelection

This.AllowCellSelection = .F.

This.Refresh()

KEYBOARD '{CTRL+TAB}'

lcFrom = This.RecordSource + [ ORDER BY ] + This.cSortField + [ ] + This.cSortOrder + [ INTO CURSOR qTmp NOFILTER]

SELECT * FROM &lcFrom

SELECT ( This.RecordSource )

lnBuffering = CURSORGETPROP( "Buffering" )

IF lnBuffering > 3

*** Since we are in view mode, the grid's RecordSource should have

*** no pending changes, but if it does, they are spurious and

*** we can throw them away

TABLEUPDATE( 1, .F., This.RecordSource )

CURSORSETPROP( "Buffering", 3, This.RecordSource )

ENDIF

ZAP

APPEND FROM DBF( 'qTmp' )

GO TOP IN ( This.RecordSource )

CURSORSETPROP( "Buffering", lnBuffering, This.RecordSource )

USE IN qTmp

This.AllowCellSelection = llAllowCellSelection

This.Refresh()

This.SetFocus()