5 Replies Latest reply on Jan 17, 2015 10:31 AM by philmodjunk

    Calulate number of unique entries by a certain attribute?

    JingjingLiang

      Title

      Calulate number of unique entries by a certain attribute?

      Post

      Hi,

      I am new to FM Pro and have little experience with the scripts. Can anyone show me how to obtain the number of unique entries in a dataset by a certain attributes?

      For example, in the following table (with heading), how can I calculate the number of unique entries in B, by different A data (the answer is for A=1, there are 2 unique B entries; for A=2, there are 3 unique B entries; for A=3, there is 1 unique B entry)?

      A                 B

      1                  7

      1                  8

      1                  7

      2                  6

      2                  7

      2                  8

      3                  6

      3                  6

      Thanks.

      -Jingjing

        • 1. Re: Calulate number of unique entries by a certain attribute?
          philmodjunk

          If you are familiar with SQL, you can use SELECT with the DISTINCT keyword to get such counts. A new way to count unique values in FileMaker 12

          And while this may seem an odd way to go about it, you can set up a conditional value list based on a relationship matching the value of A to all other records with the same value of A (A self join if both columns of your data are in the same table). ValueCount ( ValueListITems ( Get ( FileName ) ; "ConditionalValueLIstNameHere" ) ) will then return the number of unique values of B for the current record's value in A.

          And for those truly interested in esoteric ways to use FileMaker (In other words, if you thought the last method was odd...) there's a method that's been around for years that predates the ExecuteSQL function called "Sum the reciprocal": How to count the number of unique occurences in field.

          • 2. Re: Calulate number of unique entries by a certain attribute?
            JingjingLiang

            PhilModJunk:

            I am not very familiar with SQL. Could you give me the details on how to run:

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

             

            I used "Scripts" in FM Pro, selecting ExecuteSQL command, and copy and paste the following in the Script text box:

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

            but each time it popped up asking for a ODBC data source...

             

            puzzled...

            • 3. Re: Calulate number of unique entries by a certain attribute?
              philmodjunk

              There is a script step called Execute SQL and there is a function used in calculations named ExecuteSQL(). The thread that I created on this topic refers to the function, not the script step. You would use this inside the specify calculation dialog--such as for an unstored calculation field.

              • 4. Re: Calulate number of unique entries by a certain attribute?
                JingjingLiang

                I ran the following code in Table2, where there is only one column A with three entries 1, 2, 3:

                ExecuteSQL ("SELECTCOUNT( DISTINCT(Table1::B) ) FROM(Table1)";"";"")

                Table 1 is the original table shown on the top, and B is the column in that table.

                However, I got ? in all the results. Did I do anything wrong?

                 

                • 5. Re: Calulate number of unique entries by a certain attribute?
                  philmodjunk

                  If that's a direct copy/paste of your expression, there needs to be a space between SELECT and Count. And while Table1::B is correct syntax in a FileMaker calculation, it's incorrect syntax in SQL. You'll need to use either B or Table1.B to refer to that field. (and both table and field names often need to be enclosed in quotes this way: \"table 1\".\"_fkTableID\" )

                  You also do not need the extra parenthesis, but I don't think they'll produce the ? result indicating a syntax error.

                  But the query will return a count of all the unique values in Table1. This is not the end result that you want here if the value of a field in the current record is supposed to match to a subset of the records in Table1 and you only want to count the unique values amongst that subset of all the records in Table1.

                  It's a step in that direction though, so I'd clean up the syntax errors to get a result before we take a look at adding either a JOIN or a WHERE clause to limit the set of records being counted to just those that match to the current record in table 1.

                  SQL resources:

                  Seed Code's SQL Explorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree

                  FileMaker Inc.'s reference doc on SQL: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                  The SQL.DEBUG custom function that you can add to your file if you have FileMaker Advanced and use it to get actual error messages out of your failed query if you test it as a watch expression in the data viewer. (You have to click monitor and then return to the watch expression editor to get the error message.):

                  If (

                  //the sql call results in an error, return empty so the error will be returned
                  _executeSQL = "?" ; "" ;

                  //the sql call is executed correctly, just return the result
                  _executeSQL
                  )

                  // ===================================
                  /*

                      This function is published on FileMaker Custom Functions
                      to check for updates and provide feedback and bug reports
                      please visit http://www.fmfunctions.com/fid/335

                      Prototype: sql.debug( _executeSQL )
                      Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
                      Last updated: 28 July 2012
                      Version: 2.2

                  */
                  // ===================================