Using Lookup
Go Up to Searching Datasets
Lookup searches for the first row that matches specified search criteria. If it finds a matching row, it forces the recalculation of any calculated fields and lookup fields associated with the dataset, then returns one or more fields from the matching row. Lookup does not move the cursor to the matching row; it only returns values from it.
In its simplest form, you pass Lookup the name of field to search, the field value to match, and the field or fields to return. For example, the following code looks for the first record in the CustTable where the value of the Company field is "Professional Divers, Ltd.", and returns the company name, a contact person, and a phone number for the company:
var
LookupResults: Variant;
begin
LookupResults := CustTable.Lookup('Company', 'Professional Divers, Ltd.', 'Company;Contact; Phone');
end;
Variant LookupResults = CustTable->Lookup("Company", "Professional Divers, Ltd",
"Company;Contact;Phone");
Lookup returns values for the specified fields from the first matching record it finds. Values are returned as Variants. If more than one return value is requested, Lookup returns a Variant array. If there are no matching records, Lookup returns a Null Variant.
The real power of Lookup comes into play when you want to search on multiple columns and specify multiple values to search for. To specify strings containing multiple columns or result fields, separate individual fields in the string items with semicolons.
Because search values are Variants, if you pass multiple values, you must either pass a Variant array as an argument (for example, the return values from the Lookup method), or you must construct the Variant array in code using the VarArrayOf function. The following code illustrates a lookup search on multiple columns:
var
LookupResults: Variant;
begin
with CustTable do
LookupResults := Lookup('Company; City', VarArrayOf(['Sight Diver', 'Christiansted']), 'Company; Addr1; Addr2; State; Zip');
end;
Variant LookupResults;
Variant locvalues[2];
Variant v;
locvalues[0] = Variant("Sight Diver");
locvalues[1] = Variant("Kato Paphos");
LookupResults = CustTable->Lookup("Company;City", VarArrayOf(locvalues, 1),
"Company;Addr1;Addr2;State;Zip");
// now put the results in a global stringlist (created elsewhere)
pFieldValues->Clear();
for (int i = 0; i < 5; i++) // Lookup call requested 5 fields
{
v = LookupResults.GetElement(i);
if (v.IsNull())
pFieldValues->Add("");
else
pFieldValues->Add(v);
}
Like Locate, Lookup uses the fastest possible method to locate matching records. If the columns to search are indexed, Lookup uses the index.