How to change sort order by clicking on grid column header. 1

[ralphcarter]

I have an application where I want to allow the user to change the display order of a table in the grid by clicking on the appropriate column header. I have tried using the code below. The order changes but the cursor does not stay with the current record. When I click on the header a second time the cursor then goes to the appropriate record. It appears the cursor is static in the display and does not move in the display to the appropriate record. What am I missing? I know this has to be possible.
Code below:

*nRec = RECNO()
SET ORDER TO TAG tagPlant
SEEK JOBXX ORDER TAG tagJobid
*GOTO RECORD nRec
this.Parent.Parent.coljobid.SetFocus
this.Parent.Parent.Refresh

I have tried this 2 ways. First with the seek using the original tag and that didn't seem to work then I tried to capture the record number and explicitly set the cursor to the record number. No success here either. The above code currently resides in the click event for the header (don't know if that matters).

All help is appreciated

Ralph Carter
Programmer
QUESTware Corp.

 

[Nifrabar]

FWIW some code I use succesfully in my apps.
I can't remember if I borrowed some code somewhere on the net..... but I updated it to meet my needs (yours too?)
-Bart

*in init of grid

WITH This
 
	LOCAL i, lcCaption, lnAlignment, llWordWrap
  	lcCaption = ""
  
  	FOR i = 1 TO .COLUMNCOUNT
 
    	IF TYPE('.columns(i).Header1')="O"
			lcCaption 	= .Columns(i).header1.caption
			lnAlignment = .Columns(i).header1.Alignment
			llWordWrap 	= .Columns(i).header1.WordWrap
      		.columns(i).RemoveObject('Header1')
  
      		.Columns(i).NewObject('Header2','SortHeader',gcDefaultDir+"\code\cls_GridSortHeader.prg")
      		.Columns(i).header2.caption 	= lcCaption
      		.Columns(i).header2.alignment 	= lnAlignment
      		.Columns(i).header2.WordWrap 	= llWordWrap
    	ENDIF
  	
  	ENDFOR 
  	 
ENDWITH

* code to create the new sortheaderclass
DEFINE CLASS SortHeader AS Header
lSortAZ 			= .F.
cControlSource		= ""
cGridAlias			= ""
lTagA				= .F.
lTagD				= .F.
*******************************************************************************
Function Init
LOCAL lcControlSource
LOCAL lnLeftPar, lnRightPar,lnNumbers 
lnNumbers = 0

WITH this
* in grids controlsource some like  allt(transform(table.field)) might exist
* only consider value between inner brackets, if any

	lcControlSource 	= .Parent.controlsource
	lnNumbers 			= OCCURS('(',	lcControlSource)
	IF lnNumbers > 0
		lnLeftPar 		= ATC('(', lcControlSource,lnNumbers)
		lnRightPar 		= ATC( ')',lcControlSource,1)
		lnLength 		= lnRightPar - lnLeftPar
		lcControlSource	= SUBSTR(lcControlSource,lnLeftPar+1, lnLength-1)
	ENDIF 


*	.cControlSource = JUSTEXT(.Parent.controlsource)
*	.cGridAlias		= JUSTSTEM(.Parent.controlsource)
	.cControlSource = JUSTEXT(lcControlSource)
	.cGridAlias		= JUSTSTEM(lcControlSource)
ENDWITH
********************************************************************************
Function Click
LOCAL lcControlSource, lcTagName, lnBuffermode, lnSourceType, ;
lnRecord, lcAlias, lnLastRecord, lnOldSelect, lcType

WITH this 
	lcControlSource = "" 
	lcTagName 		= ""
	lnSourceType	= 0
	lcAlias			= .cGridAlias
	lnOldSelect		= SELECT()
	lcType			= ""
	
	SELECT &lcAlias
  * table with no records need no sort
  	IF RECCOUNT() = 0
		* reset alias	 	
		SELECT (lnOldSelect)
  		RETURN
  	ENDIF 			
		
  * what alias are we dealing with? view or table ?
	lnSourceType	= CURSORGETPROP("SourceType",ALIAS())
	lnBufferMode 	= CURSORGETPROP("Buffering")
	lnRecord 		= RECNO()
  		
  * controlsource of column involved
	lcControlSource = ALLTRIM(.cControlSource)
	IF UPPER(.parent.CurrentControl) <> 'TEXT1'
			SELECT (lnOldSelect)
			RETURN
	ENDIF 
  * veldtype 
	lcType = VARTYPE(EVALUATE(lcControlsource))

	IF  !.lSortAZ
		lcTagName = LEFT(lcControlSource,9) + "A"
	  
	  * a tag exists		
		IF .lTagA
			SET ORDER TO TAG &lcTagname 
		ELSE
		  * create a tag but at first be aware of tablebuffering is active	 
			DO CASE 
		  * we are having a view; so tablebuffering has to set to 3		
			CASE lnSourceType = 1 AND (lnBuffermode = 1 OR lnBuffermode = 3 OR lnBuffermode = 5)
				=CURSORsetPROP("buffering",3)
		  * for tables tablebuffering 1 is fine (no buffering)			
			CASE lnSourceType = 3 AND lnBuffermode <> 1
				=CURSORsetPROP("buffering",3)
			ENDCASE
			
		  * create index tag sort ascending
			* workaround as C-field seems to fail for index if length is 254
			IF lcType = 'C'
				INDEX on LEFT(&lcControlSource,200) TAG &lcTagname ASCENDING 			
			ELSE  
				INDEX on &lcControlSource TAG &lcTagname ASCENDING 
			ENDIF 
	  	  * put buffering back to what is was before	
			=CURSORSETPROP("Buffering",lnBufferMode)			
		ENDIF 
	   * .lTagA is telling about the existance of an expected tag	
		.lTagA = .T. 
	ELSE 
	  * same comments in here, but now we want a descending sort
		lcTagName 		= LEFT(lcControlSource,9) + "D"
 		IF .lTagD
			SET ORDER TO TAG &lcTagname 
		ELSE 
			DO CASE 
			CASE lnSourceType = 1 AND (lnBuffermode = 3 OR lnBuffermode = 5)
				=CURSORsetPROP("buffering",3)
			CASE lnSourceType = 3 AND lnBuffermode <> 1
				=CURSORsetPROP("buffering",3)
			ENDCASE 		

			IF lcType = 'C'
				INDEX on LEFT(&lcControlSource,200) TAG &lcTagname DESCENDING 			
			ELSE  
				INDEX on &lcControlSource TAG &lcTagname DESCENDING 
			ENDIF 

			=CURSORSETPROP("Buffering",lnBufferMode)
		ENDIF 		
		.lTagD = .T.
	ENDIF
	*SET STEP ON
	
	* cycle through all columns and reset header-backcolor; also remove any pictures 
	FOR i = 1 TO .parent.parent.columncount
		.parent.parent.columns(i).header2.backcolor = thisform.backcolor 
		.parent.parent.columns(i).header2.picture   = "" 
	ENDFOR
	
	* give the header you clicked-on a different color and put a graphic on it showing sortdirection
	.backcolor = RGB(128,128,128)
*	.backcolor = RGB(255,0,0)
	.picture   = IIF(.lSortAZ,"arrowup.bmp","arrowdown.bmp")

	* toggle sortdirection 
	.lSortAZ = !.lSortAZ
	
	* refresh the grid
	.Parent.parent.refresh()
	IF lnRecord > 0
	* 
		IF this.Parent.Parent.lKeepCurrentRecord && this.parent.parent = the grid
			GO lnRecord
		ELSE 
			GO top	
		ENDIF 	
	ENDIF
	* reset alias	 	
	SELECT (lnOldSelect)
ENDWITH 
ENDDEFINE 
***************************************************************************
* eof

 

[JRB-Bldr]

I am not sure why you are doing 2 things in the Header's Click method.
1. Set Index Order
2. Seek some value

If all you want to do is to "change sort order by clicking on grid column header" and end up on the original record (assuming that the original record is included within the Index Expression parameters), then I use something like the following in the Click or Doubleclick Method for the column's Header:

* --- Change to Index Tag MyTag1 ---
SELECT MyTable
nRecno = RECNO()
cTag = TAG()
IF !(UPPER(cTag) == UPPER("MyTag1"))  && Determine Current Index TAG
   * --- Not MyTag1 - Set It ---
   SET ORDER TO MyTag1
ELSE
   * --- If already set, Toggle to no Order ---
   SET ORDER TO
ENDIF
GO nRecno
ThisForm.MyGrid.Refresh

A more generalized approach would involve creating Indicies on each and every field and then setting the Grid's column's ControlSource to the table's fields.

Then it could be something like (again - in the Click or Doubleclick Method for the column's Header):

SELECT MyTable
nRecno = RECNO()
cTag = TAG()
cControlSource = This.Parent.ControlSource
IF "MyTable." $ UPPER(cControlSource)
   * --- If necessary, parse out just the Field name ---
   cControlSource = STRTRAN(UPPER(cControlSource),"MyTable.","")
ENDIF

IF !(UPPER(mcTAG) == UPPER(cControlSource))  && Determine Current Index TAG
   * --- Not This Field's Index Tag - Set It ---
   SET ORDER TO &cControlSource
ELSE
   * --- If already set, Toggle to no Order ---
   SET ORDER TO 
ENDIF
GO nRecno
ThisForm.MyGrid.Refresh

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Well the simplest code you can have in a header click is:

SET ORDER TO TAG sometag ÍN (This.Parent.recordsource)

Other more complex code first tries to find a tag or even creates an index on the fly to order by. But if you have your tags you can "hardcode" such a one liner in all your headers at design time.

Just make sure you don't cause grid reconstruction/ gried goes blank behaviour be wrongly recreating the recordsource. If your recordsource is a view you need to index it every time you use it, eg use it with nodata, set buffering to row wise buffering, index, then set tablebuffering and requery it. If your recordsource is a table you shouldn't create indexes on the fly, as they become permanent. Too many indexes only seldom needed for ordering only make the cdx grow fast and write/update operations on the table take long.

Bye, Olaf.

 

[Mike Lewis]

Ralph,

Your code is very similar to code I've used many times. I can't see anything wrong with it. I always use the record number method of returning to the previous record.

The only small difference I can see between your code and mine is that I set focus to the grid as whole, whereas you are doing it on the specific column. Also, I don't do a refresh. But I can't see that that's important.

The other thing to remember with saving a record number is that it won't work if the table is empty or is at eof. You need to test that nRec is within range before you do the GOTO.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[ralphcarter]

OK I must be doing something wrong because I put the code below in my header:

nrec = RECNO()
MESSAGEBOX("recno = "+STR(nRec))
SET ORDER TO TAG tagInpDate
GO nRec
this.Parent.Parent.SetFocus
MESSAGEBOX("recno = "+STR(RECNO()))

The first message is 670.
The second message is 62.

It appears that if the record I am looking for sorts to off of the grid then the cursor stays on the same line of the grid. What am I doing wrong?

Ralph

 

[markros]

1. Could ARCC change the record?

2. Is it possible that your index uses FILTER() expression ?

 

[Mike Lewis]

One other possibility ... Are you sure you have selected the correct work area?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[JRB-Bldr]

Could it be that the original record is somehow excluded from the Index tagInpDate expression?

An example of that might be:

USE MyTable EXCLUSIVE
SELECT MyTable
INDEX ON InpDate TAG tagInpDate FOR InpDate >= GOMONTH(DATE(),-1)
SET ORDER TO
<do whatever>

SELECT MyTable
GO 670
dThisDate = MyTable.InpDate  && Value = {^09/01/2009}
SET ORDER TO tagInpDate
GO 670  && This record 'masked' and not included in Index expression
<result is not as expected>

The same this could occur, as mentioned above by markros by having a SET FILTER TO InpDate >= GOMONTH(DATE(),-1)

I'd recommend that within the Click method where this code 'lives' you use a SET STEP ON and examine the various settings/parameters line-by-line in the TRACE window.
In that manner you should be able to quickly find the answer.

Good Luck,
JRB-Bldr

 

[wbstrider]

This has only worked for me when I setfocus on the grid 1st then goto recno, then refresh

nrec = RECNO()
MESSAGEBOX("recno = "+STR(nRec))
SET ORDER TO TAG tagInpDate
*GO nRec
this.Parent.Parent.SetFocus
Go nRec && here
MESSAGEBOX("recno = "+STR(RECNO()))

One Code to rule them all, One code to bind them.

 

[TamarGranor]

One thing you should do is add the IN clause to the SET ORDER and GO commands and specify the alias in RECNO() to be sure you're really talking to the right table every time.

Tamar

 

[ralphcarter]

Thank you all for your input. Wbstrider's comment was on the money! All I had to do was move the GO command to after the setfocus.

This is why I come here for answers.

Thanks Again

Ralph

 

[Mike Lewis]

Ralph,

Glad to see it was such a simple solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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