If you are familiar with SQL, you can use SELECT with the DISTINCT keyword to get such counts. A new way to count unique values in FileMaker 12
And while this may seem an odd way to go about it, you can set up a conditional value list based on a relationship matching the value of A to all other records with the same value of A (A self join if both columns of your data are in the same table). ValueCount ( ValueListITems ( Get ( FileName ) ; "ConditionalValueLIstNameHere" ) ) will then return the number of unique values of B for the current record's value in A.
And for those truly interested in esoteric ways to use FileMaker (In other words, if you thought the last method was odd...) there's a method that's been around for years that predates the ExecuteSQL function called "Sum the reciprocal": How to count the number of unique occurences in field.
I am not very familiar with SQL. Could you give me the details on how to run:
ExecuteSQL ("SELECT COUNT ( DISTINCT YourFieldNameGoesHere ) FROM YourTableOccurrenceNameHere" ; "" ; "" )
I used "Scripts" in FM Pro, selecting ExecuteSQL command, and copy and paste the following in the Script text box:
"SELECT COUNT ( DISTINCT YourFieldNameGoesHere ) FROM YourTableOccurrenceNameHere" ; "" ; ""
but each time it popped up asking for a ODBC data source...
There is a script step called Execute SQL and there is a function used in calculations named ExecuteSQL(). The thread that I created on this topic refers to the function, not the script step. You would use this inside the specify calculation dialog--such as for an unstored calculation field.
I ran the following code in Table2, where there is only one column A with three entries 1, 2, 3:
ExecuteSQL ("SELECTCOUNT( DISTINCT(Table1::B) ) FROM(Table1)";"";"")
Table 1 is the original table shown on the top, and B is the column in that table.
However, I got ? in all the results. Did I do anything wrong?
If that's a direct copy/paste of your expression, there needs to be a space between SELECT and Count. And while Table1::B is correct syntax in a FileMaker calculation, it's incorrect syntax in SQL. You'll need to use either B or Table1.B to refer to that field. (and both table and field names often need to be enclosed in quotes this way: \"table 1\".\"_fkTableID\" )
You also do not need the extra parenthesis, but I don't think they'll produce the ? result indicating a syntax error.
But the query will return a count of all the unique values in Table1. This is not the end result that you want here if the value of a field in the current record is supposed to match to a subset of the records in Table1 and you only want to count the unique values amongst that subset of all the records in Table1.
It's a step in that direction though, so I'd clean up the syntax errors to get a result before we take a look at adding either a JOIN or a WHERE clause to limit the set of records being counted to just those that match to the current record in table 1.
Seed Code's SQL Explorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree
FileMaker Inc.'s reference doc on SQL: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf
The SQL.DEBUG custom function that you can add to your file if you have FileMaker Advanced and use it to get actual error messages out of your failed query if you test it as a watch expression in the data viewer. (You have to click monitor and then return to the watch expression editor to get the error message.):
//the sql call results in an error, return empty so the error will be returned
_executeSQL = "?" ; "" ;
//the sql call is executed correctly, just return the result
This function is published on FileMaker Custom Functions
to check for updates and provide feedback and bug reports
please visit http://www.fmfunctions.com/fid/335
Prototype: sql.debug( _executeSQL )
Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
Last updated: 28 July 2012