ExecuteSQL Distinct doesn't work at all
I’m trying to count unique value in a field using the Execute SQL Distinct command which seems to not work:
I also found: http://help.filemaker.com/app/answers/detail/a_id/3423/kw/finding%20unique%20values but that doesn’t work either. MY report only shows ? in the field. I made file called “Contacts” and also made field called “LocationMap: and created the xDistinctLocations calculation (don’t save calculation option cleared):
ExecuteSQL ("SELECT COUNT (DISTINCT LocationField) FROM Contacts" ; "" ; "")
It calculated the first time that I have 3 unique values but then when I went to add another record and a new state ...it still says 3 unique LocationMap. This seems very faulty and unreliable. I tried to create another script but it only shows ? in the field.
I have listed for fun: Names and LocationMap:
Martin New York
Martin New York
This should show 4 unique States but to break it down further, I'd like to capture: count of unique names per state:
2 Unique Names for California, 1 Unique for Texas and 1 Unique for New York and 1 unique name for Florida. I realize this might require that I concatonate the name & maplocation in order to use for the ExecuteSQL calculation but I'm hesistant to proceed until I can just get the first script to work properly just looking at states and following the examples posted.
What am I missing or is this only for running one time? I want to use in subsummary report when I sort it out. I’m trying to use this to fix an existing report I’m working on. Otherwise, is there a bug in this Execute Distinct command?