AnsweredAssumed Answered

ExecuteSQL Distinct doesn't work at all

Question asked by snickwit on Aug 19, 2013
Latest reply on Aug 21, 2013 by philmodjunk

Title

ExecuteSQL Distinct doesn't work at all

Post

     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:
     NAMES:       LOCATIONMAP:
     Smith          California
     Sanchez    California
     Smith          California
     Johnson     Texas
     Martin          New York
     Martin          New York
     Brooks        Florida
      
     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?
      
     Thanks much!

Outcomes