Solved

Quickly determine number of detail records and add to calculated field

Posted on 2004-08-10
13
241 Views
Last Modified: 2010-04-05
Hi,
I want to quickly determine the number customers and use that number at the Company level.

Now I solved it in the OnAfterScroll-event of the Company (with SQL)
When loading the company table into a grid this takes forever, because for each record the SQL must execute.

Does anybody know a fast solution for this?
Please supply some code.

Thanks Stef
0
Comment
Question by:Delphiwizard
  • 6
  • 4
  • 3
13 Comments
 
LVL 17

Accepted Solution

by:
Wim ten Brink earned 300 total points
ID: 11761718
select count(*) from detail where DetailID=MasterID

The ID's will represent the link you have between master and client. And if you select the records for the master, you could include this count as part of your master-query.

Another option would be if your master-table and detail table are linked together. Then walking through the master table will update the detail table immediately. The DetailTable.Recordcount should tell you the number of records the detail table will have at that moment.

But you want count to be part of your master-query so you must add the Count(*) to your master-query. Perhaps something like:

select Master.Whatever, count(Detail.DetailID)
from Master, Detail
where Master.MasterID=Detail.DetailID

Make sure that DetailID is identifiable as a field of the detail table. I just did that by using the tablename as prefixes.
0
 
LVL 4

Assisted Solution

by:SurferJoe
SurferJoe earned 200 total points
ID: 11765485
procedure TForm1.wwClientDataSet2AfterScroll(DataSet: TDataSet);
begin
// After Scroll  works good, Before Scroll was resetting the Datasets Eof
// used else where in the code
  Label1.Caption :=
    'Record ' + IntToStr(TCustomADODataSet(DataSet).RecNo) +
    ' of ' + IntToStr(TCustomADODataSet(DataSet).RecordCount);

end;

0
 

Author Comment

by:Delphiwizard
ID: 11771298
Hi SurferJoe,

The way you discribe it, I will probably see the Label1.Caption displaying all different counts while loading the dataset into the grid.
For each afterscroll once.
Can that be surpressed till the dataset is fully loaded?

0
 

Author Comment

by:Delphiwizard
ID: 11771480
And what do you mean by adding a where or else in the code?
Can you give me an example?
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 11772195
I suggested adding a where-clause. :-)

Basically, you could select the master records by using a query. That way, you could also include a count of all it's children in the master table. That way, calculating the number of children becomes just part of your master record. Quite nice if you display this in a grid, since every record will have a field telling you the number of child records.
0
 

Author Comment

by:Delphiwizard
ID: 11772298
And how do I update changing I made to the records in the grid?
I know how it works with TADOTable, but not with TADOQuery.
What properties must be set?

Building the SQL is not a problem for me.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:SurferJoe
ID: 11772353
Just try it you’ll like it.
0
 

Author Comment

by:Delphiwizard
ID: 11773497
Thanks for your effort to both of you.
Stef
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 11773851
In general, to make updates, just use INSERT or UPDATE queries. That would be the better option anyway if you use an SQL-based database. Besides, with ADO, queries are slightly faster than Tables anyway.

Still, ADO is pretty smart internally. It might be able to still update the data. :-) Not all ADO queries become read-only.
0
 
LVL 4

Expert Comment

by:SurferJoe
ID: 11774146
Typically I would start with and ADOConnection component, connected to an ADOQuery, or ADOTable then a DataSetProvider connected to a ClientDataSet, finally a DataSource and DBGrid or other data aware component.  If you want to use ADO this is the best solution I have found.

See this tread for a little more;
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21083190.html
0
 

Author Comment

by:Delphiwizard
ID: 11774202
What does a DataSetProvider and ClientDataSet do?
I've never used that.
0
 
LVL 4

Expert Comment

by:SurferJoe
ID: 11774464
These components where developed for Midas/DataSnap multi-tier applications. However the middle tier application server is not required and is left out in our implementation.
In a nutshell the ClientDataSet client side in memory capabilities significantly improves performance by limiting round trips to the server. In a SQL Server environment this also eliminates conflicts/blocking between users. The developer also has a lot more control over WHEN data is actually posted back to the server. This can be a good, sometimes not. The dataset provider is part of the whole modal with out going into to much detail.

Just make sure to limit the fields in where clause to avoid posting issues. Typically I would use ONLY the Key field in the where clause, but make sure not to use a Key/Identity field in the update clause. Also in SQL environments it is very important to trim the milliseconds off of DateTime fields. Otherwise the client thinks another user has changed the record and will report an error, if there is any delay in server response during a post. That was a real headache to figure out.

Good luck,

G
0
 

Author Comment

by:Delphiwizard
ID: 11774523
Thanks for the explanation. Stef
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Intraweb submit form as a POST request 4 264
Run video youtube webbrowse 10 58
Working with hours 3 46
Multiple image collision 13 69
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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 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

24 Experts available now in Live!

Get 1:1 Help Now