Finding unique names on a list
I am new to Filemaker (13) --- I have a list and I am trying to do a count for unique names on the list .... is there an easy way to do this?
It depends on the list and how you produce that list. Is this a set of records or some other kind of list?
It was imported from Excel -- it a set of about 120 records , the particular field is "customer" which is a text field. I am trying to provide a summary that shows the number of unique customers on the list
Since you are importing, the simplest method is to import again--but into another table. In that table, you can specify a validation field option for "unique values" validate always. When you import this data, or just the customer column, into this added table, the validation rule will automatically filter out the duplicates and the number of records actually imported will be the number of unique customers.
There are other methods such as "summing the reciprocal": How to count the number of unique occurences in field.
Or you can use ExecuteSQL with the DISTINCT keyword: A new way to count unique values in FileMaker 12
You can also set up a value list based on the customer field and count the values in the value list.
But in my estimation, all of those take more work to set up than my first recommendation.
Hi --I am trying to use the ExecuteSQL --and i think i am close --just dont understand context of the "table occurance" ---is that the file name?
Here is what I put it (it returns a question mark when I run)
ExecuteSQL ("SELECT COUNT ( DISTINCT Customer ) FROM RE Plan 2.0 Converted" ; "" ; "" )
note-- the field i am trying to count is called Customer and the rile is called RE PLan 2.0 Converted
A "table occurrence" is the name of one of the "boxes" found in Manage | database | Relationships. Most the places that FileMaker asks you to select a "table" you are actually selecting a "table occurrence". They aren't quite the same thing because you can create more than one table occurrence that refers to the same table defined over on the Tables tab.
You would not use the file name here, you need the table (occurrence) name from which you are counting distinct values in the customer field.
I follow your logic --but (see attached screen shots) I think I am using the right table occurrence name. As sugguested I went to Manage |DB | Relationships I see only one box - Called "RE Plan 2.0 Converted) I also see this is the only one in the Table tab as well.
found this old post and it worked
Retrieving data ...