How to change sort order by clicking on grid column header

[titoneon]

Hi Everyone,
I know it is a lot about this Subject, but i have not found any similar to my needs, besides it is the first time i want to do this.
In my case i have form and in that form, i just droped the grid control, that grid is fill with data when the SELECT SQL is executed, so the grid's columns, header are created on the fly by the Select sql.

I am wondering if any one can guide me here, in my case to be able to do a sort when clicking on the grid header, below is some code i used to fill in the grid, i have a command search, that run the code below, if you can help me here how to treat this to be able to allow users to click the headers to sort, will be very nice

So the grid will be filled with the cursor RESULS2 as you can see, so i am assuming here that we need some code before the "ELse" command to make this grid able to be sorted on each column, any help very appreciated

thisform.Refresh()
doevents
PUBLIC lcJob_no  

SET EXCLUSIVE OFF
SET SAFETY OFF
SET CPDIALOG OFF
lcjob_no= thisform.txtTextBox.Value
thisform.label5.VISIBLE=.t.
thisform.label5.Enabled=.t.
path_1="S:\PRO50\APEX04\POTRAN04"  &&VFP 5.0 TABLE TYPE
path_2="F:\MFG\ENG_JOBS"           && FOXPRO DOS 2.0 TABLE        
path_3="F:\MFG\INS_LOG"            &&  FOXPRO DOS 2.0 TABLE
path_4 ="s:\pro50\apex04\apvend04" &&VFP 5.0 TABLE TYPE
path_5="s:\pro50\apex04\pomast04"

Select INT(VAL(t1.dept)) as ball_no, sht as Sheet, INT(t1.QtyOrd) as QTYORD, INT(t1.Qtyrec) as QTYREC,T5.PURDATE,T5.REQDATE,T5.BUYER, t4.company as VendorName, t2.part_type as  PRC, t2.draw_no, t1.item as ItemNo, t1.vpartno as vendorpartno, ;
t1.descrip, t1.Recdate, t1.purno ;
 From  (path_1) t1 ; 		 
	INNER Join (path_2) t2;
	ON INT(VAL(t2.ball_no)) = INT(VAL(t1.dept));
	INNER JOIN (path_4) t4;
	ON t1.vendno = t4.vendno;
	 INNER JOIN (PATH_5) T5;
	 ON T1.PURNO = T5.PURNO ;
	 WHERE  t1.reqno= lcJob_no AND t2.job_no=lcJob_no ; 
		ORDER BY 1,2,8 INTO Cursor RESULS1 readwrite

Local lnValue, lcSuffix, lcSheet
 Scan          
     lnValue = ball_No
     lcSuffix =  Iif(Between(Asc(Right(Alltrim(draw_no),1)),48,57),'',Right(Alltrim(draw_no),1))
     Do Case
         Case Between(m.lnValue,1,999)
             lcSheet = substr(ALLTRIM(Transform(m.lnValue,"9999999")),1,1)+m.lcSuffix

* Since converting character to integer will loose zeroes on
* the > left, then override those that are less than 200
             If m.lnValue < 200
                 lcSheet = '1'+m.lcSuffix
             Endif
         Case Between(m.lnValue,8000,8999)
             lcSheet = ''
         Case Between(m.lnValue,7000,7999)
             lcSheet = '7'+m.lcSuffix
         Case Between(m.lnValue,9000,9999)
             lcSheet = '9'+m.lcSuffix
         Otherwise
             
 lcSheet = substr(ALLTRIM(Transform(m.lnValue,"9999999")),2,1)+m.lcSuffix

     Endcase

     Replace sheet With m.lcSheet
 ENDSCAN
 GO top

Select ball_no, Sheet, QTYORD, QTYREC,PURDATE,REQDATE,BUYER, VendorName, PRC, ItemNo, vendorpartno, ;
descrip, Recdate, purno;
 From  RESULS1	ORDER BY 1 INTO Cursor RESULS2 readwrite	
 
thisform.label5.VISIBLE=.f.
thisform.label5.Enabled=.f.		

If _Tally > 0
	With Thisform.grid2
		.Visible = .T.
		.ColumnCount = -1 
		.RecordSource = 'resuls2'		
		.Refresh()		
		.Setall("DynamicBackColor", "IIF(resuls2.qtyord = resuls2.qtyrec, RGB(255,255, 0), 0xFFFFFF)") &&255, 255, 0 yellow	
		.Column3.DynamicForeColor=  "IIF(resuls2.qtyord= resuls2.qtyrec, RGB(255,0,0),RGB(0,0,0))" 
		.Column4.DynamicForeColor=  "IIF(resuls2.qtyord= resuls2.qtyrec, RGB(255,0,0),RGB(0,0,0))" 
	ENDWITH
	 THISFORM.SSGRIDLOCK1.Enabled= .T.
	 THISFORM.SSGRIDLOCK1.VISIBLE= .T.
	 thisform.buttonx1.Visible = .t.
	 Thisform.buttonx1.Enabled =.T.
	 Thisform.Sstoexcel21.Enabled=.T.	 
	 Thisform.command4.Enabled=.T.
	 Thisform.command2.Enabled=.T.
	 thisform.command1.Enabled= .F.
	 thisform.label8.Visible = .T.
	 thisform.label3.Enabled=.T.
	 thisform.label3.VISIBLE=.T.
	 Thisform.Text1.visible=.T.       
	 thisform.label7.VISIBLE=.t.  	 
Else
	Messagebox('There is not result for your Query, Check again your Entry',0+64,'Ok!')
	thisform.command1.Enabled= .F. 	
	Thisform.command4.enabled=.F.
	Thisform.command2.enabled=.t.
	thisform.label3.Enabled=.f.
    thisform.label3.VISIBLE=.f.
	thisform.buttonx1.Visible = .f.
	Thisform.buttonx1.Enabled =.f.
	Thisform.TxtTextBox.Value=''
	thisform.label7.VISIBLE=.f.
	Thisform.Text1.visible=.F.
   	
	Thisform.TxtTextBox.SetFocus
	With Thisform.grid2
		.Visible = .F.
		.ColumnCount = -1
		.RecordSource = '' 
	Endwith
Endif

Thanks

 

[Mike Lewis]

Hi Ernesto,

I haven't studied your code in detail, but I can give you some general advice for solving this problem.

First, you need to make sure the cursor that populates the grid is readwrite (which it is, according to your code).

Next, just after you create the cursor, create an index on each of the fields that correspond to a column in the grid.

In the Click event of each column's Header1, issue a SET ORDER command, to select the corresponding index. Immediately after that command, issue a SetFocus for the grid (this.parent.parent.SetFocus).

Basically, that's all you need.

You can optimise the process slightly be deferring the creation of the index until the first time the relevant header is clicked. Use ATAGINFO() to determine if the required index exists, and if it doesn't, go ahead and create it. But I suggest you get the basic functionality working first, and then come back to the optimisation when it does.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Hi Mike

You:In the Click event of each column's Header1, issue a SET ORDER command, to select the corresponding index. Immediately after that command, issue a SetFocus for the grid (this.parent.parent.SetFocus).

The Grid's columns, header etc, are created on the fly by the SELECT SQL, so i don't have in the Grid properties headers, columns, etc, so this mean, we are going to have to use another way to write code in the column's headers, so can you maybe help on that approach please ?
Thanks
Ernesto

 

[dmusicant]

I do this in a browse window by capturing the right click. I do this (in this particular app) for any of dozens of tables which are equipped with a column named "reorder." The reorder field is character and in it I have the names of whatever fields I want to have the ability to reorder the browse window by. Of course, there needs to be an index for each of those fields or I get an error. Continual right clicking will reorder the browse window sequentially in accordance with whatever field names are in the reorder field, in addition to one other field, which is the default field for ordering (that one doesn't need to be named in the reorder field). The field names in the reorder field are separated by at least one space. I don't need to click the header for these reorder actions, I just right click anywhere in the browse window to reorder it. This is convenient for me because this app goes to the last record before opening the browse window.

 

[Mike Lewis]

we are going to have to use another way to write code in the column's headers, so can you maybe help on that approach please ?

Create a custom column class. Write the SET ORDER, etc. code in the click event of the header of that class. Then, in your grid, set the MemberClass and MemberClassLibrary properties to point to that custom class.

The code in the header will have to know which column it belongs to (so that you select the correct index). It can do that by looking at the column's ControlSource (something like [tt]JUSTEXT(this.parent.ControlSource)[/tt]).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

>i am assuming here that we need some code before the "Else" command to make this grid able to be sorted on each column
Well, there is no simpy sort property, you have to do this all on your own.
And yes, you need to be able to add code in objects not existing at design time, but you can do that via OOP and classes, as you can specify what objects are created, not the native column or header objects, but your own classes.

The thought begins this way: You need to be able to react in the grid columns header click events, and code has to be added there at design time. It's very hard to solve a header click for the general case, and especially if columns are automatically created by the sql query result you bind you can't add code at design time in the individual columns, as they are not there at design time. So maybe you needd to rethink that, though you can influence what column objects are generated by the grid Memberclass and MemberclassLibrary properties. You set these at design time, keep columncount at 0 and any column generated by setting a column count or by setting the recordsource creates that column class.

The column class then in the same way needs a HeaderClass and HeaderClasslibrary and then you are at the level of the code for sorting the grid in the header. The hard part now is, you don't have a specific header for each column, you have a header class that needs to be able to sort, whatever is bound to the grid, whatever column is bound to the column and no matter if there is an index or not. Sounds quite impossible, but of course you can read your own column field by reading the controsource.

You can index on the fly, since you create the data to show qith a query, you can always index it on the fly, the first sort click will always take some more time, but that can only be prevented when binding the grid to a table, which already is indexed and that has many other limitations and disadvantages. You can also sort by redoing a query with an ORDER BY clause.

All this is much easier to do, if you prepare your grid at design time, specify all columns and headers and can even write individual order by multiple columns. Eg if your table contains persons with firstname and lastname clicking one of theses columns you typically don't just want to order by that single column, but secondary order should be the other one. You might think about offering a way to specify first, secondary and so forth order levels, but stop here.

As I know you, you shoud not aim for the general case, it's an advanced task to do. The simple solution you can offer is a combobox above the grid added to the controls the user defines filter criteria and offer two or the sortorders making sense. for the grid. And when you apply filter criteria you can also apply the selected order in the query and simply query the data in the order.

Bye, Olaf.

 

[titoneon]

Hello Mike and Olaf,
Very interesting approaches, thanks, will look on it.

 

[mjcmkrsr]

Hi,
Just type grid column sort into the search textbox and you'll find a lot of suggestions.
hth
MK

 

[mjcmkrsr]

Hi,

A very simple and elegant solution

[link ]

http://weblogs.foxite.com/ammarhadi/2009/06/19/sorting-the-grid-by-header-clicking-asc-dec/

[/url]

hth

MK