Sort a column in a Grid 1

[brownb1]

I have a column (last name) in a Grid and I need to sort it. Any help would be appreciated.

 

[MarciaAkins]

Hello Brownb1.

This solution requires VFP version 8.

Add a method called SortGrid to your base grid class and code it like this:

LOCAL laEvents[ 1 ], loHeader, lcField, loColumn, lcSortOrder, loControl
LOCAL llFoundColumn, llAllowCellSelection, lnRecNo

*** 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
    ENDIF
  ENDWITH
  *** we have a field - let's see if it already has a sort order set
  *** if it does, it will have the appropriate picture in the header
  lcSortOrder = ''
  IF NOT EMPTY( loHeader.Picture )
    lcSortOrder = IIF( LOWER( JUSTFNAME( loHeader.Picture ) ) == 'down.bmp', '', 'DESC' )
  ELSE
    *** See if there is a visual cue on any of the other grid
    *** column headers and remove it if there is
    FOR EACH loColumn IN This.Columns
      FOR EACH loControl IN loColumn.Controls
        IF LOWER( loControl.BaseClass ) == [header]
          IF NOT EMPTY( loControl.Picture )
            llFoundColumn = .T.
            loControl.Picture = []
            loControl.FontBold = .F.
            EXIT
          ENDIF
        ENDIF
      ENDFOR
      IF llFoundColumn
        EXIT
      ENDIF
    ENDFOR
  ENDIF
  
  *** if we have a field - let's sort
  IF NOT EMPTY( lcField )
    *** 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}'
    
    *** Check to see if the tag exists assume
    *** that if there is a tag on this field, it has the same name as the field
    IF IsTag( lcField, This.RecordSource )
      lnRecNo = RECNO( This.RecordSource )
      *** Go ahead and set the order for the table
      SELECT ( This.RecordSource )
      SET ORDER TO ( lcField )
      This.SetFocus()
      IF lnRecNo # 0
        GO lnRecNo IN ( This.RecordSource )
      ENDIF
      *** And set the visual cues on the header
      loHeader.Picture = IIF( EMPTY( lcSortOrder ), [..\graphics\up.bmp], [..\graphics\down.bmp] )
      loHeader.FontBold = .T.
      This.AllowCellSelection = llAllowCellSelection
    ENDIF  
  ENDIF
ENDIF

This code in the grid's Init:

*** now make sure that the dblclick method of all the contained text boxes
*** delegate to the grid's dblclick()
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
ENDFOR

IsTag function lloks like this:

*-- Passed the name of an index tag returns true if it is a tag for the specified table. Uses table in the current work area if no table name is passed.
FUNCTION IsTag( tcTagName, tcTable )
  LOCAL ARRAY laTags[1]
  LOCAL llRetVal
  *** Did we get a tag name?
  IF TYPE( 'tcTagName' ) # 'C'
    *** Error - must pass a Tag Name
    ERROR '9000: Must Pass a Tag Name when calling ISTAG()'
    RETURN .F.
  ENDIF
  *** How about a table alias?
  IF TYPE( 'tcTable' ) = 'C' AND ! EMPTY( tcTable )
      *** Get all open indexes for the specified table
      ATagInfo( laTags, "", tcTable )
  ELSE
      *** Get all open indexes for the current table
      ATagInfo( laTags, "" )
  ENDIF

  *** Do a Case Insensitive, Exact=ON, Scan of the first column of array
  *** Return Whether the Tag is Found or not
  RETURN ( ASCAN( laTags, tcTagName, -1, -1, 1, 7 ) > 0 )
ENDPROC

Now all of your grids get the ability to get sorted by any column that has a tag with the same name as the field that is used in its controlSource. No other code required as long as your grid class inhrits from this one.



Marcia G. Akins

http://www.TightlineComputers.com

 

[baltman]

if the recordsource is a table or cursor, use INDEX ON MyField [DESCENDING] TAG MyTag.

Another option is to use SQL to create a newly sorted table using SELE * from MyData ORDER by XXX. You need to watch how you implement this solution as a grid hates when it's recordsource gets pulled out from under it.

An example of the 2nd option in action resides in faq184-4339

Check out the help file for additional info.

Brian

 

[brownb1]

I'm using VFP 6.... Not sure what this is, I need step by step help on sorting a column in a grid.

 

[jimstarr]

Very basically, you don't actually sort the column, you sort the table underlying the grid.

USE mytable EXCLUSIVE
INDEX ON lastname TAG lname
SET ORDER TO lname

Now the grid will appear in last name order.

Jim

 

[JRB-Bldr]

For one client I use the Grid Column Header's DoubleClick Method to make this happen.

I also keep a "Click Counter" so that I can track how many times the header has been double clicked.

DblClick #1 - Sort Descending
DblClick #2 - Sort Ascending
DblClick #3 - Original (non-sorted) Order
- and repeat -

Since I apply this to any column within the table, I have code which will examine the table indicies to see if Indicies already exist for that field (Column).

If so, then use them.
If not, build a new set of Indicies such as:
INDEX ON ThisField ASCENDING TAG DownOrdr
INDEX ON ThisField DESCENDING TAG UpOrdr

Obviously the table must be opened EXCLUSIVE to dynamically crate the needed Indicies. Therefore within the Grid I use a Temporary copy of the original table for this purpose so that others may also be accessing other records within the table.

Good Luck,
JRB-Bldr

 

[brownb1]

More confused. Where on the grid do I put this. Does this go on the grid properties or form properties.

 

[JRB-Bldr]

When you do an action (such as Double Click on a Column's Header Box), the event will look to see if there is a Method associated with it which needs to be executed.

The Methods may be created as an over-all Form Method or a specific Grid Method.

The code can be put directly into the Grid's appropriate Method (such as ThisForm.Grid1.Column1.Header1.DblClick)

Or the code can be put into a Form Method, as long as the appropriate Grid Method calls it with something like ThisForm.SortMethod

Where you put the code depends, in part, on how you want things to operate and somewhat on your personal coding style.

Good Luck,
JRB-Bldr

 

[MarciaAkins]

Hello Brownb1.

>> I'm using VFP 6.... Not sure what this is, I need step by step help on sorting a column in a grid. <<

There is a ready made sortable grid class (designed in VFP version 6) in Chapter 6 of "1001 Things You Wanted to Know About VFP". The class is fully explained and demonstrated in a sample form.




Marcia G. Akins

http://www.TightlineComputers.com