What version of FileMaker are you using?
I am using Filemaker Pro Advanced v.11
Then that rules out using ExecuteSQL with the DISTINCT key word to get a list of distinct values. But there is another way that works in FileMaker 11.
There are two similar ways to set this up. If you have a table of clients with a client ID and only one record for each client--which would be a very useful table in your database, use it. But if not, you can set up a self join with the table that you have described here.
If you have a clients table:
Clients::Client_ID = Deliveries::Client_ID
If you do not:
Deliveries::Client_ID = Deliveries|SameClient::Client_ID
Deliveries|SameClient is a second table occurrence of Deliveries.
Define a value list, Client Organization Names, with the "use values from a field" option. Set it to list values from the right hand table (Deliveries if you use a client table, Delieveries|SameClient if you do not.)
Select the Product_Organization_Name field as the source of values.
Select the "include only related values, starting from" option and select the left hand table (Clients if you use that table, Deliveries, if you do not).
Now define this calculation field in the left hand table:
ValueCount ( ValueListitems ( Get ( FileName ) ; "Client Organization Names" ) )
and select number as the result type. Make this an unstored calculation field to be sure that it updates when new records are added to the Deliveries table.
This field will return the number of unique values in Product_Organization_Name for a given ClientID.
It sounds a little like jibberish to me, but following your steps did the job!
Thanx! I will read the directions over a few times and hope to understand what just happened.
What we set up is a conditional value list. If you added a portal to the related table and changed to list or form view, it would list all the same organizations, but with all the duplicates. Value lists that are set up to show data from a field, automatically filter out the duplicates. So we just exploited that fact and use the ValueListItems function to get a return separated list of values that we can count with the ValueCount function.
If you are unfamiliar with conditional value lists--which can be a very useful way to keep value lists reduced to more mangeable sizes, see these links to learn more:
There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.
The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
my company has FMP 13 licenses.
What would be the SQL solution you mentioned? With the "distinct"-key?
See this thread: A new way to count unique values in FileMaker 12
Other useful resources for working with ExecuteSQL:
SeedCode's free SQL explorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree
FileMaker's current reference document on SQL used with FileMaker (ODBC, JDBC and ExecuteSQL): https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf