Running Total / Balance to display in DBGrid

I've found it's best to add/sum values in a detail table when two events occur 1) when the master record changes or 2) you save a detail table.  Table1 below is the master table & table2 is the detail. The following example also displays the value in a label, however, you can easily change the value in an edit field or directly to the table (TablenameFieldname.value := count).  Any questions, please add a comment.  Jay


procedure Tform1.Datasource1DataChange(Sender: TObject; Field: TField);
var
Count: Integer;
begin

if (table1.recordcount > 0) then
  Begin
    Count := 0;
    table2.disablecontrols;
    table2.first;
    while not table2.EOF do
      begin
      if table2.FieldByName('FIELD_TO_ADD').asInteger <> 0 then
       Count := Count + 1;
       table2.next;
      end;
      table2.first;
      table2.enablecontrols;
      label1.Caption := Inttostr(Count);
  end
else
 label1.Caption := '';
end;

procedure Tform1.table2AfterPost(DataSet: TDataSet);
var
Count: Integer;
begin
dbiSaveChanges(table2.handle);
Count := 0;
table2.disablecontrols;
table2.first;
while not table2.EOF do
  begin
    if tblHistory.FieldByName('FIELD_TO_COUNT').asInteger <> 0 then
       Count := Count + 1;
    table2.next;
  end;
table2.enablecontrols;
label1.Caption := Inttostr(Count);
end;

The user will constantly add new records to this table using the dbgrid.
The dbgrid is indexed on field "date" (secondary index)  This means that when the user add a record at the end of the grid the record may move to a other record position in the grid.

I don't want a single total for the total grid but the balance after each record (as sorted on the index)
eg.
Date       DocNo Item       Dec   Inc  Balance
01/01/1998 T0001 Stationary 100   0    -100
02/01/1998 D0001 Sales      0     50   -50
02/01/1998 D0002 Sales            150  100
05/01/1998 T0002 Interest   15    0    85

My feeling is that is must be something to do with the calculated field, but the procedure that I have wrote only works when I first op the table/grid, but not after records were inserted.  

Hi dirks,

Let's try to work it out

First I don't think it's such a great way to implement it as a calculated field, because you always need the value of the previous value. So I suggest the following :

Use a new table, which is always filtered on the date previuos to the one which is active and then the calculate balance

I display/Edit this table using DBGrid, but what I also would like to display in the DBGrid is the balance after each transaction (Increase + (Decrease * -1) + previous record balance).

set autocalcfields of table to true.

procedure TForm1.Table1CalcFields(DataSet: TDataSet);
     cBalance : Double;
begin
 with DataSet do begin
   cBalance := FieldByName('Increase').AsVariant +  FieldByName('Decrease').AsVariant * (-1);    
 end;
  FieldByName ('Balance').AsVariant := cBalance + NewTable.FieldByName('Increase');
end;

Regards, Zif.

PS. Use bookmarks if you don't want that the active record moves when doing calculations...


Hope to hear something of you...

Maybe it is handy to post some of your source here...

I was faced with a similar situation and found the easiest way was to create my own component. It was my first component so it is rather simple. You can download the source and use it as an example if you wish to go that route.

http://www.geocities.com/SiliconValley/Vista/1742/tquerycalc.html