5 Replies Latest reply on Jul 14, 2015 11:34 AM by philmodjunk

    Count a column of names

    maxmorley@verizon.net

      Title

      Count a column of names

      Post

      In my Rotary Club I have a category, "MEMBERSHIP." Then, the designation "Active," "Honorary," "Exempt."  I want my field to count how many "Actives" we have in my column footer part. AND which footer part do I use?

      Thanks.

        • 1. Re: Count a column of names
          philmodjunk

          See this link on a "summary recap" feature: FMP 12 Tip: Summary Recaps (Portal Subtotals)

          As to which part?

          If you have a multi page report you could:

          Put it in the title footer to see it at the bottom of only the first page
          put it in the footer to see it at the bottom of every page
          put it in a trailing grand summary to see it immediately after the last record listed in the body.

          • 2. Re: Count a column of names
            maxmorley@verizon.net

            I want my field to count only "Active" s.  It is now counting all entries.  How do I make it count only "Active?"

             

            Thanks

            • 3. Re: Count a column of names
              philmodjunk

              Please see the details of the link that I have provided for a summary recap.

              • 4. Re: Count a column of names
                maxmorley@verizon.net

                So sorry. I'm not very advanced in FM.  That explanation is much too complicated for my place on the FM learning curve. I just don't understand it.

                I have created a field named, "Count Membership Type." That field appears in my Trailing Grand Summary, and I want it to count how many times the word "Active" appears in my field, "Membership Type."

                How do I do that?  I appreciate your patience with me.yes

                • 5. Re: Count a column of names
                  philmodjunk

                  The method described in that link does not use such a summary field. It uses an ExecuteSQL query in an unstored calculation field. While SQL may be unfamiliar to you, it's actually simpler in this instance than the nonSQL alternative. The alternative requires adding a table with one record only for each of your "category" values and then you set up a portal to that table with relationships and an extra table occurrence of your report's table that allow either a summary field in your report's table or a calculation field with Count() in this new table to produce the correct counts.

                  What can make this even more complex is if your report, as is usually the case, is not based on all records in your table but rather a subset of the total. It can be tricky to set up a relationship that links by category while still only linking to the same records in that category as are used in your report.

                  So what type of criteria do you specify when setting up your report? (Or would you rather try ExecuteSQL after all.)