Jump to content

Counting unique values in a field


darrel
 Share

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

Recommended Posts

Hi,

After having a good look around I can't seem to find the answer to what seems a simple problem.

I need to count the number of unique occurrences in a field (especially after performing a find for a report).

For example, if the records show

customer1

customer2

customer1

costumer2

I need filemaker to calculate that there are actually only two unique entries in this field.

I've tried the count function, summary fields and calculations but haven't gotten anywhere! Any help or suggestions would be greatly appreciated.

Thanks

Darrel

FileMaker Version: 6

Link to comment
Share on other sites

Hi,

If data in that field is always separated by Carriage returns, then you can create a Value List from this field, and retrieve its content with the ValueListItems() function.

This function would omit duplicate, thus running a count of "

Link to comment
Share on other sites

Yes,

You can create a value list on that field

- For all records, by selecting "Value List from Field"

- For related records by selecting "Related".

If your relationship is ID::ID, only the values for the current record would be available, while with a relationship gID::ID (with gID filled with IDs separated by Returns), all values in that field in those records which ID is stored in gIDs would show, without duplicates.

HTH

FileMaker Version: 6

Platform: Mac OS 9

Link to comment
Share on other sites

Thanks for the expert help.

I created an unstored calculation field which was

PatternCount(ValueListItems("mydatabase.fp5", "myvaluelist"), par.gif)

This was great as it counted how many unique values there were in the whole database.

Your suggestion for the relationship was where I got stuck!

I created another calculuation field to store all the unique IDs

ValueListItems("mydatabase.fp5", "myvaluelist")

I called this gID. I then did a relationship to match this field with the actual field I was wanting the unique values from gID::ID. (Is this how it was meant to go? It didn't seem to work for me - I can't see what I'm missing)

I then placed the my first calculation field on the layout based on the above relationship.

If anyone can help me further it would be much appreciated.

Kind Regards

Darrel

FileMaker Version: Dev 6

Platform: Mac OS X Panther

Link to comment
Share on other sites

Darrel,

The ID is a unique Identificant for each record in your database. It could be a serial.

Say you have a foundset of 5 records which IDs are ID0012,ID0016,ID0021,ID0054 and ID0070.

If you want to list and count the unique values in FieldX for this foundset only, you need to store these IDs in a field, separated by carriage returns. Formatted this way, you'd be able to build a relationship from this field to your ID field.

This was the purpose of the gID field, which is a global text field.

So gID would be :

ID0012

ID0016

ID0021

ID0054

ID0070

Here are the exact settings :

1. Create a relationship "FoundSetMatch", using the gID field for Left side and the ID field for the right side.

2. Define a ValueList "ValuesOfFieldXInrFoundSet", which would use this relationship.

3. Create a Calculation

c_UniqueX = PatternCount(ValueListItems(Status(CurrentFileName),"ValuesOfFieldXInrFoundSet"),"

Link to comment
Share on other sites

Ugo,

Thanks once again for such a detailed and expert response. I appreciate the amount of time and effort that you and others here at fmforums put in towards helping other users.

This is why fmforums.com is the most useful/helpful site for filemaker users on the net cool.gif

Cheers

Darrel

Link to comment
Share on other sites

This topic is 6475 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
 Share

×
×
  • Create New...

Important Information

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