using count in a calculated field question

[martinwcole]

odd scenario
have an embedded browse where the same client may appear 1 or more times

need a count of customers, but unique for the client_id - only count each client_id once

since the number of records can change, as they dynamically filter the records, it has to be a (form) calculated field

can't figure a way to do it

any ideas?

[Tom Cone Jr]

Re: using count in a calculated field question

You need the count of unique customer_id's in the browse, right ?

First thought here is that the set design is causing the problem. Could the set be inverted? What do the records have in common? i.e. what's linking them to the parent table record?

-- tom

[martinwcole]

Re: using count in a calculated field question

right
when they first open the browse, all records show, and client_id "12345" may be in there say 4 times

then they may query where the first letter of the client name is "A" and thus the records displayed change, and thus the count of records

is based on a single table

I can easily set a form calculated field as count(client_id), but can't figure out how to make the count unique

[Tom Cone Jr]

Re: using count in a calculated field question

Ok. My guess is not bad. The data isn't normalized. Probably should be two tables, right?

[martinwcole]

Re: using count in a calculated field question

yes, is normalized

image a form with an embedded browse on the table invoices - one row for each invoice per client

now say they filter for year(invoice_date)=2010

and they want a count of clients who had invoices for that year

and then filter for Dallas, and a count for customers in dallas who did business

[Tom Cone Jr]

Re: using count in a calculated field question

I see.

A brute force "count" could be done using the relatively new <tbl>.get_cursor() method. You'd then fetch through the records in the current "view" and aggregate your count.

[martinwcole]

Re: using count in a calculated field question

thanks Tom
I think I will let them initially open the form prefiltered on a single year - which in this case would work, as there is only one record per year, and if they open with "All" hide the calculated field count(client_id)

although, as I think about it, since the temp table is created with a summary, I could make a summary on client id, and link the 2nd temp table to the first by client id, and display a calcualted field on that table