Jump to content

How to count 1 occurance of many records?

This topic is 7478 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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):


Link to comment
Share on other sites

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

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


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


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



Link to comment
Share on other sites

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. smile.gif


Link to comment
Share on other sites

This topic is 7478 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.