Can you count child records with out a filter?

[Neil Hucker]

Hi,
I've been racking my brain trying to figure out how to count the number of child records for a specified parent record with out having to apply a filter.

In a simplified scenario, I've Table A (parent) and Parent B (child) that make up a set, with a One to Many relationship for Table A to Table B. When viewed in a form, I've one parent record displayed in text, with many child records displayed in an embedded browse.

Through some sort of script I want to be able to count the number of child records displayed in that embedded browse with out having to run a filter query, (trying to utilise the set). If the records are right there in front of me, you should be able to count them right?

I'm trying avoid a filter so I can maintain a speedy database. Two methods I've already tried so far are

1) attach a pointer to the current set "set".current, which I was hoping would restrict the data to what is currently being viewed. I then open a table pointer on table B, using
table_pointer = set_pointer.TableB , from which I then tried both Fetching through the records and the Table.Records_get() function. The count was not succesfully restricted to the particular records of the parent record.

2) I tried to count the records that were attached to the form object.
rec_num = topparent:browse1.Records_Get()
The script counted all the records that were available to the form instead of the records currently displayed in the browse.

Is there no way to count the records in the embedded browse that are displayed right there in front of me?

[Tom Cone Jr]

RE: Can you count child records with out a filter?

Neil,

If you don't mind moving the record pointer in the linked child table browse you can count them like this:

tbl = table.current(slot_number)
tbl.fetch_first()
counter = 0
while .not. tbl.fetch_eof()
counter = counter + 1
tbl.fetch_next()
end while

or you can use dbcount() to get the same information without moving the record pointer.

-- tom

[Barry Potter]

RE: Can you count child records with out a filter?

Neil
Another approach would be to highlight whichever field in the embedded browse that you wanted to count and then use
compute_field_statistics() which would be assigned to a variable.
Barry

[G Gabriel]

RE: Can you count child records with out a filter?

Neil:
"trying to figure out how to count the number of child records for a specified parent record with out having to apply a filter."

If you are presenting the records in the embeded browse with their recno's, which is hardly ever the case, then the easiest way is to:

a-either get the recno of the last record and that would be the total number odf records:
tbl = table.current()
tbl.fetch_last()

Or better
b-Calc field: total_records=Record_Count("child")

"I want to be able to count the number of child records displayed in that embedded browse with out having to run a filter query"
If the set link has a filter, then the child table is presented according to that filter if none else is applied.
To count the records shown based on the link filter, you do not need to run any additional queries, simply create a calc field:
dbcount()
Sure, in dbcount() you have to specify a filter, which would be the same filter of the link, or you could use .t. for all records.

"I'm trying avoid a filter so I can maintain a speedy database"
"Is there no way to count the records in the embedded browse that are displayed right there in front of me?"
Although I have not tested this, I am pretty sure any script you write to go down the list and count the records will be slower than the built-in alpha dbcount() function.

The simplest script though, would be:
t=table.open("child")
count=child.RECORDS_GET()

I suggest you try dbcount() first.

Gabe

[Neil Hucker]

RE: Can you count child records with out a filter?

Thanks for some of the tips,
I tried to test some of these solutions and they pretty much do what I what them to do. Mainly the dbcount() and the compute_field_statistics() were the most usefull.

Gabe when you suggested that you could use .t. for all records with the dbcount() function, where exactly do you insert that parameter in the following;
Number_of_Records = DBCOUNT( Lookup_Table as C, Index_Tagname as C, Key_Value as C )?

I do like the idea of using this function in a calculated field because it means it will be updated as the form is updated with out having to script for any other events or buttons.

I also tried Barry's suggestion of using the compute_field_statistics(), which I also found very usefull. But it does seem that it would need to be tied to an event script. It would be very handy if I could also make this function run in a calculated field and output the count dot variable. I unfortunatley couldn't work out how to do this. I'm not exactly sure if compute_field_statistics() can be used in an expression at all?
I also tried some fetching methods before my original post, and found it resulted in incorrect results, but I've since found that was resolved after rebuilding the index. A similar thing happend with the dbcount() function.

The compute_field_statistics() seems to be more likely to output the results that can be physically counted in the browse, and may be less likely to affected by index indiscretions. Does anyone have any advice on good practices for keeping the indexes up to date in a database application?

[whanigsberg]

RE: Can you count child records with out a filter?

Neil,

You already have a query on the child records--the one imposed by the form. The problem (as you say) is how to refer to it.

The following code goes on a button on a form based on a set. The child table is "transactions". Everything else is generic.

-------------------
dim t as P
dim ndx as P
dim nrecs as N
dim msg as C

t=table.get("Transactions") 'pointer to the child table
ndx=t.index_primary_get() 'pointer to query on the child
nrecs=ndx.records_get() 'count the records in the query

msg=alltrim(str(nrecs))+" child records"
ui_msg_box("Parent has",msg,UI_INFORMATION_SYMBOL+UI_OK)

end
-------------

You could attach similar code to the form's on_fetch event and display the result in a form-level calculated field.

Bill

[whanigsberg]

RE: Can you count child records with out a filter?

Alas I didn't test enough.

For some reason the code I posted is unreliable--works sometimes and sometimes doesn't.

I'll get back to you tomorrow.

Bill

[G Gabriel]

RE: Can you count child records with out a filter?

neil:
I am sorry, I was thinking of tablecount()
count=tablecount("table_name",".t.")
Gabe

[whanigsberg]

RE: Can you count child records with out a filter?

Back again,

It really should be possible to use the current query but I can't get it to work properly. I bet somebody will see what I'm overlooking.

For the meanwhile, I can't do better than Gabe's suggestion of dbcount().

This is the button code:

---------------
dim vKey as C
dim nrecs as N
vKey = topparent:mbrid.value
nrecs = dbcount("transactions","Mbrid",vKey)

msg=alltrim(str(nrecs))+" child records"
ui_msg_box("Parent has",msg,UI_INFORMATION_SYMBOL+UI_OK)
---------------

I wouldn't be worried about any speed hit as this is an optimized operation based on an index using a compiled function.

Bill

[Tom Cone Jr]

RE: Can you count child records with out a filter?

gabe's suggestion?

Bill,

I thought it odd that a pointer to the child table would not reference the current query condition in the form, but that's what I found when I tried this...

tbl = table.current(2)
curr_count = tbl.records_get()

Instead of the count of the currently linked child table records, I got the count of all records in the table. Hence my suggestions to use dbCount() or to simply step through the records and accumulate a total.

-- tom

[Tom Cone Jr]

RE: Can you count child records with out a filter?

Bill,

consider this feature request:

http://msgboard.alphasoftware.com/alphaphorum/read.php3?num=17&offset=0&thread=372&sortby=lastreply&direction=desc&id=785

-- tom

[Steve Andrews]

RE: Can you count child records with out a filter?

I have used get_field_statistics() as mentiioned above with luck in determining the number of child tables. Assign a variable (say, "nChild_Rcrds") to the form and within the OnFetch script simply put:

----------------
answer.count = 0
tbl=table.get("Child_Table_Name")
tbl.field_statistics("Some_Child_Fieldname",answer)
nChild_Rcrds = answer.count
----------------

This has been reliable in providing me the number of child records. If I'm missing something please let me know.

Steve

[Barry Potter]

RE: Can you count child records with out a filter?

Hi Steve
With the field statistics method you can also get field totals and averages etc., plus it is instantaneous.
Barry

[G Gabriel]

RE: Can you count child records with out a filter?

Neil & all:
Wow !!
This thread is branching out to many mini-threads! I just opened my email to find a slew of messages!

As I started reading some of these messages, it just dawned on me that we ALL, myself included, got it wrong, so wrong it's embarrassing !! We just completely mislead the author.

Irrespective of the "brain-block", my suggestion of dbcount() or tablecount() will, unwittingly, do the job, although, I must admit I was not thinking straight!

Let's go over what Neil wants:
1-He has a form based on a 1-many set
2-When he opens that form, he has a record showing values from the parent table and an embedded browse showing records from the child table
3-He wants to know if he could, without a filter, count the number of those records in the browse!

Let me show you how silly and how wrong we all got it:
UNLESS Neil has only ONE record in the parent table that connects to ALL records in the child table, then counting all the records in the child table by field statistics or recno or any other alternative means, is meaningless, unless ofcourse, you add a filter. As he scrolls through the parents records, that number will remain constant although one parent record might have 5 child records while the next one might have a dozen !!

Get it?

I got urgent business to attend to. I have a feeling when I get back, there will be yet another dozen messages and maybe, some hate mail!

Later!

Gabe

[Steve Andrews]

RE: Can you count child records with out a filter?

...UNLESS Neil has only ONE record in the parent table that connects to ALL records in the child table, then counting all the records in the child table by field statistics or recno or any other alternative means, is meaningless...

Gabe,

Neil does say that he has "one parent record" displayed in the form with an embedded browse of linked child records. True, the field statistics will not provide a count of all child records in the child table. But it will provide the number of child records linked to the one parent record being displayed. That is why I suggested that he pout my code within the OnFetch script. If I understand what he is after correctly, that will suffice.

Steve