Solved

Running Total / Balance to display in DBGrid

Posted on 1998-04-29
5
1,042 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
[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
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 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