Page 1 of 1
How to add calculated or lookup field to a dataset?
Posted: 03.09.2021, 14:08
by 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?
Re: How to add calculated or lookup field to a dataset?
Posted: 06.09.2021, 11:51
by 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
Re: How to add calculated or lookup field to a dataset?
Posted: 06.09.2021, 11:54
by 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...