selected value from grid to another grid 2

[bharons]

Hey experts,
it seems my question is classic.
I have code like below.

 select * from crsresult where alltrim(thisform.text1.value) = thisform.text2.value into cursor 'crsResult1'

where textbox1 and textbox2 come from the afterrowcolchange grid, ie

 LPARAMETERS nColIndex 
thisform.text1.value = field(this.Columns(nColIndex).header1.caption) 
thisform.text2.value= EVALUATE(this.Columns(nColIndex).ControlSource)

and I put the results of the coding in recordsource grid2 with the data displayed as empty.
As far as I know, if we do it with coding like this:

 select * from crsresult where alltrim(name) = "ALDI"

the desired data appears

The question is whether we can sort the data by double clicking the grid method in any grid1 column so it can appear in grid2 (with the conditions of the 2 textboxes above)

 

[Chris Miller]

There's no inbuilt sort feature of grids. You can program into the header click event to SET ORDER to an index to sort by it.

That requires you to create and recreate indexes on crsResult1, because every query will repopulate the workarea and all associated indexes get lost. But it's no big deal, you just do an INDEX ON fieldname TAG fieldname for every fieldname you want the user to be able to sort by, so for all columns in the extreme case. Then SET ORDER TO TAG fieldname (which is the tagname of the index, too) and the data will sort by that column.

Chriss

 

[mjcmkrsr]

Hi,

...

and some demo code to illustrate what Chriss wrote

PUBLIC goForm

ON ERROR DO errHandler WITH ;
   ERROR( ), MESSAGE( ), MESSAGE(1), PROGRAM( ), LINENO( )

goForm = CREATEOBJECT("MyForm")
goForm.Show()

READ EVENTS

CLOSE ALL
CLEAR ALL 

**********

PROCEDURE errHandler
   PARAMETER t_error, t_mess, t_mess1, t_prog, t_lineno

   WAIT WINDOW + "Error number: " + ALLTRIM(STR(t_error)) + " Error message: " + t_mess + CHR(13) ;
		+ " Line of code with error: " + t_mess1 + " Line number of error: " + ALLTRIM(STR(t_lineno)) + CHR(13) ;
		+ " Program with error: " + t_prog
ENDPROC 

**********

DEFINE CLASS MyForm as Form
	Width = 540
	Height = 360
	MinWidth = This.Width
	MinHeight = This.Height
	MaxWidth = This.Width
	Caption = "Sorting data in grid"
	AutoCenter = .T.
	ShowTips = .T.
	Themes = .F.


	ADD OBJECT grdNames as grdBase WITH ;
		RecordSource = "csrNames"
		
		PROCEDURE grdNames.Init()
			WITH This
				.Column1.Header1.Caption = "N°"
				.Column2.Header1.Caption = "Name"
				.Column3.Header1.Caption = "Age"
				.Column4.Header1.Caption = "G°"
				.Column5.Header1.Caption = "City"
				
			ENDWITH 
			
		ENDPROC 
		
		PROCEDURE grdNames.Refresh()
			LOCAL lcDBClick as String, lcClick as String
			
			lcDBClick = "DoubleClick header to reset order"
			lcClick = "Click or RightClick header to sort by "
			
			DODEFAULT()

			WITH This
				.Column1.Header1.ToolTipText = IIF(ORDER() = "TNUMBER", lcDBClick, lcClick + "Number")
				.Column2.Header1.ToolTipText = IIF(ORDER() = "TNAME", lcDBClick, lcClick + "Name")
				.Column3.Header1.ToolTipText = IIF(ORDER() = "TAGE", lcDBClick, lcClick + "Age")
				.Column4.Header1.ToolTipText = "Sort by Gender not available"
				.Column5.Header1.ToolTipText = IIF(ORDER() = "TCITY", lcDBClick, lcClick + "City")
				
			ENDWITH 
		
		ENDPROC 
	
	PROCEDURE Load()
		CREATE CURSOR csrNames (iRNumber I,cName C(20), iAge I, cGender C(1), cCity C(20))
		
			INSERT INTO csrNames VALUES (3,'Sahra', 31, "F", "Bruxelles")
			INSERT INTO csrNames VALUES (4,'Jeoffrey', 20, "M", "Paris")
			INSERT INTO csrNames VALUES (6,'Jenny', 53, "F", "Den Haag")
			INSERT INTO csrNames VALUES (1,'Toni', 44, "M", "Rome")
			INSERT INTO csrNames VALUES (2,'Sophie', 76, "F", "Berlin")
			INSERT INTO csrNames VALUES (7,'Jeremy', 67, "M", "New York")
			INSERT INTO csrNames VALUES (8,'John', 19, "M", "Chicago")
			INSERT INTO csrNames VALUES (9,'Marie-Josée', 28, "F", "Quebec")
			INSERT INTO csrNames VALUES (10,'Karen', 62, "F", "Toronto")
			INSERT INTO csrNames VALUES (11,'Abi', 56, "M", "Zurich")
			INSERT INTO csrNames VALUES (12,'Bernhard', 42, "M", "Basel")
			INSERT INTO csrNames VALUES (13,'Christiane', 26, "F", "Marseille")
			INSERT INTO csrNames VALUES (14,'Doris', 29, "F", "Munich")
			INSERT INTO csrNames VALUES (15,'Fred', 58, "M", "Wien")
			INSERT INTO csrNames VALUES (16,'Georges', 40, "M", "Budapest")
			INSERT INTO csrNames VALUES (17,'Juanita', 36, "F", "Mexico")
			INSERT INTO csrNames VALUES (18,'Jhemp', 52, "M", "Luxembourg")
			INSERT INTO csrNames VALUES (19,'Jan', 57, "M", "Amsterdam")
			INSERT INTO csrNames VALUES (20,'Henrietta', 41, "F", "Baton Rouge")
			INSERT INTO csrNames VALUES (5,'Mark', 54, "M", "Bern")
			
		INDEX on iRNumber TAG TNumber
		INDEX on cName TAG TName
		INDEX on iAge TAG TAge
		INDEX on cCity TAG TCity
		
		SET ORDER TO
		
		LOCATE
		 
	ENDPROC

	PROCEDURE Init()
		DODEFAULT() 
		This.DoBinds()
		
	ENDPROC
	
	PROCEDURE DoBinds()
		LOCAL loCol, loObj

		FOR EACH loCol IN This.grdNames.Columns
			IF loCol.BaseClass = "Column"
				UNBINDEVENTS(loCol)
				BINDEVENT(loCol,"MouseMove", This,"GetColumnToolTipText")
			ENDIF 
						
			FOR EACH loObj IN loCol.Objects
				IF loObj.Baseclass == "Header"
					UNBINDEVENTS(loObj)
					BINDEVENT(loObj,"Click",This,"HeaderClick")
					BINDEVENT(loObj,"DblClick",This,"HeaderTwiceClick")
					BINDEVENT(loObj,"RightClick",This,"HeaderRightClick")
					BINDEVENT(loObj,"MouseMove", This,"GetHeaderToolTipText")

				ENDIF
			NEXT 
		NEXT
		
	ENDPROC

	PROCEDURE HeaderClick()
		LOCAL ARRAY laEvents[1]
		LOCAL lcOrderBy, loObject
		
		AEVENTS(laEvents, 0)

*!*			= MESSAGEBOX("Clicked: " + SYS(1272, laEvents[1]), 64, "Clicked Object", 10000)
			
		loObject = laEvents[1]

		lcOrderBy = SUBSTR(RIGHT(SYS(1272, loObject),9),1,1)

		IF INLIST(lcOrderBy, "1", "2", "3", "5")
			WITH This.grdNames
				.SetAll("FontBold", .F., "Header")
				.SetAll("FontItalic", .F., "Header")
				.SetAll("BackColor", RGB(228, 228, 228), "Header")
			ENDWITH 

			WITH loObject
				.FontBold = .T.
				.FontItalic = .T.
				.Backcolor = RGB(0, 201, 201)
			ENDWITH 
		ENDIF 

		DO CASE 
			CASE lcOrderBy = "1"
				SET ORDER TO TNumber ASCENDING  
					
			CASE lcOrderBy = "2"
				SET ORDER TO TName ASCENDING  
					
			CASE lcOrderBy = "3"
				SET ORDER TO TAge ASCENDING  
				
			CASE lcOrderBy = "4"
				= MESSAGEBOX("Sorting by Gender is not available", 64, "Setting Order", 2000)

			CASE lcOrderBy = "5"
				SET ORDER TO TCity ASCENDING  

			OTHERWISE
				= MESSAGEBOX("Call developper", 16, "Setting Order", 5000)

		ENDCASE

		LOCATE
		 
		This.Refresh()
		
	ENDPROC

	PROCEDURE HeaderRightClick()
		LOCAL ARRAY laEvents[1]
		LOCAL lcOrderBy, loObject
		
		AEVENTS(laEvents, 0)

*!*			= MESSAGEBOX("Clicked: " + SYS(1272, laEvents[1]), 64, "Clicked Object", 10000)
			
		loObject = laEvents[1]

		lcOrderBy = SUBSTR(RIGHT(SYS(1272, loObject),9),1,1)

		IF INLIST(lcOrderBy, "1", "2", "3", "5")
			WITH This.grdNames
				.SetAll("FontBold", .F., "Header")
				.SetAll("FontItalic", .F., "Header")
				.SetAll("BackColor", RGB(228, 228, 228), "Header")
			ENDWITH 

			WITH loObject
				.FontBold = .T.
				.FontItalic = .T.
				.Backcolor = RGB(0, 201, 201)
			ENDWITH 
		ENDIF 

		DO CASE 
			CASE lcOrderBy = "1"
				SET ORDER TO TNumber DESCENDING   
					
			CASE lcOrderBy = "2"
				SET ORDER TO TName DESCENDING   
					
			CASE lcOrderBy = "3"
				SET ORDER TO TAge DESCENDING   
				
			CASE lcOrderBy = "4"
				= MESSAGEBOX("Sorting by Gender is not available", 64, "Setting Order", 2000)

			CASE lcOrderBy = "5"
				SET ORDER TO TCity DESCENDING   

			OTHERWISE
				= MESSAGEBOX("Call developper", 16, "Setting Order", 5000)

		ENDCASE

		LOCATE
		 
		This.Refresh()
		
	ENDPROC
	
	PROCEDURE HeaderTwiceClick()
			WITH This.grdNames
				.SetAll("FontBold", .F., "Header")
				.SetAll("FontItalic", .F., "Header")
				.SetAll("BackColor", RGB(228, 228, 228), "Header")
			ENDWITH 
			
			SET ORDER TO
			LOCATE
			This.Refresh()

	ENDPROC 

	PROCEDURE GetHeaderToolTipText(nButton, nShift, nXCoord, nYCoord)
		LOCAL laEvents[1]
		LOCAL loObject
		
		AEVENTS(laEvents,0)
		
		loObject = laEvents[1]
		
		This.grdNames.cToolTipText = loObject.ToolTipText 
		
	ENDPROC
	
*!*	The procedure below AVOIDS the TTT to show when the mouse is hovering over columns	
	
	PROCEDURE GetColumnToolTipText(nButton, nShift, nXCoord, nYCoord)
*!*			LOCAL laEvents[1]
*!*			LOCAL loObject
*!*			
*!*			AEVENTS(laEvents,0)
*!*			
*!*			loObject = laEvents[1]
*!*			
*!*			This.grdNames.cToolTipText = loObject.Name

		This.grdNames.cToolTipText = ""
		
	ENDPROC
	
	PROCEDURE Destroy()
		ThisForm.Release()
		ON ERROR  && Restores system error handler.
		CLEAR EVENTS

	ENDPROC
ENDDEFINE 

**********

DEFINE CLASS grdBase as Grid 
	cToolTipText = ""

	Top = 6
	Left = 6
	Width = 540 - 12
	Height = 360 - 12
	ReadOnly = .T.
	Anchor = 15
	BackColor = RGB(0, 246, 246)
	ColumnCount = -1
	
	PROCEDURE ToolTipText_Access()
		RETURN This.cToolTipText
	ENDPROC

ENDDEFINE 

**********

hth

MarK

 

[Chris Miller]

Sophisticated, MarK.

The usage of BINDEVENTS is not what I thought of, but justifiable. Remember what I said in your previous thread, bharons?

That needs code in the grid, which you could write into it when designing the form, but that would be deleted whenever you set a new recordsource.

It is untrue of code/methods of the grid itself, but it is true of code you put in the header.click of a header ina grid column at design time. Because you remove the column and add new ones, natively empty in all methods. Therefore the only way to work without Bindevents would be having a grid already designed for the result cursor of which you never override the recordsource.

Ingredients for that are:
1. A cursor for the grid you create empty, say crsGrid.
2. A grid already bound to that at design time to fields of crsGrid
3. Querying into a cursor crTemp, then ZAP in crsGrid and APPEND from crsTemp into crsGrid.

Only then the grid columns stay as they are with their code.

If you do that you get around the steep learning curve about designing classes and what BINDEVENT means and does. A general header click could be (based on index tags being the same as field names):

lcField = This.Parent.Controlsource
SET ORDER TO TAG (lcField)

That's the short version of header click code.



Chriss

 

[bharons]

now i understand about to set order based on header click code.. thanks expert.. Chriss and Mark..