Calulated Field?

Can I use a Calulated Field to Sum the entire column in my Table and if so, how?  Will this slow things down for a large table?
gammillAsked:
Who is Participating?
 
adengConnect With a Mentor Commented:
I've made this function in my own program to sum the entire column, but i didn't uses calculated field to store the result, because it's make the process very slow and unefficient, except it's a master-detail table, i put the result in a calculated field in master table.

function TDataModule1.Total(Tabel: TDataSet; Field: tField): Extended;
var
     Total         : Extended;
     PrevRecord    : TBookmark;
begin
     PrevRecord := Tabel.GetBookmark;
     try
          Tabel.DisableControls;
          Tabel.First;
          Total := 0;
         
          while not Tabel.EOF do
          begin
               Total := Total + Field.Value;
               Tabel.Next;
          end;
          Result := Total;
         
     finally
          Tabel.EnableControls;
          if PrevRecord <> nil then
          begin
               Tabel.GoToBookmark(PrevRecord);
               Tabel.FreeBookmark(PrevRecord);
          end;
     end;
end;

{on master table on calc event }
procedure TDataModule1.Table1.CalcFields(DataSet: TDataSet);
begin
     Table1TOTAL.Value:= Total(Table2, Table2TOTAL));
end;

0
 
kretzschmarCommented:
hi gammil,

it can be also done with a query, like

select sum(YourFieldname) as Total from Yourtable

after query.open

you can say

YourTotalContentField := Query1.FieldbyName('Total').AsFloat;

in Code

query1.SQL.Clear;
query1.SQL.Add('select sum(YourFieldname) as Total from Yourtable');
query1.Open;
YourTotalContentField := Query1.FieldbyName('Total').AsFloat;
query1.Close;

The perfomance depends on the Database in background. SQL-Databases like ORACLE are very fast, PARADOX is slower in this case.

meikl
0
 
gammillAuthor Commented:
Thanks for the help adeng!  I'll give it a try.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
merry_princeCommented:
Hello kretzschmar and adeng,

Can you give me a sample about OnCalcFields event? How to show detailed content of ftMeme field by it? (Please note, not create new column. but use original column)

Please advise. It's urgent. Thanks a lot.
0
 
kretzschmarCommented:
merry,

in the oncalcevent you can't use
directly a blob-field from the original dataset

but you could attach a calculated string- or memofield,
and fill it with a query in the onCalcField-event

meikl ;-)
0
 
merry_princeCommented:
kretzschmar,

Can you give a sample? Thks.
0
 
kretzschmarCommented:
a sample,
using the table custoly in the alias dbdemos

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DBTables, DB, Grids, DBGrids;

type
  TForm1 = class(TForm)
    Table1: TTable;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Table1CustNo: TAutoIncField;
    Table1Last_Name: TStringField;
    Table1First_Name: TStringField;
    Table1VIP_Status: TStringField;
    Table1Address1: TStringField;
    Table1Address2: TStringField;
    Table1City: TStringField;
    Table1StateProv: TStringField;
    Table1Post_Code: TStringField;
    Table1Country: TStringField;
    Table1Phone: TStringField;
    Table1Fax: TStringField;
    Table1EMail: TStringField;
    Table1Remarks: TMemoField;
    Table1AMemoAsCalcField: TStringField;
    Query1: TQuery;
    procedure Table1CalcFields(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Table1CalcFields(DataSet: TDataSet);
begin
  query1.Close;
  query1.ParamByName('ID').AsInteger := Table1.FieldByName('CustNo').AsInteger;
  query1.Open;
  Table1.FieldByName('AMemoAsCalcField').AsString :=
    TBlobField(query1.FieldByName('Remarks')).AsString;
  query1.Close;
end;

end.

meikl ;-)
0
 
merry_princeCommented:
Hi meikl,

Thanks for your helps. If it's convenient for you. Can you provide the method "DrawCell" to show memo field?
0
 
kretzschmarCommented:
? what does you mean ?
0
 
merry_princeCommented:
I want to use OnDrawDataCell event of DBGrid to show memo field. I had know how to do it. Thanks a lot. If you would like to know the details. Please inform me.
0
 
kretzschmarCommented:
well,

applied to the sample above
(property defaultdraw of the dbgrid must be set to false)

procedure TForm1.DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect;
  Field: TField; State: TGridDrawState);
begin
  if field.FieldName = 'Remarks' then
  begin
    //show the Memo content in red
    DBGrid1.Canvas.Font.Color := clRed;
    DBGrid1.Canvas.TextRect(rect,rect.left + 2,rect.Top+2,Table1Remarks.AsString);
  end else
    DBGrid1.DefaultDrawDataCell(Rect, Field, State);
end;

hope that is it

meikl ;-)
0
 
merry_princeCommented:
meikl,

Ho, you are excellent. Keep in touch. Thank you very much.
0
 
kretzschmarCommented:
well, glad that it works for you ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.