Solved

Running Total / Balance to display in DBGrid

Posted on 1998-04-29
5
1,003 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 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

17 Experts available now in Live!

Get 1:1 Help Now