Getting counts from detail tables
I have multiple record bock that displaye a list of cell ids and their attributes; each record also displays record counts of its 3 detail tables.
Originally, the POST-QUERY trigger select the counts (3 detail tables) as part of the data block for each record (or cell id). There are over 16K records, so each record query issues 3 selects. The whole query toke over a minute. I was asked to improve the retrieval time.
At first I thought perhaps I could put the 3 counts in 3 separate data blocks and issue one query each in a "select cell_id, count(*). . . group by cell_id" but scrolling would not synchronize with the other blocks. So I dropped that idea.
Next, I added a program unit from which the WHEN-NEW-FORM-INSTANCE trigger calls after execute_query. It issues "select cell_id, count(*). . . group by cell_id" (one for each detail table) and fills the count columns in the original data block. However I need to set the block property Query All Records to YES so that the cell ids and attributes are filled first. That means that before the form comes up it has to retrieve all records. That saves me about 10-15 seconds od retrieval time and I lose the ability to quickly bring up the form.
Then I looked at using dfferent Query Data Source Types. I don't think I could do one query with counts of detail tables. Besides, some items (attrinutes) of the cell are upate-able.
Any ideas and suggestions will be appreciated.