Seek, Seek(), IndexSeek(), Find

Seek, and ye shall find.

The Bible

These commands and functions all search for data in an indexed table. SEEK and SEEK() provide the fastest way to find a single record, if an index exists for the desired data. INDEXSEEK() lets you find out whether a particular record exists, without moving the record pointer. FIND is an ancient relic, but still handy in the Command Window, if you can remember to use it.

Usage

FIND cExpression
SEEK uExpression
     [ ORDER nIndexNumber | IDXFile 
      | [ TAG ] TagName [ OF CDXFile ]
      [ ASCENDING | DESCENDING ] ]
     [ IN cAlias | nWorkArea ]
lFound = SEEK( uExpression [, cAlias | nWorkArea 
              [, nIndexNumber | cIDXFile | cTagName ] ] )
lFound = INDEXSEEK( uExpression [, lMovePointer
                   [, cAlias | nWorkarea 
                   [, nIndexNumber | cIDXFile | cTagName ] ] ] )

Parameter	Value	Meaning
cExpression, uExpression		The value to search for.
nIndexNumber	Numeric	The position of the index to use among the open indexes.
IDXFile or cIDXFile	Filename or Character	The stand-alone IDX file in which to search.
TagName or cTagName	Name or Character	The index tag in which to search.
CDXFile or cCDXFile	Filename or Character	The CDX index file containing the specified tag.
cAlias	Character	The alias of the table in which to search.
cAlias	Omitted	If nWorkArea is also omitted, search in the current work area.
nWorkArea	Numeric	The work area in which to search.
nWorkArea	Omitted	If cAlias is also omitted, search in the current work area.
lMovePointer	.T.	Move the record pointer to either the matching record or to EOF, if there is no matching record.
lMovePointer	.F.	Don't move the record pointer, whether or not a match is found.
lFound	.T.	uExpression was found.
lFound	.F.	uExpression was not found.

Let's start with the most basic item here. FIND is obsolete. Don't use it in applications. It is handy when you're working in the Command Window and need to search for a particular string because you can omit the quotes around the string.Before VFP, SEEK insisted that you be in the right work area with order set to the right order. Now you can specify the table and index you want to use for the search right in the command. With those additions, SEEK is slightly more capable than SEEK(), because SEEK lets you specify which CDX file contains the tag you want, while SEEK() doesn't. However, SEEK() lets you omit the step of checking FOUND() to see if the search was successful. But SEEK lets you indicate whether to use the tag in ascending or descending order. The specification of ASCENDING or DESCENDING is independent of both the current index direction and the direction in which the index was created.INDEXSEEK(), added in VFP 6, is a solution to a problem that didn't exist before Visual FoxPro. Both SEEK and SEEK() move the record pointer in the specified table (whether or not they find a match). The addition of database rules and triggers, as well as row buffering, means we often want to search for an item without moving the record pointer. That's INDEXSEEK()'s main purpose. It's what KEYMATCH() should have been, but isn't. Use INDEXSEEK() when you want to find out if a value is already in a table, but don't want to commit the record you're working on. We're a little puzzled by INDEXSEEK()'s lMovePointer parameter. The whole point of the function is not to move the record pointer, so why make it an option? We suspect it's because Microsoft expects us all to stop using SEEK and SEEK() and simply switch to INDEXSEEK() in new code.None of these items permanently affects the current work area or the current index order for the table. Changes are made behind the scenes as necessary, then things change back to the way they were found. This means you can, for example, search the target table of a relation based on a tag other than the one controlling the relation without having to reset the relation afterwards. Very handy.As we've mentioned elsewhere, we don't find the nIndexNumber feature to be high on our list of preferred methods for identifying indexes, since modifications to the index or creation of a temporary .IDX file can skew the index numbers. It is preferable (and more self-documenting) to use the tag names, when you know them.

Example

* This is handy in the command window
USE Customer ORDER Company_Na
FIND AROUND THE HORN
 
USE Customer  && No order set
 
* Company_Na is a tag on UPPER(Company_Name)
SEEK "AROUND THE HORN" ORDER Company_Na
? FOUND()  && Returns .T.
 
SELECT 0  && change work areas
* Use nIndexNumber
? SEEK("ERNST HANDEL", "Customer", 1) && Returns .T.
* Use cTagName
? SEEK("ERNST HANDEL", "Customer", "Company_Na") && Returns .T.
 
SELECT Customer
SEEK "H" ORDER Company_Na DESCENDING  && Last "H" name
* Positions pointer on record for Hungry Owl All-Night Grocers
 
SET ORDER TO Company_Na
GO TOP
? INDEXSEEK("ERNST HANDEL")  && Returns .T.
? RECNO()  && Returns 1, because pointer didn't move