This chapter is from the book
This chapter is from the book
This chapter is from the book
A Search Form Template
I didn't include any navigation in the FlatFileForm template because Next/Previous/First/Last record is an artifact of the xBASE world. We could provide those four features with a single line of code each (not counting checking for BOF()/EOF() conditions). But users typically don't care about the previous or next record. They want to see a candidate list, point to the one they want, and click. Besides, it's nearly impossible if you use SQL Server, and that's what we're going to do next.
I've included a class called EasySearch. It allows you to add a search form with up to 4 searchable fields (and it's easy to extend that to 8 or 10 if you need them), to let users filter records and pick one, and to return the key value, from a DBF, SQL, or a Web service, with absolutely no coding in the form itself. You simply fill in three or four properties, name the input fields that you put on the search form with names SEARCH1, SEARCH2, SEARCH3, and SEARCH4, set the form's tab order to Row order, and you're done. The code for this class is shown in Listing 3.6.
Listing 3.6 The EasySearch Class
DEFINE CLASS EasySearch AS modalform
tablename = ([]) && Table name to search
colwidths = ([]) && Comma-delimited list of the relative widths
colnames = ([]) && Comma-delimited list of field names
orderby = ([]) && "Order by" column name
colheadings = ([]) && Comma-delimited list if you don't want to use
* field names as headings
keyfield = ([]) && Name of key field value to return
PROCEDURE Init
WITH THISFORM
.Caption = [Search form - ] + .Name + [ (Main Table: ] ;
+ TRIM(.TableName)+[) Data access: ] + .Access
NumWords = GETWORDCOUNT(.ColNames,[,])
IF NumWords > 4
MESSAGEBOX( [This class only supports a maximum of 4 fields, sorry], ;
16, _VFP.Caption )
RETURN .F.
ENDIF
FOR I = 1 TO NumWords
.Field(I) = GETWORDNUM(.ColNames, I,[,])
.Heading(I) = GETWORDNUM(.ColHeadings,I,[,])
.ColWidth(I)= GETWORDNUM(.ColWidths, I,[,])
ENDFOR
WITH .Grid1
.ColumnCount = NumWords
.RecordSource = THISFORM.ViewName
.RecordSourceType = 1
GridWidth = 0
FOR I = 1 TO NumWords
.Columns(I).Header1.Caption = THISFORM.Heading (I)
GridWidth = GridWidth + VAL( THISFORM.ColWidth(I) )
FldName = THISFORM.ViewName + [.] + THISFORM.Field (I)
.Columns(I).ControlSource = FldName
ENDFOR
Multiplier = ( THIS.Width / GridWidth ) * .90 && "Fudge" factor
FOR I = 1 TO NumWords
.Columns(I).Width = VAL( THISFORM.ColWidth(I) ) * Multiplier
ENDFOR
.Refresh
ENDWITH
* Look for any controls named SEARCHn (n = 1, 2, ... )
FOR I = 1 TO .ControlCount
Ctrl = .Controls(I)
IF UPPER(Ctrl.Name) = [MYLABEL] && That is, if it starts with "MyLabel"
Sub = RIGHT(Ctrl.Name,1) && Determine the index
IF TYPE([THISFORM.Search]+Sub)=[O] && A search field #"Sub" exists
Ctrl.Visible = .T.
Ctrl.Enabled = .T.
Ctrl.Caption = .Heading(VAL(Sub))
.SearchFieldCount = MAX ( VAL(Sub), .SearchFieldCount )
ENDIF
ENDIF
ENDFOR
.SetAll ( "Enabled", .T. )
ENDWITH
ENDPROC
PROCEDURE Load
WITH THISFORM
IF EMPTY ( .TableName )
MESSAGEBOX( [Table name not entered], 16, _VFP.Caption )
RETURN .F.
ENDIF
IF EMPTY ( .ColNames )
Msg = [ColNames property not filled in.]
MESSAGEBOX( Msg, 16, _VFP.Caption )
RETURN .F.
ENDIF
IF EMPTY ( .ColWidths )
.ColWidths = [1,1,1,1,1]
ENDIF
IF EMPTY ( .ColHeadings )
.ColHeadings = .ColNames
ENDIF
.Access = oDataTier.AccessMethod
.ViewName = [View] + .TableName
oDataTier.CreateView ( .TableName )
ENDWITH
ENDPROC
PROCEDURE Unload
WITH THISFORM
IF USED ( .ViewName )
USE IN ( .ViewName )
ENDIF
RETURN .ReturnValue
ENDWITH
ENDPROC
PROCEDURE cmdShowMatches.Click
WITH THISFORM
Fuzzy = IIF ( THISFORM.Fuzzy.Value = .T., [%], [] )
STORE [] TO Expr1,Expr2,Expr3,Expr4
FOR I = 1 TO .SearchFieldCount
Fld = [THISFORM.Search] + TRANSFORM(I) + [.Value]
IF NOT EMPTY ( &Fld )
LDD = IIF ( VARTYPE( &Fld) = [D], ;
IIF ( .Access = [DBF],[{],['] ), ;
IIF(VARTYPE( &Fld) = [C], ['],[]) )
RDD = IIF ( VARTYPE( &Fld) = [D], ;
IIF ( .Access = [DBF],[}],['] ), ;
IIF(VARTYPE( &Fld) = [C], ['],[]) )
Cmp = IIF ( VARTYPE( &Fld) = [C], [ LIKE ],[ = ] )
Pfx = IIF ( VARTYPE( &Fld) = [C], Fuzzy, [] )
Sfx = IIF ( VARTYPE( &Fld) = [C], [%], [] )
Exp = [Expr] + TRANSFORM(I)
&Exp = [ AND UPPER(] + .Field(I) + [)] + Cmp ;
+ LDD + Pfx + UPPER(ALLTRIM(TRANSFORM(EVALUATE(Fld)))) + Sfx + RDD
ENDIF
ENDFOR
lcExpr = Expr1 + Expr2 + Expr3 + Expr4
IF NOT EMPTY ( lcExpr )
lcExpr = [ WHERE ] + SUBSTR ( lcExpr, 6 )
ENDIF
lcOrder = IIF(EMPTY(.OrderBy),[],[ ORDER BY ] ;
+ ALLTRIM(STRTRAN(.OrderBy,[ORDER BY],[])))
Cmd = [SELECT * FROM ] + .TableName + lcExpr + lcOrder
oDataTier.SelectCmdToSQLResult ( Cmd )
SELECT ( .ViewName )
ZAP
APPEND FROM DBF([SQLResult])
GO TOP
.Grid1.Refresh
IF RECCOUNT() > 0
.cmdSelect.Enabled = .T.
.Grid1.Visible = .T.
.Grid1.Column1.Alignment = 0
.Caption = [Search Form - ] + PROPER(.Name) ;
+ [ (] + TRANSFORM(RECCOUNT()) + [ matches)]
ELSE
.Caption = [Search Form - ] + PROPER(.Name)
MESSAGEBOX( "No records matched" )
.cmdSelect.Enabled = .F.
ENDIF
KEYBOARD [{BackTab}{BackTab}{BackTab}{BackTab}{BackTab}]
ENDWITH
ENDPROC
PROCEDURE cmdClear.Click
WITH THISFORM
FOR I = 1 TO .SearchFieldCount
Fld = [THISFORM.Search] + TRANSFORM(I) + [.Value]
IF VARTYPE ( &Fld ) <> [U]
lVal = IIF ( VARTYPE( &Fld) = [C], [], ;
IIF ( VARTYPE( &Fld) = [D], {//}, ;
IIF ( VARTYPE( &Fld) = [L], .F., ;
IIF ( VARTYPE( &Fld) $ [IN], 0, [?]))))
&Fld = lVal
ENDIF
ENDFOR
ENDWITH
ENDPROC
PROCEDURE cmdSelect.Click
WITH THISFORM
lcStrValue = TRANSFORM(EVALUATE(.KeyField))
.ReturnValue = lcStrValue
.Release
ENDWITH
ENDPROC
PROCEDURE cmdCancel.Click
WITH THISFORM
.ReturnValue = []
.Release
ENDWITH
ENDPROC
ENDDEFINE
How to Use the EasySearch Template
Here's an example of how to use this template in five easy steps:
-
Type this line in the command window:
-
Add two text boxes and a StatesList combo box. Name these three controls Search1, Search2, and Search3.
-
Set the Tab Order to Rows on the search form.
-
Set the MainTable property to Customers, the KeyField property to CustomerID, and the ColumnList property to [CompanyName, ContactName, Phone].
-
Enter FindCust as the SearchForm property value in your Customers form.
CREATE FORM FindCust AS EasySearch FROM Pinter
Figure 3.3 shows the screen for the FindCust form.
)
Figure
3.3 The FindCust form.