11 Replies Latest reply on Oct 27, 2016 8:24 AM by fmpdude

    How can I find the number of records with specific values?

    gespolin

      I obviously don't understand how the Count function works. It's said to return "the number of valid, non-blank values in field", but I cant't make it do just that. Here is a simple example:

      Let's say I have registered observations of two kinds of birds (A and B) in two different countries with a number of counties in each country.  I then want a report sorted by country and county, showing the number of observations of A and B respectively.

       

      Country 1

           County 1   

                Count of A     Count of B

           County 2  

                Count of A     Count of B

           County n   

                Count of A     Count of B

       

      Country 2

           County 1a   

                Count of A     Count of B

           County 2a

                Count of A     Count of B

           County m   

                Count of A     Count of B

       

      Whatever I do and however I arrange my layout parts, the Count function returns the number "1" for every entry. I would appreciate very much if someone can expplain what I need to do.  —  Sincerely and hopefully, gespolin

        • 1. Re: How can I find the number of records with specific values?
          noel_ferron@msn.com

          Hi,

          I had the same question yesterday.  My table has a field named z_Record_Type, and I wanted to know how many records of each type it contained.

          I have created a calculation field named. Z_Count_Type_A which contains a 1 when  z_Record_Type = A and a second one named   Z_Count_Type_B which contains a 1 when z_Record_Type = B

           

          The rest was easy.  1 statistical fields count based on z_Count_Type_A. And another one based on z_Count_Type_B.

           

          Note : in english, statistical fields are name differently, but I dont remember how this type is called in english.

           

          Hoping it will help you.

          • 2. Re: How can I find the number of records with specific values?
            Stu412

            Hi there

             

            Looks like you need a summary field in this case to give me the layout part values I wanted.  You might have something like the following in your data. I'm guessing that bit!

             

            Country     County     ObservedA     ObservedB

             

            A               North       1              

            A               North       2                      1

            A               South       2                      1

            A               South       4                      3

             

            Create two further fields:

             

            sObservedA (Summary type referring to ObservedA)

            sObservedB (Summary type referring to ObservedB)

             

            Set your layout parts up with the following (which I'd guess you have already):

             

            Country (Leading)

            County (Leading)

             

            Remove the body part, it's not needed

             

            Place the two summary fields on the layout part for County next to each other

             

            You should get something like:

             

                                                    sObservedA          sObservedB

            Country A

                      County North          3                              1

                      County South          6                              4

             

            If you wanted to get grand totals per ObservedA and ObservedB, then put a copy of the sObservedA and sObservedB field on the Country part.

             

            Hope that helps

            • 3. Re: How can I find the number of records with specific values?
              gespolin

              Thank you very much. My problem was that instead of making a count-of summary field, I had made a calcualtion field using the count operator. Logic is not always that simple!

               

              Gratefully Georg

              • 4. Re: How can I find the number of records with specific values?
                philmodjunk

                The count function is designed to be used in one of three ways:

                 

                Count ( Field1 ; field2 ; field3 )-->returns a number from 0 to three from just these fields in the current record

                Count ( RepeatingField ) ---> counts the number of non-blank values from the repetitions of this field

                Count ( RelatedTable::Field ) ----> counts the number of non-blank fields from the set of related records in a table linked by relationship to the table occurrence used as the "context" for this calculation.

                 

                Which is why, as you discovered, it didn't work for you. Count ( Field1 ) can only return a value, at most of 1 as it's only referencing a single field in a single record.

                • 5. Re: How can I find the number of records with specific values?
                  fmpdude

                  Has anyone posted a request that the COUNT function have another (internal to FMP, of course) overloaded method -- to actually count the number in the table it's in without using a self-join or other gymnastics?

                   

                  Being able to get a COUNT of the table I'm in (a single table solution, for example) seems like the most expected use-case. Yet, it's not there.

                   

                  Baffling.

                  • 6. Re: How can I find the number of records with specific values?
                    philmodjunk

                    You'd need to check that for yourself and see what has been posted over in Product Ideas. I can't say that I've ever had a major need for such as the available methods aren't all that hard to use, but by all means, if you don't find it posted over there, post it and see what others have to say about it.

                    • 7. Re: How can I find the number of records with specific values?
                      fmpdude

                      As an expert, you can always devise a workaround. I get that.

                       

                      IMHO, though, it's really a matter of semantics and expectation.

                       

                      In excel, for example, count works like you'd expect: in the range in THAT sheet.

                      In SQL:  same table (as in: "select count(*) from names")

                       

                      Why FM is different is needless.

                       

                      COUNT, SUM, etc. should simply be overloaded (in the (hopefully, encapsulated) FM code) to handle counts, sums, etc., on the same table. Simple. Expected. Obvious.

                       

                      Thanks for your reply, PMJ.

                      • 8. Re: How can I find the number of records with specific values?
                        beverly

                        there are quite a few Excel features that would be handing in a Table of data in FileMaker. And Count() or other aggregates working on fields in a found set to work as the summary fields work, but into CALCULATED (or Set Field) way would be handy!

                         

                        Yes GetSummary()

                        works on the found set and returns the value as it would be in the summary field (based on the sort), but the summary field is first created to be used in this function.

                         

                        If I read your posts correctly, fmpdude, this is what you are saying:

                        a 'GetSummary()' without first needing to create the summary field?

                        or

                        eSQL working on a found set to give us these aggregate values?

                         

                         

                        beverly

                        • 9. Re: How can I find the number of records with specific values?
                          beverly

                          hate that auto-correct that is anything but "correct".

                          I can't edit at the moment...

                          change "handing" to "handy" in the first paragraph above!

                          • 10. Re: How can I find the number of records with specific values?
                            fmpdude

                            No, what I was saying was that the COUNT, SUM, and other functions should (IMHO) automatically work on the same table without self-joins, summary fields, or other special steps. All new users to FMP bump into this counter-intuitive behavior - that is that COUNT doesn't work on the same table as expected.

                             

                            I can't count the times philmodjunk helped me with things like this when I was just a babe in the FMP woods. (Thanks again, Phil!)

                             

                            So, how to do this?

                            Programmatically, speaking, FMP, assuming it's object oriented internally would just need another overloaded function for SUM, COUNT, etc., which, based on the input parameters would decide how to do the SUM, COUNT, etc.

                            So, instead of three overloaded functions for COUNT like it has now (for the three use-cases COUNT currently handles), it would have four. At run-time, the correct COUNT function/method would automatically be chosen to run (from the OO programming language FM is written in) given the particular parameter list sent.

                             

                            Since we're at version 15, and it doesn't work like this, I didn't submit a feature request.

                             

                            FMP is fine the way it is. This is a minor enhancement I wish had been there for a while.

                            • 11. Re: How can I find the number of records with specific values?
                              fmpdude

                              Yes, like GetSummary() without the summary field creation requirement, but also COUNT, SUM, etc., should just work without needing to call a separate function name.

                              That's the beauty of OO: you can call the same function name and IT (the language) can figure out which of possibly several different methods to call to run the right code.

                              So, you call COUNT, SUM, etc., and the language (behind the scenes) determines the right code to run.

                              As FM developers, we then just call COUNT, SUM, etc., and it just ... Works!

                              The beauty of this approach is you call the same function, and based on what you're doing, IT figures out what to do.

                              However, I do not know any internals of FMP and thus have no idea how they have implemented ... anything.