Grid: alphabetical order 1

[SitesMasstec]

Hello, colleagues!

I rarely use grids in my forms. I created one:

Is it possible that during run the second column (PORTO, from field nomepor from table) to appear in alphabetical order? (Of course, I want the data of first column -SIGLA- to acompany the data of the second column)


Thank you,
SitesMasstec

 

[Chris Miller]

With an index on nomepor you sort by that column. And index order is always ordering full rows, that's not a requirement needing more than a simple index. It would be a disaster if an index on one column would only sort that column, that's also not at all how indexes work, I wonder why you get the idea this could happen, maybe because Excel allows such sorting of only one column.

It's physically not possible to cut a record so that only one field of it sorts differently. An index final answer to the question of a locate or seek is the recordnumber, so it points to full records not just the one column that detemrines the index tree data structure.

Chriss

 

[Chris Miller]

I forgot the final helpful statemetn for you, once you have an index on the porto column - technical name nommpor, if I read this screenshot right, with INDEX ON ... TAG ..., which you only need to do once, you sort it by that index with

SET ORDER TO tagname

Where tagname depends on what name you gave the index.

That's all there is to it, and it's lightning fast sorting.


Chriss

 

[SitesMasstec]

Chriss, I want to index the whole row, but to consider the second column to index.

Of course, I want the data of first column -SIGLA- to acompany the data of the second column

May I have to take out the file PORTOS.DBF from the Data Environment?

Thank you,
SitesMasstec

 

[Chris Miller]

Do index just the porto column and you still sort whole rows, just do and see for yourself.

You don't need to take a table out of the data environment to index it, you add the index at design time, with the index tab of the table designer.

Or use the INDEX command, but just do it once. Once an index exists, it indexes all data, also future data, so you don't index with every form start. It's part of the table design to have indexes.

Chriss

 

[Mike Lewis]

Going further, you might want to let the user change the sort order on demand. For example, you might let them click on one of the column headers to sort on the corresponding column. This is a common thing to do. If so, execute the SET ORDER in the relevant Click event. And then set focus to the grid (call its SetFocus method). You have to do that in order for the change to become visible.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Your deleted post contained the error message "table has no index order set", which occurs, if you SEEK in a table that has no order set by n index.

I told you to create an index in the table designer, which you did, but I also told you you have to SET ORDER TO that index.

As you deleted this I assume you figured it out, finally.

Don't know whether you programmed a SEEK a SET RELATION, which is another reason to get this error, but overall it puzzles me, you're here on tek tips for 14 years and it seems to me this is your first time usage of indexes.

Indexes are not only the basis for sorting data, they are also necessary for relations, they are used to optimize SQL queries, LOCATEs and they can also be directly used by SEEK, you can't have lived through 14 years of VFP programming without indexes. It's the basis of what makes VFP tick with rushmore optimization and the reason people pick VFP for data centric applications.

Chriss

 

[Chris Miller]

Regarding your almost paranoid fear of an index on a single column rearranging the record composition by only reordering the values of that column while other values stick to their row:

An index does nothing in the DBF file, it creates a tag in a separate file, a main CDX file with the same name as the DBF file, typically (even in old foxpro versions) or a separate IDX file. It creates a data structure that makes searching specific values fast and also makes it easy to order data by the index expression, ie. typically one column. And it does order whole records only, it can't even do what you assume it would, because that would require moving data within the DBF. And that would be slow, indexes exist to accelerate things, not to slow things down.

Physically rearranging records only happens when you use the SORT command, that is quite obsolete, when using indexes. SET ORDER is not the same as physically sorting data, it means you tell VFP to traverse records from top to bottom by the index telling which recnos to visit in which order instead of going down the physical record number order from 1 to reccount.

An index on a single column is the most often used case of an index and so it would make indexing completely useless, if that would work as you think. The only index you could make not rearranging data is an index on all columns, what would that be usable for at all.

So to see with your own eyes you have a wrong idea about what an index does to the data I think you finally simply trusted me and saw that it didn't just sorted the one column.

I think you had further problems up to getting to the grid form. You can verify the index order effect much simpler by just BROWSE of the table with and without order set to the index.

Chriss

 

[SitesMasstec]

Chriss, yes, I deleted the post contained the error message "table has no index order set", because looking deeper in the code I saw the command SET ORDER TO SIGLAPOR, which was necessary to see if the typed SIGLA (for the siglapor field) was already used (it cannot be duplicated).

(In the table, I created the index, Ascending, for the two fields: siglapor and nomepor, as you advised me in your earlier post).

So, I saw that I must put in the parent form FORMPORTOS.SCX, Object: DataEnvironment, Cursor1, Procedure: Init, the following code bellow:

SET ORDER TO NOMEPOR
GOTO TOP

So, the problem was vanished, thank you, Chriss.

I think you had further problems up to getting to the grid form.

Yes, since I read in the year 2000 edition book:

The grid is the most maligned control in Visual FoxPro. Many experts have publicly
expressed the opinion that they never, under any circumstances use a grid for data
entry.

So, I had avoided to use Grid.



Thank you,
SitesMasstec

 

[Chris Miller]

SitesMasstec,

fine, to help finding the solution.

Regarding the quote about grids: That's unfortunate if such an assertion lead to not using grids at all.
The stress is on using grid for INPUT, ie. let users change data in grid columns.

But of course they use grids for displaying data.

Chriss

 

[mjcmkrsr]

Hi,

Many experts have publicly expressed the opinion that they never, under any circumstances use a grid for data entry.

Well, maybe they didn't know how versatile the grid and its columns are able to "behave" - readonly and/or hidden and ... (see code below).

*!*	grid_calculatedcolumn.prg
PUBLIC oform1

oform1=NEWOBJECT("form1")
oform1.Show
Read Events

Close all
Clear All

RETURN


**************************************************
DEFINE CLASS form1 AS form
	AutoCenter = .T.
	Caption = "Grid with calculated columns"
	Width = 450
	MinHeight = This.Height
	MinWidth = This.Width
 
	ADD OBJECT grid1 AS grid WITH ;
		ColumnCount = -1, ;
		Left = 12, ;
		Top = 42, ;
		Width = ThisForm.Width - 24, ;
		Height = ThisForm.Height - 54, ;
		RecordSource = "curTemp", ;
		Anchor = 15
 
		PROCEDURE grid1.Init
			 WITH This.Column1
				.ReadOnly = .T.
				.Width = 60
				.ControlSource = "C1"
				.Header1.Caption = "ID"
			 ENDWITH

			 WITH This.Column2
				.Width = 60
				.ControlSource = "F2"
				.Header1.Caption = "F2"
			 ENDWITH

			 WITH This.Column3
				.Width = 60
				.ControlSource = "F3"
				.Header1.Caption = "F3"
			 ENDWITH
		 ENDPROC 
 
	ADD OBJECT lblInfo as Label WITH ;
		Top = 6, Left = 234, Caption = "You may modify F2 or F3", AutoSize = .T., FontBold = .T.

 	ADD OBJECT cmdUndo AS CommandButton WITH ;
		Left = 12, Top = 6, Height = 24, Caption = "O-Data"
	
		PROCEDURE cmdUndo.Click()
			With ThisForm.Grid1
				.ColumnCount = -1
				.Recordsource = "curTemp"

				.Column1.Width = 60
				.Column1.Sparse = .F.
				.Column1.ReadOnly = .T.
				.Column1.Header1.Caption = "ID"

				.Column2.Width = 60
				.Column2.Sparse = .F.
				.Column2.Text1.Inputmask = "999,999"

				.Column3.Width = 60
				.Column3.Sparse = .F.
				.Column3.Text1.Inputmask = "999,999"
			ENDWITH
			
			LOCATE
			 
			WITH ThisForm
				.lblInfo.Caption = "You may modify F1 or F2"
				.Refresh()
			ENDWITH 	
		ENDPROC

	ADD OBJECT cmdDoit AS CommandButton WITH ;
		Left = 120, Top = 6, Height = 24, Caption = "Calculate"
	
		PROCEDURE cmdDoit.Click()
			
			Select C1, f2, f3, f2 + f3 as f4, (2 * f2) + (f3 / 4) as F5  from curTemp WITH (Buffering = .F.) into CURSOR curResults

			LOCATE
			
			With ThisForm.Grid1
				.ColumnCount = -1
				.Recordsource = "curResults"
				.SetAll("DynamicBackColor", "ThisForm.MyBackColor()", "Column")
				.SetAll("DynamicFontBold", "ThisForm.MyFontBold()", "Column")
				
				.Column1.Width = 60
				.Column1.Header1.Caption = "ID"

				.Column2.Width = 60
				.Column2.Sparse = .F.
				.Column2.Text1.Inputmask = "999,999"

				.Column3.Width = 60
				.Column3.Sparse = .F.
				.Column3.Text1.Inputmask = "999,999"

				.Column4.Width = 90
				.Column4.Sparse = .F.
				.Column4.Text1.Inputmask = "999,999"
				.Column4.Header1.Caption = "F2 + F3"

				.Column5.Width = 90
				.Column5.Sparse = .F.
				.Column5.Text1.Inputmask = "999,999.99"
				.Column5.Header1.Caption = "2*F2 + F3/4"
			EndWith
			
			WITH ThisForm
				.lblInfo.Caption = "Calculated data"
				.Refresh()
			ENDWITH 	
		ENDPROC

PROCEDURE MyBackColor()
	DO case
		CASE F2 < 250
			RETURN RGB(125, 255, 255)
			
		CASE BETWEEN(F2, 250, 500)
			RETURN RGB(255, 255, 125)

		OTHERWISE 
			RETURN RGB(255, 255, 255)

	ENDCASE 
ENDPROC

PROCEDURE MyFontBold()
	IF F2 < 250
	
		RETURN .T.

	ELSE
		RETURN .F.

	ENDIF 
ENDPROC

PROCEDURE Destroy
	Thisform.Release()
	CLOSE ALL
	Clear Events
ENDPROC

PROCEDURE Load
	LOCAL i
	CREATE CURSOR curTemp (C1 C(8), f2 I, f3 I)
	FOR i = 1 TO 20
		INSERT INTO curTemp VALUES ( "ID" + TRANSFORM(Int(Rand()*1001)), Int(rand()*1000), Int(Rand()*999))
	ENDFOR
	LOCATE 
ENDPROC

ENDDEFINE
*********************************************

hth

MarK