1 Reply Latest reply on Dec 4, 2014 3:42 PM by philmodjunk

    Count Number of Records with Related Field Value

    BrianKnippa

      Title

      Count Number of Records with Related Field Value

      Post

      Hi,

      I have searched all over the forums and internet for a solution for what I am trying to do and cannot figure out how to get it to work.

      I want a field that displays the count of all the records in the database that have the same value in a specific field. For example, I have 10 records that have the field "ECR #". 

             
      1. ECR #: 1
      2.      
      3. ECR #: 1
      4.      
      5. ECR #: 1
      6.      
      7. ECR #: 2
      8.      
      9. ECR #: 2
      10.      
      11. ECR #: 2
      12.      
      13. ECR #: 2
      14.      
      15. ECR #: 2
      16.      
      17. ECR #: 3
      18.      
      19. ECR #: 3

      I want a field that will automatically give me the number of related records with the same value in the "ECR #" field. For example, if I was on record 3, the value of said field (call it "ECR # Count") would be 3, as the current record has a "ECR #" field value of 1, and two other records in the entire database have the same "ECR #" field value (records 1 and 2). If you were on record 6, the "ECR # Count" field value would be 5, as there is a total of 5 records with "ECR #" field value of 2, ....etc.

      http://help.filemaker.com/app/answers/detail/a_id/2907/~/how-to-show-the-number-of-related-records

      http://help.filemaker.com/app/answers/detail/a_id/3423/~/counting-the-number-of-unique-values-in-a-field

      I have tried to make sense of those two links, but I am not working with tables, and when I try to use a Summary Field, (ECR # Count->Summary->Count of "ECR #") it counts every record that has a valid "ECR #" field. (So using the example database above, it would spit out 10 on every record.)

      It probably is some simple mistake or misunderstanding that I am missing.

       

        • 1. Re: Count Number of Records with Related Field Value
          philmodjunk

          but I am not working with tables,

          But you are working with a table. Without at least one table, you can't create records and enter data into your file. And you can link this table to itself in a relationship as a way to count related records. Open Manage | Database | Relationships. Find the Tutorial: What are Table Occurrences? box for the table that stores the data in your example. Use the mouse to drag from the ECR # field out away from the box and then back to the same exact field. FileMaker will ask you if you want to create a new occurrence (in FileMaker 13) or Instance (Older versions) of this table. Select Ok to do that. If your original table was named ECRData, this new occurrence of that table will be named ECRData 2. You can now define a calculation field in ECRData as Count ( ECRData 2::ECR#) and this field will show your count of all records in the table with the same ECR# value as that of the current record.

          and when I try to use a Summary Field, (ECR # Count->Summary->Count of "ECR #") it counts every record that has a valid "ECR #" field.

          Well it counts every record in your found set where ECR # is not empty. But in a list view layout, you can add a sub summary layout part "When sorted by ECR #" and put your summary field inside this sub summary layout part and it will count all the records with the same ECR # value in your current found set (rather than the whole table), but only if you sort your records on this ECR # field.This is called a Summary Report and you can set it up to list the individual records or just have one row for each unique value in your ECR# field. For more on summary reports, see: Creating Filemaker Pro summary reports--Tutorial

          Caulkins Consulting, Home of Adventures In FileMaking