Solved

Detail count in a calculated field

Posted on 2004-09-10
10
219 Views
Last Modified: 2010-04-05
I have two datasets bonded in a Master-Detail relationship. Is there any way to display the Detail's record count as a calculated field in the master dataset?

I already tried the following code on the master's oncalcfields event:

    MasterTblDetailSumaryField.value := DetailTbl.RecordCount;

but it gives me the total record count of the detail dataset in all the records.

Any help would be appreciated. Thank You
0
Comment
Question by:Dissaster
  • 7
  • 3
10 Comments
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
unit Unit1_Q_21126996;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, Grids, DBGrids, StdCtrls, Buttons, ComCtrls,
  ExtCtrls, JPEG, DBCtrls, ExtDlgs, Clipbrd;

type
  TForm1 = class(TForm)
    dstMTV: TDataSource;
    DBGridMTV: TDBGrid;
    tblMTV: TTable;
    QueryCount: TQuery;
    DBGridPHOTO: TDBGrid;
    tblPHOTO: TTable;
    dstPHOTO: TDataSource;
    tblMTVID: TAutoIncField;
    tblMTVNAME: TStringField;
    tblMTVAGE: TSmallintField;
    tblMTVCOUNT_PHOTOS: TIntegerField;
    DBImage: TDBImage;
    DBNavigator1: TDBNavigator;
    procedure FormCreate(Sender: TObject);
    procedure tblMTVCalcFields(DataSet: TDataSet);
  private   { Private declarations }
  public    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
  tblMTV.Active := True;
  tblMTV.FieldByName('ID').Visible := False;
  tblPHOTO.Active := True;
  tblPHOTO.FieldByName('ID').Visible := False;
  tblPHOTO.FieldByName('MTV_ID').Visible := False;
  tblPHOTO.FieldByName('PHOTO').Visible := False;
end;

procedure TForm1.tblMTVCalcFields(DataSet: TDataSet);
var
  Count: Integer;
begin
  Count := 0;
  try
    QueryCount.Active := False;
    QueryCount.SQL.Text :=''
                        + ' SELECT COUNT(*) FROM PHOTO.DB'
                        + ' WHERE MTV_ID='
                        + tblMTV.FieldByName('ID').AsString
                        + '';
    QueryCount.Active := True;
    Count := QueryCount.Fields[0].AsInteger;
  finally
    tblMTV.FieldByName('COUNT_PHOTOS').AsInteger := Count;
  end;

end;

end.
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
download above example from:
page:        http://www.geocities.com/esoftbg/
  link:        Q_21126996.zip
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
Under some Databases it would be:

procedure TForm1.tblMTVCalcFields(DataSet: TDataSet);
var
  Count: Integer;
begin
  Count := 0;
  try
    QueryCount.Active := False;
    QueryCount.SQL.Text :=''
                        + ' SELECT COUNT(*) FROM PHOTO.DB'
                        + ' WHERE MTV_ID='
                        + ''''
                        + tblMTV.FieldByName('ID').AsString
                        + ''''
                        + '';
    QueryCount.Active := True;
    Count := QueryCount.Fields[0].AsInteger;
  finally
    tblMTV.FieldByName('COUNT_PHOTOS').AsInteger := Count;
  end;
end;
0
 
LVL 1

Author Comment

by:Dissaster
Comment Utility
ok..  must shamefully admit that it didn't ocurred to me that a sql might solve my problem, but woldn't it become a resource hog to use that on large detail tables? retrieving from the DB all the detail records every time the field is calculted?
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
Don't worry about you don't guess for using SQL ....
You are right, it would be provoked some difficulties on a large master and detail tables using calculated fields....
I'll try to avoid that, please wait ....
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Accepted Solution

by:
esoftbg earned 250 total points
Comment Utility
The solution is using a JOIN into the master Query (instead of table). This way we avoid Calculated field:

 SELECT m.ID, m.NAME, m.AGE, COUNT(p.ID) AS COUNT_PHOTOS
 FROM MTV m
 JOIN PHOTO p ON p.MTV_ID = m.ID
 GROUP BY m.ID, m.NAME, m.AGE

I will upload the example after a few minutes to the above link
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
O.k., I did upload it
0
 
LVL 1

Author Comment

by:Dissaster
Comment Utility
It worked fine, just with a slight modification:

SELECT m.ID, m.NAME, m.AGE, COUNT(p.ID) AS COUNT_PHOTOS
FROM MTV m
LEFT JOIN PHOTO p ON p.MTV_ID = m.ID
GROUP BY m.ID, m.NAME, m.AGE

With the simple JOIN i couldn't saw themaster records that didn't had detail records.. it was solved using LEFT JOIN
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
O.k. it may be depends on what kind of database is in use. I tested it with Paradox. Your database is probably different ....
Well, I am glad that it works fine after you correct the code !
Emil
0
 
LVL 1

Author Comment

by:Dissaster
Comment Utility
Yes, i guess it depends on the database i used, in this case it was MySql
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

10 Experts available now in Live!

Get 1:1 Help Now