5 Replies Latest reply on Aug 21, 2013 10:45 AM by philmodjunk

    ExecuteSQL Distinct doesn't work at all



      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:
           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!

        • 1. Re: ExecuteSQL Distinct doesn't work at all

               Considering the FM database has table - Contacts, with fields - kp_Contact, Name and LocationMap.

               Create a 'NameCount' unstored calculation field in contacts table with formula

               "select Count(Distinct Name) from contact where LocationMap=?";
               "     ";

               Create a layout with sub-summary part sorted by  'LocationMap' field, and place two fields -LocationMap & 'NameCount' in subsumamry section.

          • 2. Re: ExecuteSQL Distinct doesn't work at all
            Thanks so much MetaSys - that worked! smiley
            I noticed that ExecuteSQL does not notify or prompt if you have typos in your table name and also it does not allow for spaces in table name so once I added an underline to make one name, it worked like a charm. I also found my checkbox to "not store calculations" seemed to be missing so I did that. Additionally,  I realized in my haste that I made some typos in my original request so I updated the last name & state properly.  Thanks again - I'm so happy to have a working solution that has saved me hours from going crazy!! Here's snapshot of table layout (not the subsummary report).
            • 3. Re: ExecuteSQL Distinct doesn't work at all

                   The SQL in this function is simply text inside of quotes so there is no way that the Specify Calculation dialog can trap for errors in the names inside the quoted string. Instead, you get the dreaded question mark and then have to figure out what you did wrong. angry

                   You can enclose your table occurrence and field names in quotes and this is a good idea since many such names will trip a syntac error due to a space, leading underscore or because the name is a reserved word in SQL.

                   You can quote a name inside the quoted string by using the backslash with the quotation marks: "SELECT \"date\" From....  "

                   SeedCode's SQLExplorer can be helpful and the SELECT query examples found in the ODBC JDBC quide that you can open from FileMaker help can also provide good examples of correct SQL syntax for use with this function.

              • 4. Re: ExecuteSQL Distinct doesn't work at all

                     Thanks - I did try quotes and it didn't work originally but I didn't know about the backslach. I will check out the SQLExplorer - sounds interesting product.

                     So - there's a snag I'm running into which is huge performance hit problem frown. I'm actually using these calculations in a crosstab report which lists monthly sums for fiscal year. The ExecuteSQL field works fine but the dependent field which is Case field that calculates count per month is taking over 10 minutes long. I have 5,000 records which will probably grow to 30,000 or more over course of a year. Is this because the calculations are not stored in database? How should I speed this up so one doesn't have to go grab lunch while it calculates?

                     Here's example of what I'm referring to in terms of 3 fields that build my quarterly fiscal cross tab report:

                     Unstored calculation Field: NameCount that is the ExecuteSQL script (see above)

                     Calculation Field: zName_July_Hours that is: Case(z_ServiceDateMonth =7; NameCount)

                     Summary Field: zName_July_Total that is: Total Sum of (zName_July_Hours)

                     The last one seems to have hour glass and just run and run and run with a screen saying Summarizing Field: zName_July_Total

                     Thanks for the awesome support!

                • 5. Re: ExecuteSQL Distinct doesn't work at all

                       There are other approaches to get your cross tab report, multiple portals--often one row and filtered for each column is a commonly used method.

                       Some of them may have a lighter computational load for your report.

                       Another approach is to compute as many summary totals "in advance" as possible. I have a system here that summarizes data from the line items of Purchase Orders (we can get up to a 1000 in one day) where we need reports with monthly totals and averages for each year over a 5 year span. That means computing totals and averages of data from multiple millions of line items.

                       I get the results that I need by using a script to collect data in a summary table each night after close of business. It creates one record for each day for each material purchased that day with the total weight, total cost, etc stored in number fields. this "condenses" what could be over 4000 records into about a dozen records for that day--greatly reducing the amount of computation needed to produce reports based on this data.

                       Of course, this is not a method that can be used in every database.