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.
cSearchTag	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.
uResult	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"