2 Replies Latest reply on Jun 9, 2010 8:09 PM by wonderbike

    Keeping a running count of records based on criteria

      Title

      Keeping a running count of records based on criteria

      Post

      Hi,

       

      I am running FileMaker Pro 11 on Windows Vista.

       

      I am trying to do something that seems like it should be very simple, but is giving me a lot of trouble.

       

      I am creating a database of faculty members at the university where I work. There is a table that stores faculty members' personal information, such as their nationality, degrees, department, etc.

       

      I want to have a layout that will show me a summary with a running count of things such as:

       

      Number of faculty members currently employed

      Number of faculty members with an M.A.

      Number of faculty members with a Ph.D.

      Number of faculty members in department X

      Number of countries from which the faculty members come

      etc.

       

      This seems like it should be very simple, and the kind of thing FileMaker is designed for, but short of going into the database and searching for each count, I can't figure out how to get these numbers. Is it possible to have a new, related table where the fields auto-calculate these values? It doesn't appear that the Count ( ) function can be used to count records based on criteria (say, count all records in the faculty table who are listed as being in the English department.)

       

      I don't know if this makes things easier or more difficult, but information like departments and degrees is stored in separate tables, related to the faculty table. (There is a list of all departments at the school connected to a table that acts as a roster for the departments, which in turn connects to the faculty table. Similarly, there is a table of degrees connected to the faculty table.) Trying to use these tables to get the numbers, I hit the same wall. Is it possible to use my degree table and count all the M.A.s or the department roster table to count the number of faculty members in the English department?

       

      I hope that is enough information. If you need me to provide more details, please let me know.

       

      Thank you

       

       

        • 1. Re: Keeping a running count of records based on criteria
          philmodjunk

          First, I suggest trying to set up a summary report for what you want. There's a "Count of" summary field that can compute sub totals for each group of records if you place it in a sub-summary part.

           

          Here's a link to a simple tutorial on setting up summary reports that you may find useful:

          Creating Filemaker Pro summary reports--Tutorial

           

          You can use count functions for this also. The way to use it is to define a relationship that uses the criteria you need so that only the records you want counted match.

           

          Example:

          Let's say your faculty table has a field called "Degree".

          Create a separate table occurrence of Faculty, call it FacultyByDegree and link it to your Degrees table this way:

           

          Degrees:: Degree = FacultyByDegree:: Degree

           

          Now a  calculation field in Degrees written as Count ( FacultyByDegree:: Degree ) will count all faculty records that have the same degree. If you create a list view layout of your faculty records and place both the Degree and this calculation field in the body, you'll get a list of all degrees and the counts for each.

           

          Note: I'm suggesting a new table occurrence of Faculty to keep existing relationships between Faculty and Degrees unchanged. If the two tables are already linked by Degree, then you don't need a new table occurrence and the count function can just be Count ( Faculty:: Degree ).

          • 2. Re: Keeping a running count of records based on criteria

            Dear Phil,

             

            Thank you so much for your answer. The sub-summary layouts were something I was completely unaware of, but playing around with them I see that they are really powerful tools.

             

            You were a great help for my first post to these forums!

             

            Thanks again.