RAD Studio |  |

Using Lookup
See Also
Collapse All

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:

Copy Code

    var
      LookupResults: Variant;
    begin
      LookupResults := CustTable.Lookup('Company', 'Professional Divers, Ltd.',     'Company;Contact; Phone');
    end;

Copy Code

    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:

Copy Code

    var
      LookupResults: Variant;
    begin
    with CustTable do
      LookupResults := Lookup('Company; City', VarArrayOf(['Sight Diver', 'Christiansted']),    'Company; Addr1; Addr2; State; Zip');
    end;

Copy Code

    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.

See Also

Using Locate

Copyright(C) 2009 Embarcadero Technologies, Inc. All Rights Reserved.
What do you think about this topic? Send feedback!