Indexing a numeric order in a grid on a form

[Steve-vfp9user]

Hello

I am looking for advice on how to display a set of records in a specific order within a grid.

A couple of my employees supply statements. Sometimes, they supply more than one statement. My table has a field called WITNO (This is the actual witness number) and WSTATNO (This is the statement number they have provided as this could be 1, 2, 3 etc)

I have tried the following:

tempfile=SYS(3)
SELECT * FROM MYFILE WHERE MASTLINK=mmastlink ORDER BY ;
  WITNO, WSTATNO INTO TABLE tempfile+'.dbf'

The above puts the records in order:

1, 1
1, 2
1, 3
2, 1

So that’s fine because the order is correct however, if I add a record to employee 1 and make it statement 4 (so effectively it would be 1, 4 the order on the grid after adding the record shows:

1, 1
1, 2
1, 3
2, 1
1, 4

Where it should show:

1, 1
1, 2
1, 3
1, 4
2, 1

I have tried reversing the field names on INDEX but do not get the desired result in the grid.

Just one other thing, every time I add a record, the tempfile+’.cdx’ file is deleted and a new one created (I tried this just in case):

INDEX ON WITNO+WSTATNO1 TAG WITNO

What am I doing wrong please?

Steve

 

[Olaf Doschke]

if witno and wstatno1 are numeric fields you index on a sum, not on the concatenation, so you index on
1+1 = 2
1+2 = 3
2+1 = 3
1+3 = 4
1+4 = 5

Why index at all, you set the order by selecting with an ORDER BY clause, the data in the tempfile+'.dbf' is sorted already.

Bye, Olaf.

 

[mjcmkrsr]

Hi vfp9user,

What is your field name WSTATNO or WSTATNO1?

WSTATNO (This is the statement number they have provided as this could be 1, 2, 3 etc)

INDEX ON WITNO+WSTATNO1 TAG WITNO

Why not have a table with the associated CDX and append from you temporary table - it would allow you NOT to recreate the CDX each time

every time I add a record, the tempfile+’.cdx’ file is deleted and a new one created

Your SQL statement should produce the desired results (please test code below)

**************************************************
PUBLIC oForm

oForm = NEWOBJECT("form1")
oForm.Show
Read Events
Close all
Clear All
RETURN


**************************************************

DEFINE CLASS form1 AS Form
	AutoCenter = .T.
	Caption = "Sales"
	ShowTips = .T.
	Height = 480
	Width = 648
	MinHeight = This.Height
	MinWidth = This.Width
	

ADD OBJECT grdSales AS Grid WITH ;
	Left = 12, ;
	Top = 48, ;
	Width = 624, ;
	Height = 420, ;
	Anchor = 1 + 2 + 4 + 8, ;
	Visible = .T., ;
	ColumnCount = -1, ;
	RecordSource = "curSales"
	
ADD OBJECT cmdSort AS CommandButton WITH ;
	Left = 12, ;
	Top = 12, ;
	Height = 24, ;
	Anchor = 1 + 2
	
	PROCEDURE cmdSort.Click()
		SELECT * FROM curSales ;
			ORDER BY 1, 2 ;
			INTO CURSOR curSalesOrderBy_1_2
		
		WITH ThisForm.grdSales
			.ColumnCount = -1
			.RecordSource = "curSalesOrderBy_1_2"
		ENDWITH 
		
		GOTO TOP 
		ThisForm.Refresh()
		
	ENDPROC 

ADD OBJECT cmdUndo AS CommandButton WITH ;
	Left = 120, ;
	Top = 12, ;
	Height = 24, ;
	Anchor = 1 + 2
	
	PROCEDURE cmdUndo.Click()
		WITH ThisForm.grdSales
			.ColumnCount = -1
			.RecordSource = "curSales"
		ENDWITH 

		GOTO TOP 
		ThisForm.Refresh()

	ENDPROC 

ADD OBJECT cmdAdd AS CommandButton WITH ;
	Left = 240, ;
	Top = 12, ;
	Height = 24, ;
	Anchor = 1 + 2
	
	PROCEDURE cmdAdd.Click()
		INSERT INTO curSales (C_CID, C_Descri ) VALUES (INT(RAND()*10) , INT(RAND()*10))
	
		WITH ThisForm.grdSales
			.ColumnCount = -1
			.RecordSource = "curSales"
			.Refresh()
		ENDWITH 

		GOTO TOP 
		ThisForm.Refresh()

	ENDPROC 
	
PROCEDURE Destroy()
	CLOSE ALL
	Clear Events

ENDPROC

PROCEDURE Load

	CREATE CURSOR curSales (C_CID I, C_Descri I)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (2, 3)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (1, 2)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (1, 1)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (2, 2)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (4, 3)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (4, 2)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (2, 1)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (1, 3)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (3, 1)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (3, 2)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (3, 3)
	INSERT INTO curSales (C_CID, C_Descri ) VALUES (4, 1)

	LOCATE 
	
ENDPROC

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

hth

MarK

 

[NasibKalsi]

As per Olaf, change your statement to

* not required
INDEX ON str(WITNO)+ str(WSTATNO1) TAG WITNO

nasib

 

[Dan Freeman]

As Olaf says, you don't want to index on a sum.

INDEX ON PADL(witno,length) + PADL(wstatno1,length)

I leave it to you to figure out the proper length needed.

 

[Olaf Doschke]

The two indexes given by Mike and Dan would work, if the fields are numeric.

But again: You query from tables which presumably are indexed for query performance optimisation, but an index on a query result seldom is useful, unless you want to allow users to sort in different ways, but the original order is already given by the ORDER BY in your query. Simply use the result table as is. I'd also recommend using cursors, query INTO CURSOR, instead of INTO TABLE, unless you want your result more permanent than in the current session of the user. Actually you would want data queried from your central database temporarily only in most cases, it's the central database, that should be the only database having all current and important data you want persisted. All other local table may only speed up things by not needing to query central data, but will sooner or later outdate anyway.

Indexes created on query results can be used to sort the retrieved data in different ways or to optimize further queries done on this queried data, so in case you stage your overall queries with several loacal tables or cursor, that can be fine. But you don't need an index on an already ordered result just for display, or don't you trust the ORDER BY clause?

Bye, Olaf.

 

[Dan Freeman]

Actually, Padl() will work with any data type you throw at it. It's a lot like Transform() that way, the difference being a consistent length.

And there is nothing wrong with indexing a query result for display purposes, although I do agree about INTO CURSOR vs. INTO TABLE.

 

[Olaf Doschke]

I'm fine with that dan.

>Indexes created on query results can be used to sort the retrieved data in different ways
That can be for the purpose of easily switching sort order in header clicks by SET ORDER TO TAG <indextagname>

But in case you need to sort the data in several ways you would a) need more than one index, b) could skip the ORDER BY in sql, as you can sort the data by the index created afterwards.

There also is no way around indexing views after having (re)queried them, as they are query results, too.

I was just referring to the case you just want to display the sort order as given by the ORDER BY. Without the interactive ability to reorder data. That needs no index on the already ordered result.

Bye, Olaf.

 

[Steve-vfp9user]

Thank you for the posts.

I will assess them this evening and post back as soon as able.

Steve

 

[Steve-vfp9user]

Apologies for the delay in replying:

Olaf:

I kept the same statement and yes, it does give the desired order however when a record is added, the order is not maintained.

Example:

1, 1
1, 2
1, 3
1, 4
2, 1

If add witness 1 statement 5 I get:

1, 1
1, 2
1, 3
1, 4
2, 1
1, 5


danfreeman

INDEX ON PADL(witno,3) + PADL(wstatno1,3) TAG WITNO

That gives the same result as above as does nasib's

INDEX ON str(WITNO)+ str(WSTATNO1) TAG WITNO

I'll keep trying and thanks for posting guys.


Steve

 

[Steve-vfp9user]

Well this may not be the most efficient way but everything works after updating and viewing records the only problem was when adding a new record so what I did was after each time a new record was added, I issued the below code and the records are now showing the desired result.

USE tempfile+'.dbf' EXCLUSIVE
INDEX ON STR(WITNO)+STR(WSTATNO1) TAG WITNO

I'l go with that for now and see how I get on.

Thank you

Steve

 

[Mike Lewis]

May I jump in?

You said " when a record is added, the order is not maintained." That is not what I would expect.

How are you determining the order of the records? If you are simply viewing the records in a Browse window, be aware that the Browse doesn't immediately sort the records to the new order as soon as a new record is inserted. The new record appears at the bottom of the Browse.

If that's what you are seeing, you need to set the Append Mode. To do that, open the View menu (when the Browse is active) and tick Append Mode. The new record should then appear in its rightful place.

You don't need to anything to your program code to make this work. If my diagnosis is correct, the record was always being inserted correctly; the problem was that the Browse wasn't showing that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You say "That gives the same result as above" to both indexes
INDEX ON PADL(witno,3) + PADL(wstatno1,3) TAG WITNO
and
INDEX ON str(WITNO)+ str(WSTATNO1) TAG WITNO

Are you meaning as above in your first post, the problematic order?
Now it would be interesting to see what field types witno and wstatio1 are to see why the data doesn't sort the way you want it. If you have text fields and some digits are entered in the middle, sone on the right and some on the left a " 2 " can easily sort before a "1 ", as that's ordering by the texts, not the numeric values. If fields are numeric, both indexes must sort primary in witno order and secondary in wstatno1 order, there is no way around that, but you may see your last record not in order as long as new data is in buffers and not yet updating the index.

Without an index the new record always adds as last, because that's where new records go. Too obvious maybe. But that makes me wonder: If you add data to the generated table, how does it get to the original table? Do you do that as save routine, saving new records in the central table?

An SQL query always is resulting in a snapshot of data into a result. As you query INTO TABLE, that's of course just a starting point for further added data, but in a normal database application you will keep all similar data in a central table shared by all users, and not make copies that get their own lifecycles.

Bye, Olaf.

 

[Steve-vfp9user]

Hello Mike and Olaf

Please excuse my explanations I am still new to this but learning fast!

Firstly I grab the required records from the table using the SQL statement
These are ordered appropriately into a sys(3) file
These records are then displayed on a grid on a form
I have command buttons for Add, Delete, Update etc

It's a bit difficult to explain on here but when I was adding records it lost the sequence I required and this was the only time.

When the records have been updated or deleted etc they are then appended back to the table I got them from after first deleting the originals.

This was something I picked up for multi user usage if that makes sense!

However as I mentioned earlier, I have now resolved the problem so thank you

In answer to Olaf's question both fields are numeric (3)

Steve

 

[NasibKalsi]

hi steve:

you could remove the 'order by' clause to relieve the cpu. since you are indexing it anyway and then displaying the results. in the add.valid() or on the bottom of the 'add' code issue thisform.refresh (refresh the grid only if you want). as mike mentioned, the data is sorted but the grid (view) is not updated. it will not update unless you pageup, pagedown, arrowup, arrowdown, etc., the item count has to be higher than the grid row count. refresh will force the grid to pickup the newly added data.


nasib


ps: "The two indexes given by Mike and Dan would work, if the fields are numeric." thank you Olaf for confusing me with mike.

 

[Dan Freeman]

I'm a little confused about issuing the index command again.

Browse does behave the way described here, but you should NEVER have to index again just because you've added a record. Are you opening the table again after adding a record? If you do, it'll be without its index order unless you SET ORDER.

 

[NasibKalsi]

the question that Olaf raised, if i understood correctly:

1. you have myfile.dbf
2. you created tmpfile from myfile
3. you displayed the result via grid.
4. yo added the new record to tmpfile.

how do you update the myfile.dbf ?
do you delete all records in myfile.dbf and then add all records of tmpfile.dbf ?

nasib

 

[Mike Lewis]

the data is sorted but the grid (view) is not updated. it will not update unless you pageup, pagedown, arrowup, arrowdown, etc.,

I think it's enough just to put focus on the grid.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Hello

I am a little confused with some of the responses now so as the issue has been "fixed" so to speak, I am going to leave it for now.

In answer to Nasib's question:

how do you update the myfile.dbf ?
do you delete all records in myfile.dbf and then add all records of tmpfile.dbf ?

Once the records are either updated or new ones added yes. The original records are deleted and the ones in the sys(3) file are appended back to myfile.dbf

USE myfile
DELETE FOR MASTLINK=mmastlink
APPEND FROM tempfile+'.dbf'

Thanks again all

Steve

 

[NasibKalsi]

I think it's enough just to put focus on the grid.

mike: you are absolutely right.


nasib