Counting and totalling child records

[Stephen Osborne]

I'm trying to count child records in a subscriber list. Each child record represents a renewal and is linked to the parent by an ID field. The report might look like this:

Year 2ChildRecs 3ChildRecs 4ChildRecs
Parent) (Count) (Count) (Count)

In other words: I need to find out many have renewed twice, three times etc, by counting the child records and totalling them for each year.

I've stymied myself trying to use dbcount. Any advise will be much appreciated.

--Stephen

[Jim Chapman]

RE: Counting and totalling child records

Hello Stephen,

Depends on what the context is. Do you want to show this count on a report, a form? on the fly or by command?

The following short Xbasic code when placed on a button's OnPush event on the Invoice form of the AlphaSports database will tell you how many child records each invoice has:

t = table.get("invoice_items")

vcount = 0
t.fetch_first()
while .not. t.fetch_eof()
vcount = vcount + 1
t.fetch_next()
end while

vstr = str(vcount)
ui_msg_box("the child records",vstr)

Good luck,
Jim

[Stephen Osborne]

RE: Counting and totalling child records

Thanks Jim: that's a very handy bit of code. But I'm trying to print a report that in your example would tell me how many invoices in any year contain a single child record, two child recs, three, etc. up to a max of five (so the report would consists of five data columns and a year column on the left.

--Stephen

[Tom Cone Jr]

RE: Counting and totalling child records

Stephen,

this looks like a case where basing the report on an intermediate, temporary, table makes sense.

You would write script that would:

a) zap the intermediate table
b) step through your records, one primary table record at a time; on each fetch a count of the current child table records would occur and a new record written to the intermediate table accordingly.
c) each record in the intermediate table would have the same structure as the fields in your report.
d) the report layout would group the records by year, and generate totals for each of the columns.

-- tom

[Scott Emerick]

RE: Counting and totalling child records

Stephen,

I might be way off base here based on the responces you already received, but I will throw my 2 cents in just in case.

Have you tried tablecount() ?

Numeric Number_Of_Records = TABLECOUNT( Lookup_Table as C, Filter as C )

You can create a filter to include the parentID and the subscriberID

This should give you a total count of how many records are in the child table.

Scott

[Jim Chapman]

RE: Counting and totalling child records

Hello Stephen,

>>But I'm trying to print a report that in your example would tell me how many invoices in any year contain a single child record, two child recs, three, etc. up to a max of five