Grid Search and Data Filter in Grids

[TSWA]

Hello, I am looking a routine or steps to search data into GRIDS, I will explain:
Question 1
I have one textbox and Grid into the Form, the Data of the Grid is Indexed by Name(TAG ORDER). I want to locate info into the grid, for example if I type P, the grid will display Names begining with "P", if I type PA, the grid will display Names begining with "PA" until I found the name and Select the Name that I want.

I am working on Network(FE 100MB with CISCO Switches) and is not faster like Hard Disk. The SQL - SELECT and LOCATE command is to slow when I need to search over 150000 records, If I use SEEK or Find is faster.

In one apps written in DOS(CLIPPER) this issue was developed and working fine. Is possible do that in VFP 6.0.

Question 2
I want to Know about Filter Data in Grids like BROWSE KEY(Indexed keys). If I use SET FILTER TO over 35000 records(and exist Deleted Items into the Table, the tables maintenance is about every 15 or 30 days) is to slowly to move between records, but If I use BROWSE KEY the data is Display faster because it works over the Index and not over the Data. Is possible do that simulate BROWSE KEY on GRIDS in VFP 6.0.

I always have the "RUSHMORE TECH" activated but is to slowly for a lot of Records.

The aplication users is located over 45 miles from the server, and the communication is not very very faster.

Thanks

 

[DSummZZZ]

Question 1: See:
How to make an incremental search in Grid
faq184-1214

Question 2: Consider using views as in this FAQ:
How to avoid grid reconstruction
faq184-1813
Dave S.

 

[IRABYY]

TSWA (IS/IT--Manageme)
Hello, I am looking a routine or steps to search data into GRIDS, I will explain:
Question 1
I have one textbox and Grid into the Form, the Data of the Grid is Indexed by Name(TAG ORDER). I want to locate info into the grid, for example if I type P, the grid will display Names begining with "P", if I type PA, the grid will display Names begining with "PA" until I found the name and Select the Name that I want.

Yes, it can be done. Consider KeyDown, KeyUp or KeyPress event procedure having something similar to

SEEK ALLTRIM(THIS.Text) IN <YourTableAlias> ORDER NameTag
THISFORM.grdName.Refresh

I have used this approach myself number of times, and it always worked fine.
(Side remark: "Name" is reserved word, I hope it's not the name of the field in question.)

I have no ready answer to your second question, colleague. It needs some investigation, unless someone else on this forum have already solved this or similar issue. In the later case, I would be very interested to know that solution also.

Regards,

Ilya

 

[ramani]

Hi

Take this as a sample and try..

*************************************************
CREATE CURSOR test (cName C(20), email c(20))
INSERT INTO test (cName, email) ;
VALUES ("experts","experts@Tek_tips.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("Dummy","dummy@dummy.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("ClayHead","clay@dummy.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("WoodBrain","wood@Tek_pits.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("IronHead","iron@dummy.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("BigHead","big@dummy.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("ShowMan","showbiz@dummy.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("NutHead","nuts@dummy.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("Ramani","ramani@Tek_tips.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("Mike","mike@Tek_tips.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("Rick","Rick@Tek_tips.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("Chris","chris@Tek_tips.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("Dsumzz","dsumzz@Tek_tips.moc&quot
INSERT INTO test (cName, email) ;
VALUES ("mySelf","me@Tek_tips.moc&quot
INDEX ON cName TAG cName
SET ORDER TO 1
LOCATE
** All above is code for a table for our example
****************************************************
myFields = "cName,email"
DO gsFindForm WITH myFields
=MESSAGEBOX("You have selected :"+ ;
ALLTRIM(cName)+" : "+email, 0+16, "Eureka!&quot
RETURN
**************************************************
PROCEDURE gsFindForm
PARAMETERS tcFields

IF PARAMETERS() < 1
RETURN .f.
ENDIF

PUBLIC oform1

oform1=NEWOBJECT(&quot;gsFindForm&quot;,'','',tcFields)
oform1.Show

RELEASE oForm1
RETURN
**************************************************
*-- Form: gsFindForm
*-- ParentClass: form
*-- BaseClass: form
*
DEFINE CLASS gsFindForm AS form

AutoCenter = .t.
Height = 242
Width = 420
DoCreate = .T.
Caption = &quot;FindForm&quot;
Name = &quot;gsFindForm&quot;
ShowTips = .t.
WindowType = 1
inrecno = 0
cFilter = &quot;&quot;
cFilterOld = &quot;&quot;

ADD OBJECT label1 AS Label WITH ;
Left = 12, ;
Top = 196, ;
Width = 78, ;
Caption = &quot;Search For&quot;, ;
Name = &quot;Label1&quot;

ADD OBJECT text1 AS textbox WITH ;
Height = 23, ;
Left = 84, ;
Top = 192, ;
Width = 400, ;
Name = &quot;Text1&quot;, ;
ToolTipText = &quot;Case sensitive.. &quot; + ;
&quot;Start with * and learn how it searches&quot;

ADD OBJECT cmdfirst AS commandbutton WITH ;
Top = 216, ;
Left = 0, ;
Height = 27, ;
Width = 84, ;
Caption = &quot;\<First&quot;, ;
Name = &quot;cmdFirst&quot;

ADD OBJECT cmdprevious AS commandbutton WITH ;
Top = 216, ;
Left = 84, ;
Height = 27, ;
Width = 84, ;
Caption = &quot;\<Previous&quot;, ;
Name = &quot;cmdPrevious&quot;

ADD OBJECT cmdnext AS commandbutton WITH ;
Top = 216, ;
Left = 168, ;
Height = 27, ;
Width = 84, ;
Caption = &quot;\<Next&quot;, ;
Name = &quot;cmdNext&quot;

ADD OBJECT cmdlast AS commandbutton WITH ;
Top = 216, ;
Left = 252, ;
Height = 27, ;
Width = 84, ;
Caption = &quot;\<Last&quot;, ;
Name = &quot;cmdLast&quot;

ADD OBJECT cmdexit AS commandbutton WITH ;
Top = 216, ;
Left = 336, ;
Height = 27, ;
Width = 84, ;
Cancel = .T., ;
Caption = &quot;E\<xit&quot;, ;
Name = &quot;cmdExit&quot;

ADD OBJECT grid1 AS grid WITH ;
Height = 192, ;
Left = 0, ;
Top = 0, ;
Width = 420, ;
Name = &quot;Grid1&quot;
DeleteMark = .F.
HighlightRow = .T.
ReadOnly = .T.
ForeColor = RGB(0,0,0)
BackColor = RGB(255,255,192)
GridLineColor = RGB(0,0,128)

PROCEDURE Init
PARAMETERS tcFields
IF PARAMETERS() < 1
RETURN .f.
ENDIF
LOCAL nCount
nCount = ALINES(laFields,tcFields,&quot;,&quot
WITH ThisForm.Grid1
.ColumnCount = nCount
FOR I=1 TO nCount
.Columns(I).ControlSource = laFields(i)
ENDFOR
.SetFocus()
ENDWITH
ENDPROC

PROCEDURE cmdfirst.Click
GO TOP
ThisForm.Grid1.SetFocus()
ThisForm.Refresh()
ENDPROC

PROCEDURE cmdprevious.Click
IF NOT BOF()
SKIP -1
ENDIF
ThisForm.Grid1.SetFocus()
ThisForm.Refresh()
ENDPROC

PROCEDURE cmdnext.Click
IF NOT EOF()
SKIP
ENDIF
ThisForm.Grid1.SetFocus()
ThisForm.Refresh()
ENDPROC

PROCEDURE cmdlast.Click
GO BOTTOM
ThisForm.Grid1.SetFocus()
ThisForm.Refresh()
ENDPROC

PROCEDURE cmdexit.Click
ThisForm.Release()
ENDPROC

PROCEDURE Text1.INTERACTIVECHANGE
LOCAL cFilterOld, cFilterIn
cFilterOld = ThisForm.cFilterOld
cFilterIn = KEY()
SET FILTER TO

IF LEFT(This.Value,1) = &quot;*&quot;
IF LEN(ALLTRIM(This.Value)) > 1
ThisForm.cFilter = ;
RIGHT(ALLTRIM(This.Value), ;
LEN(ALLTRIM(This.Value))-1)
IF EMPTY(cFilterOld)
SET FILTER TO ThisForm.cFilter $ &cFilterIn
ELSE
SET FILTER TO &cFilterOld .AND. ;
ThisForm.cFilter $ &cFilterIn
ENDIF
LOCATE
ELSE
IF !EMPTY(cFilterOld)
SET FILTER TO &cFilterOld
ENDIF
ENDIF
ELSE
IF !EMPTY(cFilterOld)
SET FILTER TO &cFilterOld
ENDIF
SEEK ALLT(This.Value)
ENDIF
ThisForm.Grid1.SetFocus
ThisForm.Text1.SetFocus
ThisForm.Refresh()
ENDPROC

PROCEDURE Grid1.AfterRowColChange
LPARAMETERS nColIndex
ThisForm.inRecNo = IIF(EOF() OR BOF(),0,RECNO())
This.Columns(This.ActiveColumn).Text1.BackColor = ;
RGB(255,0,0)
This.Columns(This.ActiveColumn).Text1.DisabledBackColor = ;
RGB(32,224,224)
This.Columns(This.ActiveColumn).Text1.DisabledForeColor = RGB(0,0,0)
This.Refresh()
ThisForm.Text1.SetFocus()
RETURN .T.
ENDPROC

PROCEDURE Grid1.Init
DODEFAULT()
WITH THIS
.SetAll(&quot;BackColor&quot;, RGB(255,192,192),&quot;Header&quot
.SetAll(&quot;Alignment&quot;, 2, &quot;Header&quot
.SetAll(&quot;DynamicBackColor&quot;, ;
&quot;IIF(recno(This.RecordSource)= ;
ThisForm.inRecno,RGB(32,224,224), ;
RGB(255,255,192))&quot;,&quot;COLUMN&quot
ENDWITH
ENDPROC

ENDDEFINE
*
*-- EndDefine: form1
**************************************************
Note how it filters when you type * as your first character of the search expression.

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[TSWA]

Hello, The first question was solved. Thanks DSummZZZ and Ramani(Dummzee sent me thread FAQ184-1214 with an example from Ramani). Ramani sent another solution with better performance and it work.

I am waiting for the response of the other Question.

Thanks,

Marlon

 

[DSummZZZ]

For the other question, use:
SET ORDER TO IndexKey

when you open the table, and also:
SET DELETED ON

Dave S.