Dynamically Creating Lookup And Calculated Fields

Introduction

On occasion you may wish to create a calculated or lookup field dynamically. If you've thought of this, you may have been put off by the statements in the C++ Builder help:

Do not create instances of TField.

This seems fairly unambiguous, but it is wrong.

The Steps

Create your form, data modules, and tables. The table for which you are going to dynamically create lookup and / or calculated fields should either have persistent fields created at design-time, or programatically prior to adding the lookup / calculated fields. The table should be inactive at the time you add the lookup / calculated fields, and can be made active afterward. If you are adding a calculated field, you must have an OnCalcFields handler to calculate the value. This can be established for the table at design-time or assigned to the OnCalcFields event at run-time. In any event, the code must be present for this at design time (though you could, with some complication, make this dynamic by calling functions from a dynamically loaded DLL). Then make the table active, and you are set.

The Example

The example adds a calculated and lookup field to a table at run time. The calculated field essentially duplicates the lookup field for simplicity, but of course could contain any calculated value.

The tables are a table of accounts which have a zone ID foreign key field and a Zone domain table which contains the description of the zone. Two copies of this table are used - one for the lookup and one for the calculated field. This may not be strictly necessary, but keeps things simple.

This is what it looks like at run-time:

Here is the .dfm

The Code

The code itself is simple:

//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop

#include "MainFormUnit.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
   : TForm(Owner)
{
}
//---------------------------------------------------------------------------

void __fastcall TForm1::FormShow(TObject *Sender)
{
   TStringField   *CalcField = new TStringField(this);

      CalcField->FieldName = "TestDynamicCalc";
      CalcField->FieldKind = fkCalculated;
      CalcField->DataSet = AccountTable;

      CalcField->Name = "Table1TestDynamicCalc";

   TStringField   *LookupField = new TStringField(this);

      LookupField->FieldName = "TestDynamicLookup";
      LookupField->FieldKind = fkLookup;
      LookupField->DataSet = AccountTable;

      LookupField->LookupDataSet = ZoneLookupFieldTable;
      LookupField->KeyFields = "ZONE_ID";
      LookupField->LookupKeyFields = "ID";
      LookupField->LookupResultField = "DESC";

      LookupField->Name = "Table1TestDynamicLookup";

   AccountTable->Active = true;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::AccountTableCalcFields(TDataSet *DataSet)
{
   if
   (
      ZoneCalculatedFieldTable->Locate
      (
         "ID",
         AccountTable->FieldByName("ZONE_ID")->AsString,
         TLocateOptions()
      )
   )
   {
      AccountTable->FieldByName("TestDynamicCalc")->AsString =
         ZoneCalculatedFieldTable->FieldByName("DESC")->AsString;
   };
}
//---------------------------------------------------------------------------


Conclusion

Contrary to common belief, it is not hard to create calculated and lookup fields for tables and queries at run-time. Whether it is worth the effort depends on your application.