How do you add a lookup field to a dataset?

Question

I've got a dataset that I need a lookup field for. Problem is, this dataset's structure is defined by the result of a query. I can't add the field as a TFieldDef before setting .Active = true; because it gets overwritten, and I can't add it after running the query because you can't alter the structure of an open dataset.

There has to be some way to do this. Does anyone know how?

EDIT: There seems to be some confusion about what I'm looking for. I'm not looking for a lookup at query time. I'm looking for a lookup field, a TField object where FieldKind = fkLookup, so that it can be used with a data-aware lookup combo box, for editing the data after the query has returned its result. This has nothing whatsoever to do with the SQL and everything to do with Delphi's dataset model and data-aware controls.

Answer 1

The easiest way is to define persistent fields at design time.

You could also modify your SQL statement to get the calculated values from the server.

Answer 2

The easiest way is to do the lookup yourself with a JOIN in the SQL of the original query, or use a CASE statement to supply the values for the column, again in the original query's SQL.

You can then open that query in the IDE and create a persistent field on the JOINED (or generated by CASE) column at designtime.

Answer 3

You need to create the fields yourself before you open the dataset.

First get all your field definitions from the database

  DataSet.FieldDefs.Update;

Then loop through each fielddef and create the normal field, and also create the lookup field where appropriate. Simplified version of the code like this;

  for I := 0 to DataSet.FieldDefs.Count - 1 do
  begin
    // Allocate a normal field
    AField := DataSet.FieldDefs[I].CreateField(DataSet);

    // Add lookup field if we have another table to look it up from
    if (??? this is the key field of a lookup table) then
    begin
      AField := TStringField.Create(DataSet.Owner);
      AField.FieldName := ???;
      AField.DataSet := DataSet;
      AField.FieldKind := fkLookup;
      AField.KeyFields := ???;
      AField.LookupKeyFields := ???;
      AField.LookupDataSet := ???;
      AField.LookupResultField := ???;
    end;
  end;

Then you can open the dataset.

Answer 4

You have two datasets on your form (say tblOrder,tblCustomer)
One field in the order is a foreign key to the customer table customerId
The Key field of the customer table is Id, Name = Name

Add all fields (right click on the datasets, fields editor , add all fields.

Then right click on the order table and choose fields editor then right click new field.

Name = myLookup,Type is string,Size is xx, FieldType = Lookup.
Key field = customerid,dataset = tblCustomer, lookup Key = Id,Result field = Name.

Now your lookup field is defined.
To make it work in an editor (say in a TDBLookupCombo)
Add a datasources to the form dsOrder
Connect it to tblOrder.

Now set datasource = dsOrder,Field = myLookup
You don't have to set the lookup source...