Find – SQL

When you click a form's Find toolbar control, VPM Enterprise displays the SQL Find Form if the form's InitialSelectedAlias is a filtered view or CursorAdapter that uses special FIND<field name> parameter variables.

The SQL Find Form allows you to quickly find records using the fields in the parameterized view or CursorAdapter that are filtered using the FIND<field name> variable.

Tip: See View Filters and CursorAdapter Filters to learn how to set up a parameterized view or CursorAdapter with filters that use the Find <field name> variable.

The SQL Find Form lists the fields that have filters that use the FIND<field name> variable in the order that the fields are entered:

·       Views: On the Filter page in the Visual FoxPro View Designer.

·       CursorAdapters: In the CursorAdapter filter WHERE clause.

The SQL Find Form uses the values a user enters on the form to perform a REQUERY() on the InitialSelectedAlias view cursor or a CursorRefresh() on the InitialSelectedAlias CursorAdapter cursor. The REQUERY()/CursorRefresh() creates a cursor with records that match the parameter values entered on the SQL Find Form.

A filtered character field can appear on the SQL Find Form as a single entry (one text box) or as a range (two text boxes). Filtered date, datetime, numeric, float, double and integer fields appear on the SQL Find as a range (two text boxes).

Character Case

To make a character field’s filter case-insensitive, the UPPER() function must be used. For example, a FIND filter can be made case-insensitive as follows:

UPPER(<field>) LIKE UPPER(?FIND<field>)

How to Control the SQL Find Form

Forms based on the Form_Toolbar class have the following properties that allow you to control the behavior of the SQL Find Form:

·       lStartWithFind Property: The lStartWithFind property is the most important form property related to the SQL Find Form. It's default is .F. When you set it to .T. for a form that uses the SQL Find Form, the SQL Find Form is displayed when the form starts up. A user can then select the records to be included in the cursor by entering values into the fields on the SQL Find Form.

Tip: Whenever you decide to use the SQL Find Form, you will almost always want to display it at form startup.

·       lFindForm_SQL_NoEntriesOK Property: By default, the lFindForm_SQL_NoEntriesOK property is set to .F., and a user must enter at least one parameter value on the SQL Find Form before clicking OK.

If you set this property to .T., a user can choose to not make any entries on the SQL Find Form. Then, when the user clicks OK, the user is presented with the Find List Grid showing all records in the source table of the filtered view or CursorAdapter.

Tip: When a form uses a filtered remote view or CursorAdapter to access remote data, think twice before setting the lFindForm_SQL_NoEntriesOK property to .T. In this case, if a user clicks OK without making any entries on the SQL Find Form, the cursor will include all records in the remote data table. That could produce an interminable delay and a serious drain on connection resources.

·       lFindForm_SQLOverride Property: When you set a form's lFindForm_SQLOverride property to .T. (the default is .F.), the form will use the Standard Find Form even if the form's InitialSelectedAlias is a filtered view or CursorAdapter with the special FIND<field name> parameter variables.

·       cFindForm_SQL_AltCaptions Property: You can use the cFindForm_SQL_AltCaptions property to change field label captions displayed on the SQL Find Form. The captions should be entered into this property in the order in which the associated parameters will be displayed on the SQL Find Form. Multiple captions should be separated by semi-colons. Captions can be entered selectively, meaning that only those captions that should be different than the captions that would be used by default from the data dictionary need to be specified.

How to Change the SQL Find Form

When the SQL Find form is accessed, the RunSQLFindForm method of the form runs the form specified in the form properties cFindForm_SQL and cFindForm_SQL_ClassLibrary. cFindForm_SQL contains the name of the Find form or form class. cFindForm_SQL_ClassLibrary contains the name of the class library that contains the Find form class, if any. By default, the SQL Find form is instantiated from the Find_SQL class in the PROGEN.VCX class library.

Specific Forms: If you create your own SQL Find form or form class and want to use it with the Find Toolbar control for a particular form, simply enter your SQL Find form name or form class name into the form’s cFindForm_SQL property. If you are using your own Find form class, enter the name of the class library that contains the form class into the cFindForm_SQL_ClassLibrary. Rebuild the application. The next time you click on the Find toolbar control, your SQL Find form will appear.

All Forms: If you want to use your own SQL Find form or form class with all forms in an application, make the property entries in the properties of the Form_ class (and subclasses) in the application’s PROGEN.VCX class library. If you want your own SQL Find form to be used by all forms in all applications, make your changes at the VPM or DEV level.

How the SQL Find Form Finds Records

Partial Entries: The SQL Find Form finds records that match whatever characters you enter into the fields on the form if you use the "Like" Criteria operator in your filter definitions. For example, using the SQL Find Form illustrated above, a partial entry of “H” in the Last Name field and a partial entry of "3" in the Soc. Sec. # field would return all the records with a last name beginning with "H" and a social security number beginning with "3".

Find List Grid: If more than one record meets the search criteria entered, VPM Enterprise displays all the records that meet the search criteria in a searchable, sortable grid. You can highlight a record and close the grid to display the record on the form.

Tip: The fields in the Find grid are the fields you select for the form's Toolbar List control. See ProMatrix Toolbar List Builder. If you do not set up the Toolbar List control for a form, the Find grid will not appear if more than one record meets the search criteria; instead, the first record found will be displayed on the form.

 

More:

Locate