Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.
LOOKUP()
LOOKUP() is a very cool, but very complex, way to pull information out of a table. It lets you look up information based on one field or tag, but return information from a different field. It’s kind of like many of the lists and combo boxes we set up, where the user chooses based on one piece of data, but we store a different data item behind the scenes.
Usage
uResult = LOOKUP( FieldToReturn, uSearchExpr, SearchField
[, cSearchTag ] )
|
Parameter |
Value |
Meaning |
|
FieldToReturn |
Name |
The name of the field whose value is returned. |
|
uSearchExpr |
Expression |
The value to search for. |
|
SearchField |
Name |
The field in which to search for uSearchExpr unless cSearchTag is included. Ignored if cSearchTag is included; see below. |
|
cSearchTag |
Character |
The name of an index tag that should be used to search for uSearchExpr. |
|
Omitted |
Search for uSearchExpr in the field SearchField. |
|
|
uResult |
Anything except empty value |
The search expression was found, so the value of FieldToReturn for that record is returned. |
|
Empty |
No record was found with the specified value. |
LOOKUP() actually provides two different ways to search, either in a particular field or using a particular tag. If you specify a tag, it doesn’t matter what you pass for SearchField as long as it’s a valid field name in the table. If you don’t pass a tag name, FoxPro searches for the value in SearchField. If there’s a tag on SearchField, it’s used to speed up the search. Otherwise, each record in turn is checked.
Notice also that both field names (the one to return and the one to search) are specified as names while the tag name is specified as a string. Weird.
In addition to returning the specified field, LOOKUP() moves the record pointer to the matching record. When LOOKUP() uses a tag to perform the search, and there’s more than one matching record, you get the first one in that index. If there’s no tag for that search, you get the first matching record in physical order, even if order is currently set. If no match is found, the record pointer moves to the end of the file (that is, EOF() is .T.).
The value returned by LOOKUP() is always of the same type as FieldToReturn. If no match is found, you get an empty value of the specified type.
We don’t use LOOKUP() a lot because it’s complex and because SEEK and SEEK() let us do the same thing. Although they require more code, the code itself is easier to understand.
Example
USE Employee
* No index used for this one
? LOOKUP(Birth_Date, "Laura", First_Name) && Returns {1/9/58}
* Use name index. Specification of Last_Name is ignored, but
* aids in readability
? LOOKUP(Home_Phone, "FULLER", Last_Name, "Last_Name")
&& Returns "(206) 555-9482"
* Since there's an index on Employee_Id, it's used automatically
? LOOKUP(Last_Name, " 10", Employee_Id) && Returns "Martin"