5 Replies Latest reply on Oct 23, 2014 11:02 AM by Doug Staubach

    A new way to count unique values in FileMaker 12

    philmodjunk

      Title

      A new way to count unique values in FileMaker 12

      Post

      Say you have these values in a table:

      Apple
      Orange
      Strawberry
      Orange
      Apple
      Orange
      Strawberry

      and you don't want a count of all the records (7). You want a count of how many unique values exist (3). In previous versions of Filemaker, we've had to use the How to count the number of unique occurences in field. to produce this count. Or you might define a value list with the specify values from field option and use ValueCount ( ValueLIstItems ....) ) to count the unique values.

      FileMaker 12 provides a new and much simpler method: ExecuteSQL

      Here's the expression you can use for this:

      ExecuteSQL ("SELECT COUNT ( DISTINCT YourFieldNameGoesHere ) FROM YourTableOccurrenceNameHere" ; "" ; ""  )

        • 1. Re: A new way to count unique values in FileMaker 12
          DavidDuncan

               Forgive me if this is a foolish question, but if I want to perform this count for data arranged in a summary layout, where do i create the ExecuteSQL expression?  

               I tried to make it a field in my raw data table (59K records).  See the image below, I have a layout containing fields from higher level tables (beginning "::") summarising raw data (PointID, Species). The count value of 463 (for example) includes multiple instances of "PointID", whereas i want only one. I tried to make a field using the expression in a new calculation field referencing "PointID".  

          > ExecuteSQL ("SELECT COUNT ( DISTINCT PointID ) FROM SpeciesData2013" ; "" ; ""  ) 

               I presumed i would then add that field to my summary. However, when i exited the Manager window, FM froze merely trying to evaluate the expression so I imagine i have done something wrong.

               Where should that ExecuteSQL expression go in my case?

          • 2. Re: A new way to count unique values in FileMaker 12
            FilmUser

            Same question - where does the recommended ExecuteSQL expression go, or how do I use it?

            • 3. Re: A new way to count unique values in FileMaker 12
              Doug Staubach

              Hi FilmUser:

              The ExecuteSQL statement that PhilModJunk provided will give you a count of all unique values for the named field (for the entire database). To use it, you just create a calculated field and paste the ExecuteSQL statement into the calculated field's formula window. (Caution: make sure the field's "calculation result" option is set to number).

               

              • 4. Re: A new way to count unique values in FileMaker 12
                FilmUser

                Don't know if I'm messing something up, but below is my calculation statement in the field def - result is a number, no repeats.

                The name of the field for unique values to be counted is "PROJECT NUMBER" and the table is "ALL PROJECTS".

                But the field returns a "?"

                Also, can this field be used in a report to return the count for only the records shown in the report? (My real reason for needing the field.)

                ExecuteSQL ("SELECT COUNT ( DISTINCT  PROJECT NUMBER ) FROM ALL PROJECTS" ; "" ; ""  )

                • 5. Re: A new way to count unique values in FileMaker 12
                  Doug Staubach

                  Hi FilmUser:

                   

                  Try this and see if it helps:

                  ExecuteSQL ("SELECT COUNT ( DISTINCT  '' + PROJECT NUMBER ) FROM ALL PROJECTS" ; "" ; ""  )

                  Note that '' (two single quotes, NOT a single doublequote) is an empty text constant: adding it to the project number field may force the data type of the project number to be text instead of numeric.