Solved

Running Total / Balance to display in DBGrid

Posted on 1998-04-29
5
1,016 Views
Last Modified: 2010-04-03
I am using Delphi 3 client/server and am developing an accounting system using paradox.(Will probably convert to Interbase)
I have a table with the following fields:
Date, DocNo, Item, Increase, Decrease.
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).

I have tried to use a calculated field and the oncalc event but I don't know what the expression should be, I also tried a query but again don't know what to do there either.

(I previously used MS Access which made this running total very easy, but converted to Delphi for its speed, power and RAD?)



0
Comment
Question by:dirks
5 Comments
 
LVL 3

Expert Comment

by:d4jaj1
ID: 1339283
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;
0
 

Author Comment

by:dirks
ID: 1339284
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.  
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1339285
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...
0
 
LVL 1

Accepted Solution

by:
cmain earned 220 total points
ID: 1339286
I don't mean to cause trouble, but what about using a query.

SELECT DISTINCT MyTable.VALUE, (SELECT Sum(VALUE) FROM MyTable as MyTable_1 Where MyTable_1.Identity < MyTable.IDENTITY) AS EXPRESSION
FROM MyTable, MyTable AS MyTable_1;

Table Looks Like This.
IDENTITY     VALUE
AutoInc      50
             50
             50

This will give you a running balance. Now just bind your query to the grid, and you'll have the running balance.

Hope this helps.
0
 

Expert Comment

by:AG112297
ID: 1339287
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

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle global variables 4 63
Error E2158 compiling with Delphi XE10 Seattle 2 87
how to send memory stream from ics Client To ics server ? 11 81
DBGrid or StringGrid ? 6 70
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now