Title: Automating lookup process without compromising speed
Question: Every Delphi DB programmers know the famous rule of thumb:
Never use fkLookup but use fkInternalCalc and do lookup yourself.
I wish Borland give another FieldKind probably named fkLookupInternalCalc. This field act as fkInternalCalc on retrieval and act as fkLookup on manipulation. But the reality is far from my wish.
Answer:
My first attempt to do something like this is setting all my fkInternalCalc as fkLookup on design time. On TDataset.BeforeOpen event I change all fkLookup FieldKind into fkInternalCalc and set TField.Tag with certain number (888 for example). On TDatset.AfterOpen I change all field with 888 value on it's tag into fkLookup. And ooops I can't do that on an opened dataset. So I start thinking another way for automating this process, because I hate to do coding for something like this. So I let the field as fkInternalCalc field but setting all properties needed by lookup field. I write a generic event handler for OnFieldChange event and then I write the method for hooking up the necessary field with the generic event handler. Here's my snipplet:
.
.
.
public
procedure OnFieldChange(Sender: TField);
procedure CheckKeyField(Dataset: TDataset);
end;
TOldOnFieldChange = class(TComponent)
public
OnChange: TFieldNotifyEvent;
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure TForm1.CheckKeyField(Dataset: TDataset);
var
i,j:integer;
aField:TField;
anOldOnFieldChange:TOldOnFieldChange;
aStringList:TStringList;
begin
aStringList:=TStringList.Create;
try
with DataSet do
for i:=0 to Fields.Count-1 do
begin
aField:=Fields[i];
for j:=0 to Fields.Count-1 do
begin
aStringList.Text:=StringReplace(Fields[j].KeyFields,';',chr(10),[rfReplaceAll]);
if aStringList.IndexOf(aField.FieldName)=0 then
begin
anOldOnFieldChange:=TOldOnFieldChange.Create(aField.Owner);
with anOldOnFieldChange do
begin
OnChange:=aField.OnChange;
Tag:=aField.Tag;
end;
aField.Tag:=longint(anOldOnFieldChange);
aField.OnChange:=OnFieldChange;
break;
end;
end;
end;
finally
aStringList.Free;
end;
end;
procedure TForm1.OnFieldChange(Sender: TField);
var
i,j,keyFieldsCount:integer;
aField:TField;
anOldOnFieldChange: TOldOnFieldChange;
aStringList:TStringList;
aVariant:variant;
begin
anOldOnFieldChange:= TOldOnFieldChange(Sender.Tag);
with anOldOnFieldChange do
if Assigned(OnChange) then
OnChange(Sender);
aStringList:=TStringList.Create;
try
with Sender.DataSet do
for i:=0 to Fields.Count-1 do
begin
aField:=Fields[i];
aStringList.Text:=StringReplace(aField.KeyFields,';',chr(10),[rfReplaceAll]);
keyFieldsCount:=aStringList.Count;
if aStringList.IndexOf(Sender.FieldName)=0 then
begin
with aField,aField.LookupDataSet do
if keyFieldsCount=1 then
Value:=Lookup(LookupKeyFields,Sender.Value,LookupResultField)
else
begin
aVariant:=VarArrayCreate([0,keyFieldsCount-1],varVariant);
for j:=0 to keyFieldsCount-1 do
aVariant[j]:=Sender.DataSet[aStringList.Strings[i]];
Value:=Lookup(LookupKeyFields,aVariant,LookupResultField)
end;
end;
end
finally
aStringList.Free;
end;
end;
procedure TForm1.FormCreate(Sender: TObject);
var
i:integer;
begin
for i:=0 to ComponentCount-1 do
if Components[i] is TDataset then
CheckKeyField(TDataset(Components[i]));
end;
end.