Calculated fields

I want an easy way to create calculated fields at run-time for example suppose that I have two fields A and B both are Integers, I want to multiply A by B and put the result in temporary field (C) C should not be a physical field.
How can I do that ?
LVL 7
MotazAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JaymolCommented:
Motaz, could you explain why you need to do this?  I don't really see what you're getting at.

If you're just looking for a referential variable to accompany a table, simply create an array [0..Number of records-1] and then refer to it with ArrayName[RecNum] to read/write.

Is that what you mean?

John.
0
rwilson032697Commented:
You can do this in the context of a query, like this:

MyQuery.sql := 'select a, b, sum(a * b) as c from ATable where...'

Field C in the query result set is then the product of fields a and b in ATable.

Is this what you want?

Cheers,

Raymond
0
MotazAuthor Commented:
If I declare such array I've to fill it with data programmatically (I must go from begining of table until end of it to do this calculation manually) moreover I want to display this new field in a quick report, it will be easy if I could define it as a calculated field.

Thanks in advance.
Motaz
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

karouriCommented:
try this motaz,
* right click on the dataset(let's say table) and choose 'Add New Field'
* name the field as you like (say C)and then choose the type 'Calculated' with type integer (e.g)
* go the the event of the table named OnCalcFields and program it like:
procedure TForm1.Table1CalcFields...

begin
 Table1.FieldByName('C').AsInteger:= Table1.FieldByName('A').AsInteger * Table1.FieldByName('B').AsInteger;

end;

I am not near a Delphi to try this but if you can't do with this say and i'll explain it further..
yours,
k
0
aubsCommented:
Karouri is right (not bad from memory), but you will need to add all the other fields that you need as well, or you will have only one field ('C') accessible.

Either double click on the dataset component (TTable) or right click and select 'fields editor'. Then click the 'add fields...' to add all the existing fields you want, then 'New fields...' to add the calculated field. The rest is like Karouri says.

Aubs
0
MotazAuthor Commented:
Yes, this work when I determine the table which I want to open at design-time but I open the tables at run time and I do not know it's fields until I open it.

Motaz
0
karouriCommented:
This is a source code that makes a new integer calculated field by the name of you choice:

===================
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, Grids, DBGrids, StdCtrls;

type
  TForm1 = class(TForm)
    Table1: TTable;
    Table1NAME: TStringField;
    Table1SIZE: TSmallintField;
    Table1WEIGHT: TSmallintField;
    Table1AREA: TStringField;
    Table1BMP: TBlobField;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    Edit1: TEdit;
    procedure Table1CalcFields(DataSet: TDataSet);
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
    newfield:string;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Table1CalcFields(DataSet: TDataSet);
begin
Table1.FieldByName(newfield).Value:=Table1SIZE.Value+2;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  T: TIntegerField;
begin
      if newfield<>'' then
  begin
        Table1.Close;
        T:=(Table1.FieldByName(newfield) as TIntegerField);
    T.DataSet:=nil;
    T.Free;
    Table1.FieldDefs.UpDate;
    Table1.Open;
  end;
  newfield:=Edit1.Text;
  if newfield='' then exit;
      Table1.Close;
  T := TIntegerField.Create(Self);
  T.FieldName := newfield;
  T.Name := Table1.Name + T.FieldName;
  T.Index := Table1.FieldCount;
  T.FieldKind:=fkCalculated;
  T.DataSet := Table1;
  Table1.FieldDefs.UpDate;
  Table1.OnCalcFields:=Table1CalcFields;
  Table1.Open;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
newfield:='';
end;

end.
=====================

Now if you want to change the rule of calculating each field you can:
* Either change the line
     Table1.OnCalcFields:=Table1CalcFields;

to choose a function you want on each field,
* or write Table1CalcFields in such away it implements mulitple rules by multiple cases dependent on 'newfield'

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.