Summary field to count unique values

[Gabe]

Hi

I need a summary field to count the number of unique values in a found set. Right now, all I can get it to do is count either all found records or "1".

Just to make sure I'm coming across clear...

if I have the following values in a found set

1

2

2

2

2

1

3

3

4

I want the summary count value to be 4

Help appreciated. Thanks.

[-Queue-]

Well, I would say use a calc field of 1 / GetSummary( summaryField; breakField ) and then a summary field to total the calc field, but 7 does not appear to update the final summary consistently, if it even updates it at all. So it may work best to script this.

[sbg2]

I don't know of any way to use the found set within a function. As Queue said, you may have to script the count. One way that sprang to my mind was to use the count of the items in a value list. For example if you could create a value list of the found records for your number field it would return:

1

2

3

4

even though the fields contained:

1

2

2

2

2

1

3

3

4

In other words a Value List filters out duplicate values. Seems simple! All you need is a count of the items in that value list. So what I came up with was to export the found set into a related table and used the related records to create the value list, example attached. Once those records are in a seperate table you can return the count.

I lifted part of the Database from an example Comment posted the other day. I have stripped it down to a database that just lists a few names. If you click the Find button on the Name layout it will begin the script to perform the find. Once the user clicks continue or hits enter to perform the find the records are imported into the VL_Name table, which in turn allows you to calculate the number of values in the value list.

ValueCount.zip

[Gabe]

Seems simpler to simply loop through the found set and count the unique values using a working value global field before navigating the user to the report layout. Thats what I ended up doing anyway.

Thanks for the advice though.