I have a database defined by the relationship visible in the first screenshot.
In the Species table (see screenshot 2) I would like to define a field to count the number of studies in which each species occurs. As in the All_records table a specific combination of Species and Study code can occur more than once I need to find a way to return the count of unique records in the field All_records::Study code related to each value in the field Species::Species.
Counting non-unique related values posed no problem with the function Count ( All_records::Study code ).
For counting the unique records I found the following support page on counting unique records in the same table Counting the number of unique values in a field | FileMaker .
I tried the sql function
ExecuteSQL ("SELECT COUNT (DISTINCT All_records::Study code) FROM All_records " ; "" ; "")
and played around with it for a while but it only returns "?".
Now I am not sure whether this is because this specific sql query does not work for related records or because I can't find the error I made in code.
Any help on this or alternative solutions for achieving the count of unique related records would be greatly appreciated.