Order By not working as Expected

[stanlyn]

Hi,

I am expecting 'All Clerks' to be at the top of the list, instead of at the bottom. It appears that records added after the select query is not under of the control of the "order by" clause.

Select ClerkName, SystemID 	;
	FROM 'rv_Clerk' ;
	ORDER By ClerkName ;
	INTO Cursor 'curClerk' ;
	readwrite

Select 'curClerk'
Insert Into 'curClerk' (ClerkName, SystemID) Values ('All Clerks', '*****')

So, how is this handled? VFPA

Thanks,
Stanley

 

[foxmuldr3]

One simple solution:

SELECT ClerkName, SystemID ;
	FROM 'rv_Clerk' ;
	INTO CURSOR 'curClerk' READWRITE

INSERT 'curClerk' (ClerkName, SystemID) VALUES('All Clerks', '*****')

SELECT * ;
    FROM curClerk ;
    ORDER BY ClerkName ;
    INTO CURSOR curClerk READWRITE

* Right now, the curClerk is in sorted order without an index

Another:

SELECT ClerkName, SystemID ;
	FROM 'rv_Clerk' ;
	INTO CURSOR 'curClerk' READWRITE

INSERT 'curClerk' (ClerkName, SystemID) VALUES('All Clerks', '*****')

INDEX ON ClerkName TAG main
* Right now the curClerk is in natural order with an index placing it in sorted order

You'll want to add the index AFTER you create the cursor, otherwise it will insert every record one by one into the index as it goes, which will take longer.

My personal preference is the former because it's:
1. Clear in code what it's doing
2. It shows the operation step-by-step making it easier to debug
3. Doesn't require an index

Your mileage may vary.

--
Rick C. Hodgin

 

[Gerrit Broekhuis]

Hi Rick.

I agree with you, but when you’re using this cursor with a grid, you can use your second option to sort ascending or descending when clicking the grid column’s header. Just because the index is already there.

In that case I prefer the second solution. If you use your first solution you will still have to create an index in the header to allow a change in the grid’s sorting.

Regards, Gerrit

 

[stanlyn]

Hi Mark,

Thanks for the sample code as it will help.

While studying your demo code I ran across a "locate" and wondering why it is placed there? I've always used it with parameters. Here is a screenshot.

Thanks,
Stanley

 

[stanlyn]

Hi Mark,

I found my M$ "Microsoft Visual Foxpro 6.0 Programmers Guide" book that has so much about remote views and the best reference yet. It survived... It was part of my flooded book collection that was laid out to dry after literally prying them out of the shelf. The books were stuffed into the shelf and turned out to be a good thing as the tightness is what saved them.

An 8 second video shows my grandson prying them out of the bookcase at stanlyndotcom slash public slash 20220920_books.mp4

I'll have more questions as I work through your materials. Thanks for sending them...

Thanks,
Stanley

 

[stanlyn]

Mark,

Help says... The default scope for LOCATE is ALL records.

If this is the reason, isn't all records already in scope without the LOCATE?

Thanks, Stanley

 

[stanlyn]

Hi,

Here is what I settled on. It insures that "All Clerks" will hold the #1 position, regardless what comes down from the query, something that Chris spoke of when he mentioned "Adair County" would come before "All Clerks".

Stanley

Open Database '.\data\eFir'

Select ClerkName, SystemID 	;
	FROM 'rv_Clerk' ;
	ORDER By ClerkName ;
	INTO Cursor 'curTmpClerk'

Create Cursor 'curClerk' (ClerkName C(20), SystemID C(7))
Insert Into 'curClerk' (ClerkName, SystemID) Values ('All Clerks', 'US999')

Select 'curTmpClerk'
Do While !Eof('curTmpClerk')
	Insert Into 'curClerk' (ClerkName, SystemID) Values ;
		(curTmpClerk.ClerkName,  curTmpClerk.SystemID)
	Skip 1
Enddo

Select 'curClerk'
GO top
Use In Select('curTmpClerk')

 

[stanlyn]

Hi,

The code the view designer shows is pseudocode. It doesn't work.

True, but a 1-liner fixes that. Here is what I did that makes it easy by copying the builder's sql code straight to a .prg or method and adding a small one line. Then leave as is, or edit as it contains the whole table's definition as a starting point/template.

Before I invest a lot of time with this, is there a gotcha that I don't see yet?

This working code is in the combobox's interactive change method.

Thanks,
Stanley

With Thisform As Form
	Use In Select('rv_DocType')

	If Alltrim(This.DisplayValue) != 'All Clerks'
		lcWhere = "where DocType.SystemID = '" + curClerk.SystemID + "'"
		lcOrderBy = "ORDER BY Doctype.WebType"

		TEXT TO lcClerkSQL TEXTMERGE NOSHOW PRETEXT 15
			SELECT Doctype.Pk, Doctype.SystemId, Doctype.Statute,
			Doctype.Type, Doctype.WebType, Doctype.Description,
			Doctype.SqlTimeStamp
			FROM dbo.DocType DocType
			<<lcWhere>>
			<<lcOrderBy>>
		ENDTEXT
	Else		&& All Clerks was selected
		lcWhere = "where 1=1"
		lcOrderBy = "ORDER BY Doctype.WebType"

		TEXT TO lcClerkSQL TEXTMERGE NOSHOW PRETEXT 15
			SELECT Doctype.Pk, Doctype.SystemId, Doctype.Statute,
			Doctype.Type, Doctype.WebType, Doctype.Description,
			Doctype.SqlTimeStamp
			FROM dbo.DocType Doctype
			<<lcWhere>>
			<<lcOrderBy>>
		ENDTEXT
	Endif

	Set Safety Off
	Create Sql View rv_DocType  ;
		CONNECTION eFileIt ;
		REMOTE ;
		as &lcClerkSQL
	Set Safety On

	Requery('RV_DocType')

	Go Top

	ThisView = 'rv_DocType'

	DBSetProp(ThisView,"View","SendUpdates",.T.)
	DBSetProp(ThisView,"View","BatchUpdateCount",1)
	DBSetProp(ThisView,"View","CompareMemo",.T.)
	DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
	DBSetProp(ThisView,"View","FetchMemo",.T.)
	DBSetProp(ThisView,"View","FetchSize",100)
	DBSetProp(ThisView,"View","MaxRecords",-1)
	DBSetProp(ThisView,"View","Prepared",.F.)
	DBSetProp(ThisView,"View","ShareConnection",.T.)
	DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
	DBSetProp(ThisView,"View","UpdateType",1)
	DBSetProp(ThisView,"View","UseMemoSize",255)
	DBSetProp(ThisView,"View","Tables","dbo.DocType")
	DBSetProp(ThisView,"View","WhereType",3)

	DBSetProp(ThisView+".pk","Field","DataType","I")
	DBSetProp(ThisView+".pk","Field","UpdateName","dbo.DocType.Pk")
	DBSetProp(ThisView+".pk","Field","KeyField",.T.)
	DBSetProp(ThisView+".pk","Field","Updatable",.F.)

	DBSetProp(ThisView+".systemid","Field","DataType","C(7)")
	DBSetProp(ThisView+".systemid","Field","UpdateName","dbo.DocType.SystemId")
	DBSetProp(ThisView+".systemid","Field","KeyField",.F.)
	DBSetProp(ThisView+".systemid","Field","Updatable",.T.)

	DBSetProp(ThisView+".statute","Field","DataType","C(254)")
	DBSetProp(ThisView+".statute","Field","UpdateName","dbo.DocType.Statute")
	DBSetProp(ThisView+".statute","Field","KeyField",.F.)
	DBSetProp(ThisView+".statute","Field","Updatable",.T.)

	DBSetProp(ThisView+".type","Field","DataType","C(40)")
	DBSetProp(ThisView+".type","Field","UpdateName","dbo.DocType.Type")
	DBSetProp(ThisView+".type","Field","KeyField",.F.)
	DBSetProp(ThisView+".type","Field","Updatable",.T.)

	.Refresh
Endwith

 

[mjcmkrsr]

Hi Stan,

Before I invest a lot of time with this, is there a gotcha that I don't see yet?

Right now I don't see any. It should work - but you'll have to test.

While studying your demo code I ran across a "locate" and wondering why it is placed there?

If you use the LOCATE command without the FOR expression, Visual FoxPro positions the record pointer at the first logical record. This is faster than using GO TOP when a filter is in use or when DELETED is ON.

hth

MarK

 

[mjcmkrsr]

...

and the updated demo code, with MULTILOCKS = ON, buffering of the view set to 5, a procedure to step through the view to update and some minor tweaks.

PUBLIC go_Form 

[highlight #73D216]SET Multilocks On
[/highlight]
go_Form = CreateObject("frmForm")
go_Form.Visible = .T.
go_Form.Show

READ Events

CLOSE ALL
CLEAR ALL

*****

DEFINE CLASS frmForm As Form

	gcValue = " "
	
	Width = 702
	Height = 720
	MinWidth = This.Width
	MaxWidth = This.Width
	MinHeight = This.Height
	AutoCenter = .T.
	Themes = .F.
	
	ADD OBJECT lblTable as Label WITH ;
		Left = 18, Top = 15, Caption = "Data in Table", FontItalic = .T., FontBold  = .T., FontSize = 12
		
	ADD OBJECT lblName as Label WITH ;
		Left = 216, Top = 18, Caption = "Enter name :"
		
	ADD OBJECT txtName as TextBox WITH ;
		Left = 300, Top = 15, Value = ""
		
		PROCEDURE txtName.LostFocus()
			ThisForm.gcValue = ALLTRIM(This.Value)
		
		ENDPROC 
		
	ADD OBJECT cmdRequery as CommandButton WITH ;
		Left = 420, Top = 15, Height = 24, Caption = "Requery view", BackColor = RGB(0, 210, 0)

		PROCEDURE cmdRequery.Click()
			
			ThisForm.cmdSave.Click()
			
			IF EMPTY(ThisForm.gcValue)
				= MESSAGEBOX("Please enter name", 48, "Names")

			ELSE
				REQUERY("vueNames")
			
			ENDIF
			
			ThisForm.Refresh()

		ENDPROC 

	ADD OBJECT cmdSave as CommandButton WITH ;
		Left = 540, Top = 15, Height = 24, Caption = "Update table", ForeColor = RGB(255, 255, 255), BackColor = RGB(0, 215, 0)
		
[highlight #73D216]		PROCEDURE cmdSave.Click()
			LOCAL liNextRecord, lcAlias
			
			lcAlias = ALIAS()
			
			SELECT vueNames
			
			liNextRecord = GETNEXTMODIFIED(0)
			DO WHILE liNextRecord > 0
				GOTO liNextRecord
				= TABLEUPDATE(0, .T., "vueNames")
				liNextRecord = GETNEXTMODIFIED(liNextRecord)
			ENDDO 
				
			SELECT (lcAlias)

		ENDPROC 
[/highlight]
	ADD OBJECT lblView as Label WITH ;
		Left = 18, Top = 360, Caption = "Data in View", FontItalic = .T., FontBold  = .T., Anchor = 90, FontSize = 12

	Add Object grdATM as Grid WITH ;
		ReadOnly = .T., ;
		Top = 48, ;
		Left = 18, ;
		Height = 300, ;
		Width = ThisForm.Width - 36, ;
		RowHeight = 24, ;
		AllowRowSizing = .F., ;
		HeaderHeight = 21, ;
		AllowHeaderSizing = .F., ;
		DeleteMark = .F., ;
		Anchor = 75, ;
		Visible = .T., ;
		ColumnCount = -1, ;
		RecordSource = "tblNames"
    	
		PROCEDURE grdATM.Init()
			WITH This 
				.Column1.Header1.Caption = "ID"
				.Column1.Header1.FontBold = .T.
				.Column1.Width = 60

				.Column2.Header1.Caption = "Name"
				.Column2.Header1.FontBold = .T.
				.Column2.Width = 120

				.Column3.Header1.Caption = "Street"
				.Column3.Header1.FontBold = .T.
				.Column3.Width = 150

				.Column4.Header1.Caption = "City"
				.Column4.Header1.FontBold = .T.
				.Column4.Width = 150

				.Column5.Header1.Caption = "ZipCode"
				.Column5.Header1.FontBold = .T.
				.Column5.Width = 150

			ENDWITH 
		ENDPROC
		
	Add Object grdATMVUE as Grid with;
		Top = 396, ;
		Left = 18, ;
		Height = 300, ;
		Width = ThisForm.Width - 36, ;
		RowHeight = 24, ;
		AllowRowSizing = .F., ;
		HeaderHeight = 21, ;
		AllowHeaderSizing = .F., ;
		DeleteMark = .F., ;
		Anchor = 30, ;
		Visible = .T., ;
		ColumnCount = -1, ;
		RecordSource = "vueNames"
		
		PROCEDURE grdATMVUE.Init()
			WITH This 
				.Column1.Header1.Caption = "ID"
				.Column1.Header1.FontBold = .T.
				.Column1.Width = 60
				.Column1.ReadOnly = .T.

				.Column2.Header1.Caption = "Name"
				.Column2.Header1.FontBold = .T.
				.Column2.Width = 120

				.Column3.Header1.Caption = "Street"
				.Column3.Header1.FontBold = .T.
				.Column3.Width = 150

				.Column4.Header1.Caption = "City"
				.Column4.Header1.FontBold = .T.
				.Column4.Width = 150

				.Column5.Header1.Caption = "ZipCode"
				.Column5.Header1.FontBold = .T.
				.Column5.Width = 150

			ENDWITH 
		ENDPROC

	PROCEDURE Load()	

		IF FILE("ATMOS.DBC")
			OPEN DATABASE ATMOS
			
		ELSE 
			CREATE DATABASE "ATMOS"
			
			CREATE TABLE "tblNames" (iAutoInc i AUTOINC NEXTVALUE 100, cName C(20), cStreet C(20), cCity C(20), cZipCode C(20))

				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Winston Smith","123 Anywhere St","Chicago","60601")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Andrea Lopez","235 N Ave","Lawrence","08648")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Sandra Johnson","2500 8th St","Los Angeles","90061")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Kate Wilson","987 M Blvd","Tarzana","91356")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Kate Smith","87 Santa M Blvd","Santa Barbara","91358")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Jim Monterrey","8715 Santa Monica","Santa Barbara","91358")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Winston Smith","123 Anywhere St","Eau Claire","20601")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Andrea Lopez","235 N Ave","Toronto","28648")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Sandra Johnson","2500 8th St","Los Alamos","20061")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Kate Wilson","987 M Blvd","Carlson","21356")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Kate Smith","87 Santa M Blvd","Santa Ines","21358")
				INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Jim Monterrey","8715 Santa Monica","Santa Ines","21358")
				
				LOCATE 
				

				CREATE SQL VIEW "VUENAMES" ; 
				   AS Select * from ATMOS!tblNames where AT(ThisForm.gcValue, cName) > 0 order by 2

				DBSetProp("VUENAMES", "View", "UpdateType", 1)
				DBSetProp("VUENAMES", "View", "WhereType", 3)
				DBSetProp("VUENAMES", "View", "FetchMemo", .T.)
				DBSetProp("VUENAMES", "View", "SendUpdates", .T.)
				DBSetProp("VUENAMES", "View", "UseMemoSize", 255)
				DBSetProp("VUENAMES", "View", "FetchSize", 100)
				DBSetProp("VUENAMES", "View", "MaxRecords", -1)
				DBSetProp("VUENAMES", "View", "Tables", "tblNames")
				DBSetProp("VUENAMES", "View", "Prepared", .F.)
				DBSetProp("VUENAMES", "View", "CompareMemo", .T.)
				DBSetProp("VUENAMES", "View", "FetchAsNeeded", .F.)
				DBSetProp("VUENAMES", "View", "Comment", "")
				DBSetProp("VUENAMES", "View", "BatchUpdateCount", 1)
				DBSetProp("VUENAMES", "View", "ShareConnection", .F.)
				DBSetProp("VUENAMES", "View", "AllowSimultaneousFetch", .F.)

				*!* Field Level Properties for VUENAMES
				DBSetProp("VUENAMES.iautoinc", "Field", "KeyField", .T.)
				DBSetProp("VUENAMES.iautoinc", "Field", "Updatable", .F.)
				DBSetProp("VUENAMES.iautoinc", "Field", "UpdateName", "tblNames.iAutoInc")
				DBSetProp("VUENAMES.iautoinc", "Field", "DataType", "I")
				* Props for the VUENAMES.cname field.
				DBSetProp("VUENAMES.cname", "Field", "KeyField", .F.)
				DBSetProp("VUENAMES.cname", "Field", "Updatable", .T.)
				DBSetProp("VUENAMES.cname", "Field", "UpdateName", "tblNames.cName")
				DBSetProp("VUENAMES.cname", "Field", "DataType", "C(20)")
				* Props for the VUENAMES.cstreet field.
				DBSetProp("VUENAMES.cstreet", "Field", "KeyField", .F.)
				DBSetProp("VUENAMES.cstreet", "Field", "Updatable", .T.)
				DBSetProp("VUENAMES.cstreet", "Field", "UpdateName", "tblNames.cStreet")
				DBSetProp("VUENAMES.cstreet", "Field", "DataType", "C(20)")
				* Props for the VUENAMES.ccity field.
				DBSetProp("VUENAMES.ccity", "Field", "KeyField", .F.)
				DBSetProp("VUENAMES.ccity", "Field", "Updatable", .T.)
				DBSetProp("VUENAMES.ccity", "Field", "UpdateName", "tblNames.cCity")
				DBSetProp("VUENAMES.ccity", "Field", "DataType", "C(20)")
				* Props for the VUENAMES.czipcode field.
				DBSetProp("VUENAMES.czipcode", "Field", "KeyField", .F.)
				DBSetProp("VUENAMES.czipcode", "Field", "Updatable", .T.)
				DBSetProp("VUENAMES.czipcode", "Field", "UpdateName", "tblNames.cZipCode")
				DBSetProp("VUENAMES.czipcode", "Field", "DataType", "C(20)")

		ENDIF

		IF USED("tblNames")
			SELECT tblNames
		ELSE 
			USE tblNames IN 0
		ENDIF 

		IF USED("vueNames")
			SELECT vueNames
		ELSE 
			USE vueNames IN 0
		ENDIF 

[highlight #73D216]		CURSORSETPROP("Buffering", 5, "VUENAMES")[/highlight]

	ENDPROC 

	PROCEDURE Destroy()
		= TABLEREVERT(.T., "VUENAMES")
		WAIT WINDOW + "... reverting data changes ..." TIMEOUT 1
		CLEAR Events
		ThisForm.Release
	ENDPROC
ENDDEFINE

*****

hth

MarK

 

[stanlyn]

Thanks Mark,

I can certainly use the GetNextModified() function as this is first time I've seen it (as least I don't remember seeing it and definitely have never used it and all probably because its related to remote data, something I didn't do back then.

The more we discuss things here the more I need to stop and re-read the Hacker's guide (the big book) and others, as its been 15+ years since reviewing.) Wonder what else I might learn from a review. Looks easy enough to implement. Looks like tomorrow is for reading these old books.

I follow your logic in the sample code with no issue and special thanks for the table buffering routine inclusion. I also see you are doing most of the setup in the form's load event, which I also do.

Question, Do you also add the views to the DE and if so, how do they differ from what is in your load event? What I have been doing is creating a view in the designer with all fields and all updateable except the pk, then add that to the DE for filling out all the form object's controlsources. I also set its cursor to NoDataOnLoad. Would it be acceptable to clear the DE on execution, or later once the form is finished and working? Wondering about conflicts between the load and DE.

Thanks again,
Stanley

 

[mjcmkrsr]

Hi Stan,

Question, Do you also add the views to the DE and if so, how do they differ from what is in your load event?

Yes I do, but then I define them in the project manager with the view designer.

hth

MarK

 

[mjcmkrsr]

Hi Stan,

The more we discuss things here the more I need to stop and re-read the Hacker's guide

Do you mean HG 7?

Don't forget "What's New in Nine" (Visual Foxpro's Latest Hits) by T. Granor, D. Hennig, R. Schummer, T. Feltman & J. Slater - Hentzenwerke 2005 - ISBN 1-930919-64-6 - a must have.

hth

MarK

 

[stanlyn]

Hi Mark,

I have all the Hentzenwerke books as they were not flooded and in my day-to-day collection. "What's New in Nine" is in that collection. I have looked extensively in those books and have found very little about view implementation.

I just found the best and probably the most basic in the old VFP6 Programming guide from M$. Chapter 8 "Creating Views" starting on page 183 and continues to page 218, and all about views (35 pages). Also small print.

Just found another good reference "1001 Things You Wanted to Know about VFP" by Marcia Akins, Andy Kramek, and Rick Schummer (15 pages). And "Client Server Apps for VFP", another 14 pages by Chuck, Gary, Mike & Leslie.

Lot of reading to do.

Thanks,
Stanley

 

[stanlyn]

HG 7?

Yes, I have both v3 and v7 of the Hackers Guide.

Stanley

 

[stanlyn]

Mark,

Yes I do, but then I define them in the project manager with the view designer.

Looks like I'm doing it the other way around, View Designer first, then load.

Are both versions of the view identical? The one I create with the Designer is a complete single table view with all fields selected as updateable. I use that as a starting point for what is in the load event.

Is there ever a conflict? Should the DE's version be unloaded at runtime?

Thanks,
Stanley

 

[mjcmkrsr]

Hi Stan,

HG 7 = Hacker's Guide to Visual Foxpro 7

... VFP6 Programming guide from M$. Chapter 8 "Creating Views" starting on page 183 and continues to page 218, and all about views (35 pages)

Well, if you have this book, then please read on page 195 ff "Creating a Parameterized View".

hth

MarK

 

[mjcmkrsr]

Hi Stan,

Are both versions of the view identical? The one I create with the Designer is a complete single table view with all fields selected as updatable

ad 1) They should be
ad 2) You have to define in the view designer which field(s) is/are key field(s) and which should be updatable

hth

MarK