Counting unique values in a field

[darrel]

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

[Ugo DI LUCA]

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 "

[darrel]

Thanks. I'll give this a try. However, the values are on separate records in the same field (they are not all in the one field). Does this still make it possible?

TIA

Darrel

Platform: Mac OS X Panther

[Ugo DI LUCA]

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

[darrel]

Thanks for clarifying ... much appreciated

FileMaker Version: 6

Platform: Mac OS X Panther

[darrel]

Thanks for the expert help.

I created an unstored calculation field which was

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

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

[Ugo DI LUCA]

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"),"

[darrel]

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

Cheers

Darrel