Calculating the sum of a field in a DBGrid

[aitpeter]

Hi, I am new to this forum.
I hope that you can help me with a problem.

I have created order, orderitem, customer and product tables.
The tables have appropriate indexes so that I can link the keyfields.

On a Delphi form I want to show a bill with the orderitems in a dbgrid and in a seperate field the sum of salesprice of all the orderitems to a specific ordernr.

Browsing through the orders is working Ok.
The dbgrid shows the correct orderitems with a certain ordernr.
But calculating and showing the total sum of the bill won't work.

I have tried calculated fields, querys but nothing worked.

Please will anyone show me where to start to summerize the salesprice to show the total sum of the orderitems in a seperate field on the form while browsing through the orders table.

Peter.

 

[DjangMan]

One way is to call the detail line's dataset.disablecontrols, sweep through the details to total the amounts and then call dataset.enablecontrols.

OnCalcFields should work, however. Set it to manual and run a forwardonly query (or readonly depending on your database) to get the total of the related detail lines. This allows you to see those totals without needing a detail dataset.

 

[aitpeter]

Thanx for your reply.

I am a newbie with Delphi so will you please explain what you mean with "sweep through the details to total the amounts". Also "run a forwardonly query"?

I use for now paradox tables. But want to have the possibility to switch to MySQL database without changing a lot of code.

I have the detail dataset available because I show it in a form. Isn't there a possibility to sum a colunm in the detailed dataset?
I have searched in the properties of dbgrid but only found selectedRows. Isn't it just not possible or am I missing something beside extensive knowledge of Delphi?

 

[DjangMan]

A forward only query is a way of telling the database engine that your query will be travelling through the records in only one direction and that you're not making any changes to the database. That allows your query to travel through the records faster because the database engine isn't going to worry about locking records for you. The is available with ADO.

In your case you could use a TQuery to run something like:
'SELECT SUM(FieldToTotal) FROM YourDetailTable WHERE DocumentNo='XYZ'

The dataset itself doesn't have any built in function to total up records. You'd either run a query to get that total or go through the records yourself.

If you use something like:

try
  YourDetailTable.DisableControls;
  YourDetailTable.First;
  Total := 0;
  While not(YourDetailTable.EOF) do begin
    Total := Total + YourDetailTable.FieldByName('FieldToTotal').AsDouble;
    YourDetailTable.Next;
  end;
finally
  YourDetailTable.EnableControls;
end;

This code will run very quickly because of the Disable/Enable controls method calls. When you don't have to update the screen things can run pretty quick. But - if the use makes changes to the details you either have to keep track of them manually or re-calculate.

 

[aitpeter]

Thanx for your quick and clear reply.

Can I use your code example in a calculated field or should I use it in another event?

 

[aitpeter]

Dear DjangMan,

I have put your codesample to the onClick event of DBNavigator and it worked. Now I have to find out where to put it so that the sum is calculated when the form is displayed and the DBNavigator bar isn't clicked. I still have to learn a lot.

Thanx very much.

 

[roo0047]

@aitpeter - The differences between Paradox and MySQL are so great that you would be wasting your time to learn all the quirky attributes of Paradox. It is VERY slow compared to MySQL. I don't mean 2x or 10x slower, I mean 100x slower in some cases. You are better off to learn SQL from the get-go rather than to be confused by all the differences.

All you need to learn then is the SQL language itself. With MySQL, you just pass SQL commands off via a Delphi TQuery, just like DjangMan demonstrated in his 2nd paragraph. With Paradox, you have no choice but to use things like he demonstrated in the "Code" snippet he also posted.

Paradox is very old and all but dead. You will find much more help and examples with SQL.

Oh, and welcome to the forum!


Roo
Delphi Rules!

 

[DjangMan]

I would still recommend using a calculated field and controlling the value in the OnCalcFields event. By doing that you can use a regular DB aware control and it will work on reports, grids, etc.

I would agree with Roo: Go the SQL route if you can. MySQL, MSSQL, Firebird and even MS Access. All have free versions that you can get confortable with.