Sounds like you are trying to get a Value List of the field and then count the number of unique values?
ValueCount ( ValueListItems ( Get(FileName) ; ValueList ) )
So what you will do is make a vaule list of that field. And then put the name of that value list in the calculation. If you make a Value List called SurveyID then you Calculation would look like this:
ValueCount ( ValueListItems ( Get(FileName) ; "SurveyID" ) )
The result will be a number representing the unique values in that field.
Another way to approach it would be:
ValueCount ( ExecuteSQL (" select Distinct (SurveyID) from QuestionsImportedfromAccess";"";"" ))
Thanks! That worked great.
Smac's answer is actually the better one as it's hundreds of times faster if you have a large number of records (say >200,000 records).
Currently I am following your suggested method to get a count of unique value in a subsummary report, but I am getting the total count of records as the output, not the unique value count.
I have 18765 records in a one table two layout database. Out of those 18765 records only 9876 records are unique to MR NUM.
I created a value list "MR_Num value list" using the values from the field MR NUM field.
Now my calculation field MR_# Unique Count=ValueCount ( ValueListItems ( Get(FileName) ; "MR_Num value list" ) )
I placed this field in my display layout.
When I browse thru the records each record is giving the value of 1 under MR_# Unique Count field.
Also give me the total record count in my sub-summary report.
Is it suppose to give the value of 1 for each unique value in that record set?
Any idea to display the unique value count in my report?
This would suggest that you only have 1 unique number in the MR NUM field, which, as you suggest, is not true.
For more information, what is the value if you just try - ValueListItems( Get(FileName) ; "MR_Num value list" )
if you have filemaker advanced, an easy way to test this is to go to Tools --> Data Viewer -->Watch and try this calculation in to test what you get.
Thank you for your guidance and suuport....
Finally I got the result thru your calculation which is
ValueCount(ValueListItems(Get(FileName); "MR_# Unique Value"))
One value for every record (365 MR_# unique count out of 647 total records)...So glad to see that number..
But now my issue is that, based on my record selection thru the report script, outputting 365 MR_# count for even less # of records.
Example: I ran a script to find out how many MR_#s were there from Oct 1 thru Oct 5 (5 days).
Output: Total records count 76 and Unique MR# count 365 - MR_# count is wrong, suppose to be 37.
How do I get the exact # of MR_#s based on my record selection?
Looking for some guidance...