Detail count in a calculated field

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
LVL 1
DissasterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
esoftbgConnect With a Mentor Commented:
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
 
esoftbgCommented:
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
 
esoftbgCommented:
download above example from:
page:        http://www.geocities.com/esoftbg/
  link:        Q_21126996.zip
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
esoftbgCommented:
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
 
DissasterAuthor Commented:
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
 
esoftbgCommented:
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
 
esoftbgCommented:
O.k., I did upload it
0
 
DissasterAuthor Commented:
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
 
esoftbgCommented:
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
 
DissasterAuthor Commented:
Yes, i guess it depends on the database i used, in this case it was MySql
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.