4 Replies Latest reply on Sep 19, 2012 7:34 AM by ErinDeloreto

    Count Field Where Value = "Y"



      Count Field Where Value = "Y"


           I'm trying to create a new calculation field to Count all of the "Y" values in an existing field (Diabetes). Here's what I programmed:

      ValueCount ( Diabetes = "Y" )

           Instead of counting only the "Y" values, it's counting all values regardless of whether they are "Y" or not. Can someone help me with this syntax or suggest another method of counting my "Y" values?

           Much appreciated!


        • 1. Re: Count Field Where Value = "Y"

               ValueCount counts the number of elements in a list of values. Your list of values in the above expression is a list of one value that is either 1 (True) if there is a "Y" in diabetes and a 0 (False) if it does not. Thus, it should return a 1 everytime.

               Are you counting records where there is a "Y" in the diabetes field or are there multiple entries in the same diabetes field of the same record and you need to know how many of them are "Y"?

          • 2. Re: Count Field Where Value = "Y"

                 Thanks for replying to my post. The "Diabetes" field has one entry per record: either a "Y" or an "N." I'm trying to count the "Y" values across all my "Diabetes" records.

            • 3. Re: Count Field Where Value = "Y"

                   Diabetes is a Field not a record. There are three ways that you can count the number of records that have a "Y" in the diabetes field. This assumes that you have a single diabetes field in your table and that it is not a repeating field.

                   1) Count the records as part of a found set. You can perform a find for all records where diabetes has the value "y" and then Get ( FoundCount ) or a summary field defined as the "count of" any field in your table that is never empty will return the total number of records with this value. Using  sub summary layout part, you can sort your records by the diabetes field, define the sub summary layout part to be "when sorted by diabetes" and then you can place the summary field in this layout part to see a count of the records in your found set that have "Y" in the diabetes field.

                   2) Count the records via a relationship. Define a relationship to this table (it can be a "self join" that links a table to itself) that matches only to records with "Y" in this field. Then either Count ( relatedTable::NeverEmptyField ) or the summary field described in 1 above can count the related records.

                   3) If you are using FileMaker 12, there are ways to count these records using ExecuteSQL.

              • 4. Re: Count Field Where Value = "Y"

                     Thanks so much! I'll try these out :)