TBDEDataSet.Lookup Method

Retrieves field values from a record that matches specified search values.

Pascal

function Lookup(const KeyFields: string; const KeyValues: Variant; const ResultFields: string): Variant; override;

C++

virtual __fastcall Variant Lookup(const AnsiString KeyFields, const Variant KeyValues, const AnsiString ResultFields);

Description

Call Lookup to search for a record in the dataset in which specified fields contain specified values and return other field values from the found record. Unlike other dataset search methods, Lookup performs its search without moving the record pointer of the dataset. This is useful when doing data validation on a record's data while the record is still in edit or insert mode.

KeyFields is a string containing the names of the field or fields on which the search is predicated. If the search is based on more than one field, KeyFields is a list of field names delimited with semicolons.

KeyValues is a variant array containing the values to find in the fields specified in KeyFields. To specify multiple search values, pass KeyValues as a variant array as an argument, or construct a variant array on the fly using the VarArrayOf routine. These search values must be specified in the order of the fields in KeyFields to which each corresponds. For instance, the first value is used for the first field in KeyFields, the second value for the second field, and so on.

ResultFields is a string containing the names of the field or fields from which to return values. If values are to be returned from more than one field, ResultFields is a list of field names delimited with semicolons.

Lookup returns a variant array containing the values from the fields specified in ResultFields.

Lookup uses the fastest possible method to locate matching records. If the search fields in KeyFields are indexed, Lookup uses the index. Otherwise Lookup creates a filter for the search.

The example below uses the Lookup method to search the sample Paradox table Customer. The search is based on two fields: Company and State. The search is also based on the following two values in these two fields: "Blue Sports" and "OR". For the search to be successful, there must be a record where the Company field contains the value "Blue Sports" AND the field State contains "OR". If either criteria is not met, the search is unsuccessful (such a record does exist in this table, so the search will be successful). The values of the CustNo and Addr1 fields are returned by the execution of the Lookup method. If the search is unsuccessful, the VarType function applied to the returned variant array returns varNull.

Copy Code

procedure TForm1.Button1Click(Sender: TObject);
var
  V: Variant;
  C: Integer;
  A: String;
begin
  V := Table1.Lookup('Company;State',
    VarArrayOf(['Blue Sports', 'OR']),
    'CustNo;Addr1');
if not (VarType(V) in [varNull]) then begin
    C := V[0];
    A := V[1];
    ShowMessage(IntToStr(C) + #10 + A);
end
else
    ShowMessage('Search unsuccessful!');
end;

Copy Code

int C;
AnsiString A;
Variant V;
V = Table1->Lookup("Company;State",
  VarArrayOf(OPENARRAY(Variant, ("Blue Sports","OR"))),
  "CustNo;Addr1");
if !(VarType(V) == varNull)
{
  C = V[0];
  A = V[1];
  ShowMessage(IntToStr(C) + #10 + A);
}
else
  ShowMessage("Search unsuccessful!");