Prior to the advent of ExecuteSQL, we still had the List function (FileMaker Pro 15 Help). That will give you the list of all related values in the field you want. But it doesn't remove the duplicates. For that, you can use a Custom Function. Here's one I use for removing duplicates (there are plenty of others):
Author: Tom Seidler, 2012 [based on Peter Roots CF: http://www.briandunning.com/cf/1109]
Method: tail end recursion and use of substitute means this can handle lists of (theoretically) infinite size with as many as 50k variants in the list.
An essential aspect: ["¶" & _this & "¶" ;"¶¶" & _this & "¶¶"]
This allows the guaranteed removal of all occurences of _this, even if they are next to each other, in a list...
Also borrows Ray Cologon's Trim4 methodology to clear all empty values.
input = return-delimited list of values
output = initially empty
_this = GetValue ( input ; 1 ) ;
_null = If ( Left ( input ; 1 ) = "¶" ; 1 ) ;
_valueList = If ( not _null ; Substitute ( "¶" & input & "¶" ; ["¶" & _this & "¶" ;"¶¶" & _this & "¶¶"]; ["¶" & _this & "¶" ; ""] ; ["¶¶";"¶"] ) ; input ) ;
_l1r = If ( Left ( _valueList ; 1 ) = "¶" ; 1 ) ;
_r1r = If ( Right ( _valueList ; 1 ) = "¶" ; 1 )
Case ( Length ( _valueList ) ;
cfDedupe ( Middle ( _valueList ; 1+_l1r ; Length ( _valueList ) - ( _l1r + _r1r ) ) ; List ( output ; _this ) ) ;
Should i use type or category of the items? Is it easy to understand?
If i use ValueCount, Is it ValueCount(country)?
You use whatever field you want to use to determine the count.
Your final calculation should look something like:
ValueCount ( cfDedupe ( List ( relatedTable::relatedField ) ; "" ))
You can accomplish this in a single calculation. The key to this approach is reversing the normal use of the FilterValues() function.
Instead of filtering your data with a list of unique country names, you are instead filtering the unique list of country names against your own list of country names. The result is a list of unique country names which you can then count.
~MyListToFilter = List(MyPortalTORelationship::Country) ;
~CountryList = ValueListItems ( Get ( FileName ) ; "Countries" ) ;
~FilteredResult = Trim( FilterValues ( ~CountryList ; ~MyListToFilter ) ) ;
~LengthResult = Length( ~FilteredResult );
~CR.Check = If( Right( ~FilteredResult;1) = "¶" ; 1 ; 0 ) ;
~FinalResult = If( ~CR.Check = 1 ; Left( ~FilteredResult ; (~LengthResult - 1) ) ; ~FilteredResult )
Good answer, assuming you have a value list defined …
Of course, if you’re going to go to the trouble to define a value list, you could just define it as “related records only” and just use ValueListItems on that list without the filter ...
Thank you again Brian and Mike. i have tried Brian's method, but there is nothing showing in the field.
The key to making the calc work is making sure you have created a value list first which contains a list of unique values for all of the countries being used. You can either base this on a separate table of country names that you maintain, or you can base it on the data that has been input into the field that you are trying to do a calculation upon. Then you just need to make sure that the value list name you choose for your value list is the one used in my calc. If you like I can post a sample file if you are interested in seeing it in a working file.
Thank you Brian. Please post a sample file for me in Filemaker Pro 11
Thank you Mike. If it is value list from other tables, could i count the number of country?
Yes. A value list automatically gives you only the unique values. So just use the Count ( ) function.
I have tried the Count function, but there are no value could be seen in that field. Is my calculation wrong?