9 Replies Latest reply on Aug 6, 2013 3:07 PM by philmodjunk

    Unique Count

    FileMakerNovice

      Title

      Unique Count

      Post

           Hello,

           I have been trying to create a count of unique values in a table.  The way I am approaching it is through ExecuteSQL in:
           http://help.filemaker.com/app/answers/detail/a_id/3423/~/counting-the-number-of-unique-values-in-a-field

           I have one table that I'm using that has a field called "EmployeeID".  I created a field called countEmployeeID = ExecuteSQL ("SELECT COUNT (DISTINCT EmployeeID) FROM CallType" ; "" ; "")
           CallType is the table name in relationship view.

      The problem is that it returns a "?".  I've looked at SQL forums and it appears my syntax is correct.  What else could I be missing?

           Edit:  I checked to not store the value and recalulate when needed.  It now provides me a number but this number does not change when I refine the search.  Is this normal or is there a way to create a unique find on a found set?

        • 1. Re: Unique Count
          philmodjunk

               "refine the search"?

               Your SQL query counts all distinct values in the entire table. If you want to selectively count values, you'll need to use a WHERE clause in the query that selects for the records you want to count.

          • 2. Re: Unique Count
            FileMakerNovice

                 Awesome.  That would explain it.  So... 
                 ExecuteSQL ("SELECT COUNT (DISTINCT EmployeeID) FROM CallType where date between STARTDATE from Globals and ENDDATE from Globals" ; "" ; "")

                 Apologies but my SQL is super rusty.  Am I missing something?  It now displays a "?" again.  Do date, STARTDATE and ENDDATE need to be referanced differently?

                 Thanks in advance.

            • 3. Re: Unique Count
              philmodjunk

                   date is a reserved word in SQL so the SQL engine fails to recognize it as a field name. Put it into your SQL String like this: \"date\". Given how often a field or table occurrence name can trip a syntax error, it's not a bad idea to enclose all of them in quotes like this.

                   The backslash is the escape character you need to put before quotation mark characters so that FileMaker doesn't interpret the quote as the end of the string.

                   SeedCodes SQLExplorer is a handy tool you can use to help get the syntax correct. And the SELECT query examples found in the ODBC JDBC Guide that you can access from FileMaker help are accurate examples of the SQL syntax you can use with the ExecuteSQL function.

              • 4. Re: Unique Count
                FileMakerNovice

                     Wow.  SQLExplorer is pretty cool.  Provided a good example of what we found previously within a few seconds.  Great tool I'm sure I'll use.  Unfortunately, I just still can't figure out what is going on with my query.  sad

                     Before Phil's pointing out of date being a reserved word, I changed the field to be reportDate.  I am still getting the "?".  Through testing, I simplified my query to see if it worked at all:

                       ExecuteSQL 
                       ("SELECT COUNT (DISTINCT \"EmployeeID\") 
                         FROM CallType 
                         WHERE \"reportDate\" = '07/01/2013'" 
                       ; "" ; "")
                      
                     This returns the correct value of 46.  I then changed \"reportDate\" = to \"reportDate\" between '07/01/2013' and '07/30/2013'.  This also returned a correct value of 95.  But once I changed it to: WHERE reportDate between \"STARTDATE\" from Globals and \"ENDDATE\" from GLOBALS, it provides a "?".
                      
                     To add to my difficulty, the layout that I am working with also has a checkmark set (global value) that performs a search on a value called "CallType" in my CallType table.  
                      
                     Edit:  Found out through testing that Let() variables do not translate to the SQL query.
                • 5. Re: Unique Count
                  philmodjunk

                       Use Globals.StartDate and Globals.EndDate to refer to your two fields instead of FROM.

                  • 6. Re: Unique Count
                    FileMakerNovice

                         You are awesome with the quick response.  Unfortunately, it still does not appear to be calculating correctly.  Here is my newest iteration:

                         ExecuteSQL(" 
                           SELECT COUNT (DISTINCT \"EmployeeID\") 
                             FROM CallType 
                             WHERE \"reportDate\" BETWEEN Globals.STARTDATE and Globals.ENDDATE" 
                           ; "" ; "")
                          
                         I have confirmed that Start and end dates are populated and display properly as M/D/YYYY.  Also confirmed that it populates correctly when viewed in the same layout.  Additionally, I confirmed that all dates are set to the date type.  Still, though, the only time I see a proper result is when I replace "Globals.Startdate" and "Globals.EndDate" with date values in quotes.
                          
                         I apologize if I'm missing something and appreciate the help.  Thanks again.
                    • 7. Re: Unique Count
                      philmodjunk

                           Are startDate and ENddate fields of type date or are they  text? They'll look correct if they are text, but won't evaluate correctly. REportDate also needs to be of type date and als oneeds to have an identifying table occurrence reference.

                           I'd try this expresson:

                           ExecuteSQL(" 
                             SELECT COUNT (DISTINCT \"EmployeeID\") 
                               FROM CallType 
                               WHERE Calltype.\"reportDate\" BETWEEN ? and ?" 
                             ; "" ; "" ; Globals::STARTDATE ; Globals::ENDDATE )
                      • 8. Re: Unique Count
                        FileMakerNovice

                             Yuzzir.  All fields are of type date.  The awesome thing is that adding the fields to the arguments section worked like a charm.  Just curious on how this functions.  Does the arguments go in the order of the question marks?  IE first question mark gets assigned the first argument, second gets second?  I was reading up on the ExecuteSQL function and didn't see an example that explains this.  Thanks again for your awesome work.

                        • 9. Re: Unique Count
                          philmodjunk

                                Do the arguments go in the order of the question marks?

                               yes

                               I'm relearning SQL myself--used to use it a lot with MS Access and have found that danged ? result when I messed up syntax frustratingly uninformative.

                               The reason (I think) that you need to use the ? parameters is due to the fact that you don't have the Globals table declared anywhere in your SQL. If it were part of a Join clause, you wouldn't need to use the substitution parameters.

                               Another thing to keep in mind is that the first parameter in the ExecuteSQL fuction call is simply a string expression. While the examples typically show a literal string for the SQL, any text result that evaluates to a valid SQL query may be used. That opens the door to using other methods to produce a SQL query that reconfigures on the file to produce very different results based on how that text get's produced in the first parameter.