3 Replies Latest reply on Jun 11, 2013 8:19 AM by philmodjunk

    Counting Field instances



      Counting Field instances


           I have a field that has either 0 or 1 or 2. I want to create a field that counts how many 0s there are. However it should count the zeros in a specific group.


           field with 0,1,2        specific group          count field

                   1                                  A                                0

                   1                                  B                                2

                   2                                  B                                2 

                   0                                  C                                1

                   0                                  B                                2

                   0                                  B                                2

           what can i use in the calculation for the count field which counts the number of 0s in group A, B, and C?

        • 1. Re: Counting Field instances

               Does each row in you example data represent a different record?

               If each row is a different record, do you want a count of all the records in the table where the value is zero?

               Or a count for each record where the value is zero for a subset of the total records? (And if so, how do you define that subset?)

               Are 0,1,2 the only possible values for that field? (Could the field ever be empty instead of having one of these three values?

               Are you using FileMaker 12?

          • 2. Re: Counting Field instances

                 Yes each row represents a different record.

                 I want a count where the value is zero for a subset of the total records. That subset is defined by another field, in my example, the field is A,B, or C. that's why the A has a count of 0 0s, B has a count of 2 0s, and C has a count of 1 0s.

                 Yes 0,1, 2 are the only possible values, but I was thinking maybe I should account for empty's just in case. However, it shouldn't matter because I only care if the field contains a 0, right? Anything else can just be ignored including empty. 

                 Yes I am using filemaker 12.

            • 3. Re: Counting Field instances

                   In some expressions, a field with 0 and a field that's empty evaluate the same and other methods treat them as different values so knowing whether or not you need to descriminate between 0 and empty is a key detail.

                   Without using Execute SQL:

                   You could set up a calculation field, cZeroFlag in this table:

                   Field 1 = 0

                   Select Number as the return type and leave the "Do not evaluate if all referenced fields are empty" check box selected so you can ignore cases where Field 1 is empty.

                   Then you can define a self join relationship that uses Field 2 as the match field. And then a calculation field with this expression:

                   Count ( YourTable2::cZeroFlag )

                   Using Execute SQL:

                   An expression similar to this:

                   ExecuteSQL ( "SELECT Count ( * ) FROM \"YourTableName\" WHERE \"YourTableName.Field1\" = 0 and \"YourTableName.Field2\" = ?" ; "" ; "" ; YourTable::Field2 )

                   should also produce the same count without the extra fields and relationship.