Devart Forums

Posted: **Thu 03 Jan 2013 03:52**

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;

Posted: **Thu 03 Jan 2013 10:21**

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;

Posted: **Mon 07 Jan 2013 05:51**

Thanks a lot! It works

Posted: **Tue 08 Jan 2013 09:23**

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

Posted: **Wed 09 Jan 2013 11:56**

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;

Posted: **Thu 10 Jan 2013 04:30**

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;

Posted: **Thu 10 Jan 2013 13:34**

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;

Posted: **Thu 10 Jan 2013 14:20**

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

Posted: **Thu 10 Jan 2013 14:39**

hello,

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

Posted: **Sun 13 Jan 2013 08:14**

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;

Posted: **Sun 13 Jan 2013 09:04**

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;

Posted: **Tue 15 Jan 2013 09:57**

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.

Posted: **Thu 14 Mar 2013 02:23**

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)

Posted: **Thu 14 Mar 2013 02:42**

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.

Posted: **Thu 14 Mar 2013 13:13**

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

Devart Forums

Dynamic field problem in VirtualTable

Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable

Re: Dynamic field problem in VirtualTable