6 Replies Latest reply on Aug 26, 2016 8:56 AM by coherentkris

    Count unique records in a found set

    sp-1370

      Hi Everyone,

       

      I'm not a programmer, but a fast learner.  I have a database of Courses watched by date; and I am trying to count the # of users that logged in to the system to watch a course within a date range.

       

      Since a user could have watched 15 courses, I need to count the unique user records.  I have this working as a whole using the following method in the field calculation:

       

      1 / GetSummary (TotalCount ; LocationField)

       

      Although, I have discovered that I would need to write a script to produce a correct result for a found set.  This is my trouble (as stated before I'm not a programmer), however, I can find a few scripts online but am unclear which fields would be the list field (where the script gets the data) and the return result field (where the end value displays).

       

      And any provide a script they've used and let me know for "fieldname" which field they have used (list field or result field).

       

      I apologize for my laughable language to the programmers, and thank you in advance.

       

      Thanks again,

      Rachael

        • 1. Re: Count unique records in a found set
          coherentkris

          If all you need is a count then

           

          ExecuteSQL ( "SELECT DISTINCT COUNT(user_field_here) FROM your_table WHERE date_field BETWEEN date_1 AND date_2" ; "" ; "" )

          • 2. Re: Count unique records in a found set
            sp-1370

            Thanks, but when I add this calculation to the field I get a ? as a result.  Should this be a script?

            • 3. Re: Count unique records in a found set
              mikebeargie

              You can generate this with an ExecuteSQL() calculation:

               

              ExecuteSQL("SELECT COUNT(DISTINCT userKey) FROM videosWatched WHERE dateWatched >= ? and dateWatched =< ?";"";"";reportStartDate;reportEndDate)

               

              The bold portions are things you would need to match up from your table, you can also pass variables in place of reportStart/EndDate

               

              ---

               

              The second method would be to use the getsummary function as you described.

               

              To need that you need a summary field (totalCount in your example), so create one called "countKey" or something that is a count type summary of the userKey you have.

               

              You then find based on dateWatched to make sure your found set only contains the "watched" records included in your report.

               

              Next you need to SORT the found set by userKey (locationkey in your example), otherwise GetSummary doesn't work.

               

              Last, you need an unstored calculation field to run and display the GetSummary function you posted.

               

              To script the find:

              Enter Find Mode [ no pause ]

              Set Field [ table::dateWatched ; reportStartDate & "..." & reportEndDate ]

              Perform Find

              Sort Records [ userKey asc ]

               

               

              1 of 1 people found this helpful
              • 4. Re: Count unique records in a found set
                beverly

                you cannot just say BETWEEN and list the fields, you must supply values (or ? - parameters to these fields to get the values).

                1 of 1 people found this helpful
                • 5. Re: Count unique records in a found set
                  David Moyer

                  Hi,

                  regarding the ? result, you don't need a script.  The most likely reason for the ? (sql error) is syntax, including using SQL keywords.  Here's the FM guide for SQL:

                  https://fmhelp.filemaker.com/docs/15/en/fm15_sql_reference.pdf

                  1 of 1 people found this helpful
                  • 6. Re: Count unique records in a found set
                    coherentkris

                    That's because its pseudocode to illustrate the use of sql with distinct, count and between operations.

                    You'll have to substitute your fields and play with it to get it to work.

                    I like to provide clues and let people solve the details for themselves in the hope it leads to better learning.

                    Mikes example got it right.