Dynamic field problem in VirtualTable

[tcflam]

I create the dynamic field in Virtualtable and use the EMS Advance Import Xls component to import the data into virtualtable. But the datetime field, float, integer is empty after import. If I hardcode the field attrib in the coding, then the data is OK. Any solution?

I want to use the "Red Color" coding only. Any idea?

while not map_setting.Eof do
begin
if map_setting.FieldByName('map_del_attrib').AsString = 'ftDateTime' then
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
ftDateTime, 0);
end
else
if map_setting.FieldByName('map_del_attrib').AsString = 'ftFloat' then
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
ftFloat, 0);
end
else
if map_setting.FieldByName('map_del_attrib').AsString = 'ftInteger' then
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
ftInteger, 0);
end
else
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
map_setting.FieldByName('map_del_attrib').DataType,
map_setting.FieldByName('map_field_length').Value); end;

map_setting.Next;
end;

[AlexP]

Hello,

In your highlighted code you are trying to use the DataType property that is always ftString, thus you should look for the type corresponding to the name, e.g. TFieldType.

Code: Select all

const
  FieldTypeStr:  array [0..11] of string = ('ftUnknown', 'ftString', 'ftSmallint', 'ftInteger', 'ftWord',
    'ftBoolean', 'ftFloat', 'ftCurrency', 'ftBCD', 'ftDate', 'ftTime', 'ftDateTime');
var
  i: integer;
begin
  VirtualTable2.Open;
  while not VirtualTable2.Eof do
  begin
    for i := low(FieldTypeStr) to high(FieldTypeStr) do
      if  VirtualTable2.FieldByName('map_del_attrib').AsString = FieldTypeStr[i] then
      begin
        VirtualTable1.FieldDefs.Add(VirtualTable2.FieldByName('map_table_field').Value, TFieldType(i) ,VirtualTable2.FieldByName('map_field_length').Value);
        break;
      end;
    VirtualTable2.Next;
  end;
  VirtualTable1.Open;
end;

[tcflam]

Thanks a lot! It works

[tcflam]

How to create the new calculated or lookup field in virtualtable by dynamic query?

[AlexP]

Hello,

There is a code below for adding both simple and calculated fields in run-time and calculation of this field value.

Code: Select all

type
  TForm1 = class(TForm)
    VirtualTable1: TVirtualTable;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    procedure FormCreate(Sender: TObject);
    procedure VirtualTable1CalcFields(DataSet: TDataSet);
  private
    { Private declarations }
    FNAME: TStringField;
    FSAL: TCurrencyField;
    FBONUS: TFloatField;
    FSUM: TCurrencyField;
  public
    { Public declarations }

  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
  FNAME := TStringField.Create(VirtualTable1);
  FNAME.FieldName := 'Name';
  FNAME.DataSet := VirtualTable1;
  FSAL := TCurrencyField.Create(VirtualTable1);
  FSAL.FieldName := 'Sal';
  FSAL.DataSet := VirtualTable1;
  FBONUS := TFloatField.Create(VirtualTable1);
  FBONUS.FieldName := 'Bonus';
  FBONUS.DataSet := VirtualTable1;
  FSUM := TCurrencyField.Create(VirtualTable1);
  FSUM.FieldName := 'Sum';
  FSUM.FieldKind := fkCalculated;
  FSUM.DataSet := VirtualTable1;
  VirtualTable1.Open;
  VirtualTable1.Edit;
  VirtualTable1.Fields[0].AsString := 'SCOTT';
  VirtualTable1.Fields[1].AsCurrency := 100;
  VirtualTable1.Fields[2].AsFloat := 1.2;
  VirtualTable1.Post;

end;

procedure TForm1.VirtualTable1CalcFields(DataSet: TDataSet);
begin
  FSUM.Value :=  FSAL.Value*FBONUS.Value;
end;

[tcflam]

Thanks for your hints how to create calculated field. But I need to create the dynamic field too. I used your hint to create the following code. But it has the error that is "Field 'po_batch_nbr' not found"

while not msqry_extfield.Eof do
begin
for i := low(FieldTypeStr) to high(FieldTypeStr) do
if msqry_extfield.FieldByName('add_table_del_attrib').AsString = FieldTypeStr then
begin
poreport_buffer.FieldDefs.Add(msqry_extfield.FieldByName('add_table_field').Value, TFieldType(i) ,msqry_extfield.FieldByName('add_table_field_len').Value);
poreport_buffer.FieldDefs.Update;
poreport_buffer.FieldByName(msqry_extfield.FieldByName('add_table_field').Value).FieldKind := fkCalculated;
poreport_buffer.FieldDefs.Update;
break;
end;
msqry_extfield.Next;
end;

[AlexP]

Hello,

FieldDefs, not Fields, are created in the provided code. To create fields and modify their properties, you should add a line for creation of field by its FieldDef to the loop.

Code: Select all

procedure TForm1.FormCreate(Sender: TObject);
const
  FieldTypeStr:  array [0..11] of string = ('ftUnknown', 'ftString', 'ftSmallint', 'ftInteger', 'ftWord',
    'ftBoolean', 'ftFloat', 'ftCurrency', 'ftBCD', 'ftDate', 'ftTime', 'ftDateTime');
var
  i: integer;
begin
  VirtualTable2.Open;
  while not VirtualTable2.Eof do
  begin
    for i := low(FieldTypeStr) to high(FieldTypeStr) do
      if  VirtualTable2.FieldByName('map_del_attrib').AsString = FieldTypeStr[i] then
      begin
        VirtualTable1.AddField(VirtualTable2.FieldByName('map_table_field').Value, TFieldType(i) ,VirtualTable2.FieldByName('map_field_length').Value);
        VirtualTable1.FieldDefs[VirtualTable1.FieldDefs.IndexOf(VirtualTable2.FieldByName('map_table_field').Value)].CreateField(VirtualTable1);
        VirtualTable1.FieldByName(VirtualTable2.FieldByName('map_table_field').Value).FieldKind := fkCalculated;
        break;
      end;
    VirtualTable2.Next;
  end;
  VirtualTable1.Open;
end;

[tcflam]

That's great. You help me to solve my problem.

[AlexP]

hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.

[tcflam]

Hi, I have one more question about calculated field. Can I assign the value dynamically?
The following is hardcode in the oncalcfield event. But I want to dynamic assign by table records. Because I don't want to hardcode in coding. I want to control by table records. Then the program need not to recomplie when add the fields.


procedure Tfrm_ImportDialog.poreport_bufferCalcFields(DataSet: TDataSet);
begin
poreport_buffer.FieldByName('shp_type').Value := tran_code;
end;

[tcflam]

Why I got the field not found error as following code? If remark shp_createdate and shp_modifydate in oncalcfields event, then the error is gone.

while not msqry_extfield.Eof do
begin
for i := low(FieldTypeStr) to high(FieldTypeStr) do
if msqry_extfield.FieldByName('add_table_del_attrib').AsString = FieldTypeStr then
begin
poreport_buffer.AddField(msqry_extfield.FieldByName('add_table_field').Value, TFieldType(i) ,msqry_extfield.FieldByName('add_table_field_len').Value);
poreport_buffer.FieldDefs[poreport_buffer.FieldDefs.IndexOf(msqry_extfield.FieldByName('add_table_field').Value)].CreateField(poreport_buffer);
poreport_buffer.FieldByName(msqry_extfield.FieldByName('add_table_field').Value).FieldKind := fkCalculated;
break;
end;
msqry_extfield.Next;
end;

poreport_buffer.Active := true;

procedure Tfrm_ImportDialog.poreport_bufferCalcFields(DataSet: TDataSet);
begin
poreport_buffer.FieldByName('shp_type').Value := tran_code;
// poreport_buffer.FieldByName('shp_createdate').Value := date;
// poreport_buffer.FieldByName('shp_modifydate').AsDateTime := now;
end;

[AlexP]

Hello,

The value calculating procedure for calculated fields occurs only in the onCalcFields event, therefore you should create this method and set the required actions in it. You can find more information on calculated fields in the Delphi help.

[tcflam]

I have the problem again about ftWideString attrib in VirtualTable. If I create the dynamic field and set it ftWideString, then it has the error said "Invalid field size" How to solve it. Because I found that it has unknown character if define ftString. My program need to handle chinese characters (Traditional & Simplified. That means unicode handling)

[tcflam]

Additional information about my version. Delphi is XE2 and SDAC is 6.6.11

And I found the VirtualTable is lost in 6.6.11. Before I use Delphi 2007 & SDAC 4.8. Now I need to convert the new version because the unicode issue.

[AlexP]

Hello,

Please specify the moment when this error occurs: during field creation, during value assigning, during table opening, etc.
The VirtualTable is at the Data Access tab