I'm trying to create a field that displays the count of listed items in a Sub summary report in preview mode.

Example: If I have 10 records, 3 with the name "Joe", 2 with the name "Bill" and 5 with the name "Sue" and I create a preview report listing a summary of names, I would get a list of 3 names...

Joe

Bill

Sue

I'm in trying to create a field that states "3" for the number of items listed.

I tried creating a self join of the "names" field and then created a counter field to identify unique names, but that only works if you do a report on the whole db not the found set.

I've attached a example pdf of the report.

I hope this make some sense. Any advice is much appreciated!!

example.pdf

You don't actually need to define any relationships to do this, just one summary field. Create a summary field defined as the count of the field "Names" and put it in your subsummary part. Then sort by names. The count will appear for each person.

That doesn't seem to work for me. Not sure if I'm doing something wrong. Attached is my test file.

Try this route. It calculates the sum of unique names. Each record has an auto-entered id. Then, using the self-relationship based on 'name', it marks the first occurence of each name with 1 and the remainder with 0. Then it totals the calc results and voila!

Note: Many people would often suggest using Min() for this sort of purpose. However, since a relationship's default sort order is by order of creation, you can simply compare the current record's serial to self::serial without the extra overhead.

It Works! I have been racking my brains on this one for months! thanks for your feedback!

Thank Ugo, since he introduced the concept to me, and Andy Gaunt for introducing it to him.

Woo is me. I don't think this is working the way I had hoped. I noticed if you do a search by date, say the month of June, and all the records in that month are not the orginal (they generate "0" for Dupe), the sum number does't come out correctly. Any suggestions?

See Attached

Sorry here is the attached...

I had a look at your original file that you posted (using summary fields), and can't find anything wrong with it, except that maybe you were not sorting the file by the name field. I added a little script that sorts and then goes to preview mode. See the attachment.

I have nothing against using self-join type summaries like Queue has posted, but if you only need the summary to show in preview mode, or on a printed report, summary fields are simple and reliable.

test.fp5.zip

As you referred to a self-join, Queue probably assumed that you wanted this list to be permanent.

Foundsets and relationships are rather different.

You could have a relationship returns exactly the same records that the foundest, but this would need some work on the keys (i.e. concanating the Name and the Month Number here).

As you may need to input other find criterias, I'd also go for the Subsummary report here.

The file you attached is getting "20" for the sum. This is not what I'm looking for. I'm looking for a "6" which is the number of unique names, if you do a search for all records, see attached.

The "test 2" file works, but only if you do a "show all records". If you do a search by date, it doesn't see the original (1) record unless it was created during the date range.

Create a c|sum_by_name calculation number field 1/GetSummary( count, name ) and a sum|unique summary field which is a Total of the c|sum_by_name field. Put this summary field on your report.