[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

Calulated Field?

Posted on 1999-01-09
Medium Priority
408 Views
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?
0
Question by:gammill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 1

Accepted Solution

ID: 1355566
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

LVL 27

Expert Comment

ID: 1355567
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

Author Comment

ID: 1355568
Thanks for the help adeng!  I'll give it a try.
0

LVL 1

Expert Comment

ID: 8149835

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)

0

LVL 27

Expert Comment

ID: 8149911
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

LVL 1

Expert Comment

ID: 8149965
kretzschmar,

Can you give a sample? Thks.
0

LVL 27

Expert Comment

ID: 8150037
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;
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

LVL 1

Expert Comment

ID: 8156081
Hi meikl,

Thanks for your helps. If it's convenient for you. Can you provide the method "DrawCell" to show memo field?
0

LVL 27

Expert Comment

ID: 8156943
? what does you mean ?
0

LVL 1

Expert Comment

ID: 8157099
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

LVL 27

Expert Comment

ID: 8157393
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

LVL 1

Expert Comment

ID: 8157419
meikl,

Ho, you are excellent. Keep in touch. Thank you very much.
0

LVL 27

Expert Comment

ID: 8157495
well, glad that it works for you ;-)
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, thâ€¦
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGridâ€¦
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediafâ€¦
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. â€¦
Suggested Courses
Course of the Month13 days, 3 hours left to enroll