How to count 1 occurance of many records?

[SassyTexan]

Here is my question, I hope that I ask it properly. I have a related db, 1 file has many records and many of the records have the same information, however, I only need 1 occurance of each counted. I am at a complete loss as to how to accomplish this. It is probably quite obvious, but I am lost.

[jscooper]

This is how I've done it:

Make a Value List, using the key field in the related file. Then define an unstored calculation counting the number of cariage returns in the value list and add one. For example:

Two files: Main.fp5, Related.fp5

In Main.fp5, your relationship to Related.fp5 is "MainID =::MainID" ("Related" is the name of the relationship)

Also in Main.fp5, make a value list called "UniqueRelatedrecords", define it as follows:

Use values from Field -- Only related values: Related, use values from field: MainID

FileMaker automatically builds value lists like this, omitting duplicate values. So, a calculation like this:

ValueListItems(Status(CurrentFileName),"UniqueRelatedrecords") should return all the items in the list. The following calculation should give you a count (add 1 since the last item in the list has no return after it):

PatternCount(ValueListItems(Status(CurrentFileName),"UniqueRelatedrecords"),"

[SassyTexan]

OK, I tried that and I got a number of all single occurances, however, now how do I use that in a subsummary? Ex. John Doe has an ID of 001 and is in Ca. How do I count all of the individuals in Ca.? Is this as clear as mud?

[jscooper]

I'm assuming you have state in your main file and

names in the related one. If not, let me know. If

so...

Generate the report in the related file. If you

haven't already, set up a reverse relationship back to

the main file (use the same fields that you used to

connect Main to Related and just go in the other

direction). Then, create two subsummaries in a new

layout:

Subsummary 1 should be sorted by Main::State. In it,

put the fields "Main::State" and "Main::Counter" (or

whatever you called your counting field.

Subsummary 2 should be sorted by Name.

Note that if you stick another subsummary between the

two you'll have to change the relationship to use THAT

field (or a combination of fields), since Counter is

returning ALL distinct names in the Related file,

regardless of how it's sorted and regardless of the

found set.

Maybe there's another way to do it (somehow

summarizing a summary?) -- but it's Monday and my

brain isn't awake yet -- if I think if it I'll let you

know.

Jeff

[bruceR]

Not really sure what you're after. That's what standard sub-summary reports do.

[LaRetta]

Hi Sassy,

Create your Columnar List/Report. Select Grouped with sub-totals. Specify fields State, Name (John Doe, etc). Organize by STATE, sort by STATE. On Summary fields, 'Create Summary Field, name it and COUNT your STATE field. Be sure to click ADD sub-total and specify 'Below.'

Theme and Header footer selection as you desire, but on Sort - you must again specify sort by State. And I suggest you allow FM to create the script for you. In this way, it'll create the Go To Layout [Your Columnar Layout] and the specific sort and store it within the script.

Now, if you don't care about getting the details but only want a list showing State with the total count next to it, delete the body and move the Count Summary to the leading Sub-Summary and you'll be set.

If I've missed your point, please say more. We'll help you through this.

LaRetta