6 Replies Latest reply on Apr 16, 2015 2:16 AM by AnupSatyal

    Checkbox Pattern Count

    AnupSatyal

      Title

      Checkbox Pattern Count

      Post

      Hello,

      I have checkboxes and pattern counts set up so that I can count the number of specific text strings. For example, in a field where I can check Europe, North America, Asia etc, I have pattern counters set up that can count the number of times Europe occurs in each field and also have summary counters that give me the amount of times Europe occurs in the entire database. 

      I have 600 records that ticks Europe, North America, or only Europe or only North America. When I add up all the individual pattern counts( Total Pattern Counts), i.e Europe+North America, I get 702. This I assume is because something is being double counted. What am I doing wrong?

      Anup

        • 1. Re: Checkbox Pattern Count
          philmodjunk

          A record that has both the Europe and North America check boxes selected will be counted twice will it not? Once for each value?

          • 2. Re: Checkbox Pattern Count
            AnupSatyal

            Exactly, 

            As you said, Individual counts:- Pattern count (Field; "Europe") and Pattern Count ( Field; " North America") check for Europe/ North America on each of the 600 records ( a 1 or a 0 depending on). But then I have a summary( i.e. Summary for total pattern count for Europe) which counts all the 1s to give me a total of Europe and  a similar summary for the  total North America.

            However, the  Sum of the the Summaries ( so Total pattern count of Europe + Total pattern Count of North America) gives me more than 600 records.  What I cannot seem to figure out is why!?

            Anup

            • 3. Re: Checkbox Pattern Count
              philmodjunk

              Exactly,

              A sum of those sub totals will count any record that has both check boxes selected twice, once for each of the two values.

              And why would this be considered as incorrect?

              • 4. Re: Checkbox Pattern Count
                AnupSatyal

                You're right, but what I wanted to see was if I could get a subtotal without the double count. I think there is no way to do so. But thank you for your response!

                Anup

                • 5. Re: Checkbox Pattern Count
                  philmodjunk

                  There is indeed a way to get that count, but not with the fields the way that you have currently set them up.

                  Here's a calculation that returns a 1 for counting or summing by a summary field that will return that value if Europe OR North America OR both is selected:

                  Not IsEmpty ( FilterValues ( YourTable::YourcheckBoxField ; List ( "North America" ; "Europe" ) )

                  But I encourage you to consider replacing your single field formatted with check boxes with a portal to a related table where each related record records the selection of one value from your check box list. This enables much more flexible tabulation and analysis of what "check boxes" were or were not selected.

                  You can find some examples of portals that look and function like a check box formatted field in "Adventures in FileMaking #2 - Enhanced Value Selection".

                  • 6. Re: Checkbox Pattern Count
                    AnupSatyal

                    Thank you for the Enhanced Value Selection file. Given the size and functionalities explained, I will take a few days to review and refine the solutions I currently have. Will get back to you with the progress!