Link to home
Start Free TrialLog in
Avatar of GundamGP01
GundamGP01

asked on

Calculat the sum of a column in DBGrid

Hello!

I have a DBGrid with some numeric fields, I want to calculate the sum of
the column when any data change, the change will not write to database until a key pressed, so I can't use another query to find the sum, could anyone give me some hints?

Thank.
Avatar of kretzschmar
kretzschmar
Flag of Germany image

hi gundam,

why can't u use another-query?
do u use cachedupdates?

meikl
Avatar of GundamGP01
GundamGP01

ASKER

Hi Meikl,

Yes, I use cachedupdates, so the data will not write to database until I applyupdate. At this time, the another-query doesn't got new data.

Gundam
hi gundam,

a sample how could it be done

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

type
  TForm1 = class(TForm)
    Table1: TTable;
    DataSource1: TDataSource;
    Query1: TQuery;
    DBGrid1: TDBGrid;
    DataSource2: TDataSource;
    Button1: TButton;
    Button2: TButton;
    Edit1: TEdit;
    procedure FormCreate(Sender: TObject);
    procedure Table1BeforePost(DataSet: TDataSet);
    procedure Table1BeforeDelete(DataSet: TDataSet);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

var sumval : double;  //holds the sum

procedure TForm1.FormCreate(Sender: TObject);
begin
  query1.open;
  sumval := query1.Fields[0].AsFloat;  //get initial sum with a query
  edit1.text := floattostr(sumval);
  query1.close;
end;

//Recalculate sum, you could also use the fieldbyname-method
procedure TForm1.Table1BeforePost(DataSet: TDataSet);
begin
  if (table1.State = dsedit) then
  begin
    sumval := sumval - Table1.Fields[0].OldValue;
    sumval := sumval + Table1.Fields[0].AsFloat;
  end;
  if table1.state = dsinsert then
    sumval := sumval + Table1.Fields[0].AsFloat;
  edit1.text := floattostr(sumval);
end;

//Recalculate sum
procedure TForm1.Table1BeforeDelete(DataSet: TDataSet);
begin
  sumval := sumval - Table1.Fields[0].AsFloat;
  edit1.text := floattostr(sumval);
end;

end.

hope this helps

meikl
That is very long...

Any simpler answer.. ?
That is very long...

Any simpler answer.. ?

Cause I also have many problems.on this too...
well yes,

there is another solution also,
in the afterpost and afterdelete event of the dataset iterate through all records and add the field, where the sum is wanted.

but, depending on the amount of records of the dataset, this will cost some more perfomance.

let me know, if a sample is needed for this

meikl
gundam, are you here?
Yes, I have try your solution, but having some problem

1. The field was a calculated field, without OldValue

2. Even if it is a data field, it will get the wrong result if modify more than onec. for example:

before change:
Fields[0].oldValue = 10.0, Fields[0].AsFloat = 10.0, sumval = 10.0,

if I change fields[0] to 20, the data will be:
Fields[0].oldValue = 10.0, Fields[0].AsFloat = 20.0, sumval = 10.0,

the sumval after calculation should be:
sumval := 10.0 - 10.0; sumval := 0 + 20.0 = 20 <- this is correct

if I change fields[0] to 15, the data will be:
Fields[0].oldValue = 10.0, Fields[0].AsFloat = 15.0, sumval = 20.0,

after calculation:
sumval := 20.0 - 10.0; sumval := 10 + 15.0 = 25 <- this is wrong!

because I am using CachedUpdates, the OldValue will not update after Post. Until I commitupdate.

I've think about your second solution, because I calculate this field in the oncalcfield event, it will be trigger when iterate through the records, which make my program loop infinitely.

Gundam
well,

must do a bit finetuning,
to avoid such effects you've
mentioned above.

i let you know when i'm ready

meikl
ASKER CERTIFIED SOLUTION
Avatar of kretzschmar
kretzschmar
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank, I will test it.

Gundam
hi gundam,
some results?
meikl
Thank for your solution.

I have use your concept but make some modification.
Because the field is a calculated field, I record the old value,
before assign new value, so the variable are local.
Also, do the calculation in both BeforeDelete and BeforeCancel event.

Gundam
hi gundam,

first thanks for accepting,
glad that you get it work
good luck again

meikl ;-)