4 Replies Latest reply on Apr 6, 2016 6:44 PM by brendaha

    Counting single occurrences (unique values)

    brendaha

      I created a report that counts the number of classes a student would take in a given year.  While one necessary report tells me the classes the student took in the given year, I need another report that gives me the total number of attendees for a given year.  Right now, my report shows duplicates thus if Alfred Hitchcock took two classes in 2015, it would my report lists his name twice thus the summary includes one extra number which should not be there.

       

      I am extremely new to FileMaker Pro 13 and admit I don't know much about calculations or scripts.  Could someone in the community provide a detailed step-by-step process that would result in a count of unique values such that students taking two classes in a given year would only be counted once for that year, please?  I may have bitten off more than I can chew with my limited knowledge of the program, but a database with this capability is due in two weeks.

       

      Please help!!!

        • 1. Re: Counting single occurrences (unique values)
          Mike_Mitchell

          The simplest way is probably to use ExecuteSQL:

           

          ExecuteSQL ( "SELECT DISTINCT table.field FROM table WHERE table.yearTaken = '2015'" ; "" ; "" )

           

          Another way is to render the report from the proper context where each item you're trying to count has only one occurrence. In your example, you could use a Students table and perform a Find for all students who had classes in 2015 (perform the Find through a portal).

           

          HTH

           

          Mike

          • 2. Re: Counting single occurrences (unique values)
            erolst

            Mike_Mitchell wrote:

            In your example, you could use a Students table and perform a Find for all students who had classes in 2015 (perform the Find through a portal).

             

            Not necessarily by using a portal, but simply by querying the related fields; if the structure is


            Class (incl. year) --< Enrolment >-- Student

             

            you can get the distinct set of Students for classes in 2015 with a script like:


             

            Go to Layout [ Student (Student) ]

            Enter Find Mode

            Set Field [ target field: Class::year ; calculated value: 2015 ]

            Set Error Capture [ on ]

            Perform Find

            # [ if found set, sort records / do other stuff etc. ]

             

            The found set will be all students enrolled in Classes that are being held in 2015; their number is the current found count.

             

            In the end, the best/easiest method depends on the nature of your report: only students (report on a Student layout), or students and their classes (base the report layout on Enrolment and get the distinct count e.g. via SQL, as described by Mike, and display that value using a $$variable).

            • 3. Re: Counting single occurrences (unique values)
              brendaha

              Thank you

               

               

              Get Outlook for Android

              • 4. Re: Counting single occurrences (unique values)
                brendaha

                Thank you.  The steps are quite helpful.

                 

                 

                Get Outlook for Android