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.

Solution 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.

Solution 2

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.

Comments

[Mason Wheeler]

I need something that can be modified at runtime, via a lookup column in a grid control, and that specifically requires a lookup field in the dataset.

[Ken White]

@TOndrej: He edited the question, after sighing at my response. :)