How to add calculated or lookup field to a dataset?

[aehimself]

I have a TZQuery object with a simple query... SELECT * FROM MyTable.
I want add an extra calculated (or lookup) so I can build it's value runtime based on the data in the other fields of the same record.

Doubleclick on the query, add a field, set the type to calculated and then open the query.
Only one column exists, the calculated one which I just added design-time.

Because of

ZAbstractRODataSet.pas : 3735 (TZAbstractRODataset.InternalOpen)

Code: Select all

    if ((FieldOptions.AutoCreateMode <> acExclusive) or not (lcPersistent in Fields.LifeCycles)) and not FRefreshInProgress then

the call to CreateFields never happens but even if I call it nothing gets done because a similar check exists in Data.DB:

Code: Select all

  if Fields.Count <> 0 then
    case FieldOptions.AutoCreateMode of
      acExclusive:
        Exit;

I guess I'm doing something wrong, how exactly can I append a calculated field right next to the ones returned by the query itself?

[marsupilami]

Hello AEhimself,

usually Delphi expects you to create all fields during design time for these cases. Doing this during runtime is a 3 step process:

• Create the FieldDefs
• Create Fields
• Add Lookup / Calculated Fields
• Open the DataSet

This is a sample code:

Code: Select all

procedure TSomeForm.SomeProc;
begin
  // probably some code...
  
  // add the lookupfield
    if ZQ_Provision.FieldCount = 0 then
      AddLookupField(ZQ_Provision, 'MA', frmDatenmodul.MitarbeiterQ, 'MITARBEITER_NR', 'NR', 'MITARBEITER');
    ZQ_Provision.Open;
        
  // more code...
end;

procedure AddLookupField(DataSet: TDataSet; const FieldName: String;
  LookupDataSet: TDataSet; const KeyFields, LookupKeyFields, LookupResultField: string);
var
  x: Integer;
  NewField: TWideStringField;
  SourceField: TField;
begin
  SourceField := LookupDataSet.FindField(LookupResultField);
  if not Assigned(SourceField) then raise Exception.Create('Couldn''t find field "' + LookupResultField + '" in LookupDataSet');
  if not (SourceField is TStringField) and not (SourceField is TWideStringField) then raise Exception.Create('Source field needs to be either TStringField or TWideStringField.');

  if (DataSet.FieldDefs.Count = 0) and (DataSet.FieldCount = 0) then begin
    DataSet.FieldDefs.Update;
  end;

  if (DataSet.FieldCount = 0) then begin
    for x := 0 to DataSet.FieldDefs.Count - 1
    do DataSet.FieldDefs.Items[x].CreateField(DataSet);
  end;

  NewField := TWideStringField.Create(DataSet);
  NewField.FieldName := FieldName;
  NewField.KeyFields := KeyFields;
  NewFIeld.LookupDataSet := LookupDataSet;
  NewField.LookupKeyFields := LookupKeyFields;
  NewField.LookupResultField := LookupResultField;
  if Pos(';', LookupResultField) = 0 then NewField.Size := LookupDataSet.FieldByName(LookupResultField).Size;
  NewField.FieldKind := fkLookup;
  NewField.Dataset := DataSet;
  NewField.Size := SourceField.Size;
end;

The steps are all done by AddLookupField. I am not sure, why SourceField needs to be TStringField or TWideStringField. Probably because I use strings for IDs in my program. It possibly also could work for INtegers and the like but I don't need that.

Also you have to make sure that you call AddLookupField only before opening the query for the first time. The fields will not be cleared after in Close when Fields get added. Possibly because TDataSet thinks that someone added Lookupfields manually.

I really wish we had better possibilities for this kind of things in Zeos...

Best regards,

Jan

[marsupilami]

Note: I seem to remember that there are problems when doing a locate on a calculated field or filtering by calculated fields or something like that with the current Zeos 8.0 Beta...