AnsweredAssumed Answered

Counting unique related records

Question asked by realgecko on Sep 2, 2016
Latest reply on Sep 2, 2016 by LabsRock

Hello everybody,


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.

Screen Shot 2016-09-02 at 12.55.42 PM.png

Screen Shot 2016-09-02 at 1.09.20 PM.png