6 Replies Latest reply on Nov 26, 2013 10:27 AM by philmodjunk

    Count records that have fields with certain values.

    MikeProcopio

      Title

      Count records that have fields with certain values.

      Post

           I'm trying to count records that have yes in a certain field. Is there a a calculation that can do this?

        • 1. Re: Count records that have fields with certain values.
          philmodjunk

               I seem to recall posting an answer to this question in other contexts with you. The basic options remain:

               A relationship can be set up that matches to only records with the specified value and then you can count the related records.

               A Found set can be pulled up with just the records to be counted or sorting can group the records to produce groups that can be counted in a summary report using a summary field and (for counting groups) a sub summary layout part.

               Filtered portals can use a summary field to count records where the filter limits the related records to those with the specified value.

               ExecuteSQL, in FileMaker 12, can produce such counts and subcounts.

          • 2. Re: Count records that have fields with certain values.
            AppGuy

                 If you are only looking for a certain value you you could do this with 2 fields.. One would be a calculation field and the other a summary field.

                 Calc field: (this will have the search criteria and the value count.

                 c_calcfieldname - ValueCount ( FilterValues ( fieldname ; "text or what you are searching the field for") )

                 Summary field ( which points to the calc field above)

                 s_sumrecord - = count of c_calcfieldname

                 or

                 s_sumrecord = total of c_calcfieldname

                  

                 depending on what your are looking to do..

                  

            • 3. Re: Count records that have fields with certain values.
              AppGuy

                   sry Phil.. just saw your post..

              • 4. Re: Count records that have fields with certain values.
                philmodjunk

                     No problem, summarizing or counting a calculation field that is empty unless the original field contains a specified value is the one option not listed in my post. wink

                • 5. Re: Count records that have fields with certain values.
                  MikeProcopio

                       Only issue with this is on a "screen" there are three options a made screen, miss screen, and veer. Veer and make are positive but need to be listed differently , but want to be counted as a positive so if a player sets 20 screens in a game made 10 veer 5. In reality it was 15-20

                  • 6. Re: Count records that have fields with certain values.
                    philmodjunk

                         So the finger taps on buttons might generate one of these records in GameStats (See your other post):

                         Player       Made     Veer    Miss
                         23                1
                         23                               1
                         20                                         1
                         18                               1

                         Summary fields and calculation fields with Getsummary can count total stats in each category and combine total made with total veer to get the results that you show.