6 Replies Latest reply on Oct 18, 2015 2:04 PM by jxjarvi

    Count of individual student entries

    jxjarvi

      I want to count the number of times a student is entered into a discipline data base.


      Each have a unique ID number.


      For example, student 13453 has 42 entries.


      I want that number to show up as a field in my table.

       

      Here is what I tried:

       

      f your version of FileMaker Pro does not support the ExecuteSQL function, you can accomplish the same results using a couple of summary fields, one calculation field and sorting your database.

      Start by defining a summary field called "TotalCount" which is a Count Of LocationField

      Next, create a calculation field called "FractionOf" with the following expression:

      1 / GetSummary (TotalCount ; LocationField)

      This calculation gets the reciprocal of the TotalCount summary field. The GetSummary function is used to return the value of the "TotalCount" summary field when the database is sorted by the "LocationField" field.

      Lastly, create another summary field called "UniqueCount" which is a total of the "FractionOf" calculation field. The "UniqueCount"field is the only field that you need to place on your layouts (if needed) to display the count of unique records.

      After you have created the three fields above, sort your database by "LocationField" to provide a count of unique records in the "UniqueCount" field.

        • 1. Re: Count of individual student entries
          user19752

          You referenced "count of student".

          You would want count of entry for each student, then

           

          Make a summary field count of ID

          Sort by student

           

          show the result.

          • 2. Re: Count of individual student entries
            jxjarvi

            Ok I'm not sure I understand. I am going to:

             

            make a summary field "count of ID"

            sort by student

            show the result

             

            This all goes in the summary field script window?   Sorry, it's been awhile since I did this.

            • 3. Re: Count of individual student entries
              jxjarvi

              Ok I tried the summary field. All it did was give me a running count of records. It didn't summarize the individual occurrences.

              Any ideas? Thanks for your help! Jack

              • 4. Re: Count of individual student entries
                erolst

                jxjarvi wrote:

                make a summary field "count of ID"

                sort by student

                show the result

                This all goes in the summary field script window? 

                 

                What is a “summary field script window”?

                 

                You need to add a sub-summary part sorted by studentID (or probably better, the related student::name field) to your layout, and place the summary field there (make sure you didn't check the "running" option in the summary field definition).


                If you sort the found set by the field you defined as sort field for the sub-summary part, every sorted group (i.e. student) has a count result for the found set. (You can remove the body part if you're only interested in aggregated results.)

                 

                Note that the solution you quoted in your original post serves to find the number of individual students in a found set of disciplinary records; not the same thing.

                 

                If you want to see the number of entries for a given student while on a Student layout, put the related summary field on that layout.

                 

                PS: The answer you marked “correct” isn't correct – well, it's not even an answer …

                • 5. Re: Count of individual student entries
                  jxjarvi

                  Thanks much!! I don't know how I marked that. I used to have a whole

                  student information system built in Filemaker, but it has been awhile.

                  Thanks for the answer. I'll try it!!

                  • 6. Re: Count of individual student entries
                    jxjarvi

                    Many thanks! That worked very well! I'll go to the forum and click on

                    correct. Thanks!

                    Jack