Counting Unique Values
I am trying count the number of unique values in a field. I have tried the self-join method but it doesn't seem to work. Could someone give me some insight on how to do it? I am using v.10.
The "self join" method?
Are you counting unique values over the entire table, a found set of records or a related set of records?
The third method described here: http://help.filemaker.com/app/answers/detail/a_id/3423/~/counting-the-number-of-unique-values-in-a-field
I am counting the number of unique zip codes across one entire table.
These days, I'd probably use ExecuteSQL with the DISTINCT keyword to get a count of all records in a table: A new way to count unique values in FileMaker 12 Which is option 1 in this article.
The "self join" method should work for you. If you still want to get that method working, you'll need to describe your attempt at getting it to work in detail so that we can spot where it went wrong.
But for counting all the unique values in the table, there's another method I used to use in versions prior to FileMaker 12 which didn't have ExecuteSQL as an option:
Define a "use values from a field" value list that pulls all of it's values from this same field in this same table.
Then, ValueCount ( ValueListItems ( get ( FileName ) ; "NameOfTheValueListGoesHereinQuotes" ) )
will count the number of unique values in this field.
I defined the list and created the calculation field with that code, but it returns the value 0. Any thoughts? Thanks for the help.
Please describe how you "defined the list". You may want to show a screen shot of the dialog that opens when you select the "use values from a field" option.
Attached is what comes up when I edit the list and select "use values from a field." I went File|Manage|Value Lists|New and the dialogue on the right came up, and when I selected the option you told me two the dialogue on the left came up.
EDIT: Also, there is no "get" function in the list of available functions. I used the getField function.
Then this calculation should give you the unique count of unique values for zip in the participation records table (occurrence):
ValueCount ( ValueListItems ( Get ( FileName ) ; "zips" ) )
If you put this into a calculation field, make sure that the field is of type calculation (Not a number field with an auto-entered calculation) and that you specify "do not store" in storage options.
It works! Thank you very much.
Retrieving data ...