13 Replies Latest reply on Sep 24, 2011 6:46 PM by dbail22@comcast.net

    How to count checkboxed items?

    UnionGuy

      Title

      How to count checkboxed items?

      Post

      I have a lot of checkboxes in my organization's database, and I want to count the number of checked items in a particular field. In other words, I might have 500 records showing, but only 200 of them have the checkbox "Own a car" in them. How do I get FMP 11 to count the number of records that have the checkbox "Own a car" checked on? There is only on value in the value list for these checkboxes. (I miss AppleWorks' Count 2 function, which did just this so elegantly.) Thanks.

        • 1. Re: How to count checkboxed items?
          symbister

          I could only suggest running a find on that criteria, then returning the found count...

          • 2. Re: How to count checkboxed items?
            UnionGuy

            That would work, except that I do not want to run separate finds for the 20 or so fields I need to count and show on one layout. I need one layout that has all the amounts showing at one time.

            I did come up with this workaround: Create a calculation field using PatternCount to return a 0 or a 1 if the checkbox in a particular record has the textstring associated with it being checked on, and then create a summary field totaling the aforementioned counts (the 1s). For example, if I want to count the number of people who checked "Own a car" in a Transportation value list, then I would create a field (call it "Own a car count") that uses the calculation PatternCount (Transportation;"Own a car"), where Transportation is the field and "Own a car" is one of the checkboxes in the value list. Then I can just do a summary total on the "Own a car count" field. Rather clumsy, but it works.

            I tried to sum the PatternCounts, but that function is looking for a field, not a textstring, so that did not work.

            Appleworks had its drawbacks, but it was light years ahead of other databases in some respects. In the above example, Appleworks just returned a 1 or a 0 if a checkbox was On or Off, respectively. That made it easy to total them right off the checkbox itself.

            Thanks for the reply.

            • 3. Re: How to count checkboxed items?
              raybaudi

               A lone summary ( count of that field ) will do that count just well.

              • 4. Re: How to count checkboxed items?
                UnionGuy

                Godo idea, but when I tried to create a summary field on the checkbox field, that field was not available, perhaps because it is formatted as text (as checkboxes place the text of the value in the field data) and not as a number. I also tried to sum it and got the same problem. Thanks for trying, though.

                • 5. Re: How to count checkboxed items?
                  LaRetta_1

                  "How do I get FMP 11 to count the number of records that have the checkbox "Own a car" checked on? There is only on value in the value list for these checkboxes. "

                  Is that a typo or are you saying there is only ONE value in this checkbox field?

                  If only one value is allowed then radio button should be used instead of checkbox and you can create a report layout which groups by this field and counts the entries.  Using a checkbox is akin to creating a mini table within your table.  It can be handy to allow multiple selections but it can not handle reporting and summarizing by entry.   The reason is simply that a single record cannot appear (or be counted) in multiple groups (summaries). 

                  If you have a checkbox which needs to generate counts 'per multiple selection within' then you have two options:

                  1)  Create a ValueLists table with a unique ID and the checkbox value.  Then in your checkbox value list options, choose 'use values from field' and select this new VL table.  Specify the ID in the left pane and 'also show second field' in the right pane.  Then below, specify 'show values only from second field' and make the selector a pop-up menu instead of a drop-down list.  The relationship should look like:

                  Data::ValueListID = ValueLists::ValueListID

                  In your data table, create a new field called s_countIDs which is type SUMMARY and specify 'count of unique ID' in that table.  Now go to a layout based upon Value List table and add this new Data::s_countIDs field.  You can sort by value list 'answer' or sort by s_countIDs.

                  2) Create a join table where each unique 'combination' is a record.  So it would hold the data ID and the value.  Then generate reports from this join table ( sort and summarize by the value ).

                  • 6. Re: How to count checkboxed items?
                    UnionGuy

                    So, LaRetta, you are saying that radio buttons should be used instead of checkboxes if there is going to be only one value? I did not realize that radio buttons can be counted. I will try that. Thanks.

                    • 7. Re: How to count checkboxed items?
                      UnionGuy

                      Ok, I changed the checkbox (with one value) to a radio button and then created a summary count field. It is counting every single record, not just the ones with the radio button turned on. Any way to count just the ones that are on?

                      • 8. Re: How to count checkboxed items?
                        bumper

                        The following counts the individual values and can be used on a layout or in a report.

                        Create a calculation field, do not store results, text results. Replace your values, etc., in the following calculation:

                         

                        "Value1:"  & PatternCount ( List ( CheckboxField ) ; "Value1" ) 

                        & ¶ & 

                        "Value2:"  & PatternCount ( List ( CheckboxField ) ; "Value2" ) 

                        & ¶ & 

                        "Value3:"  & PatternCount ( List ( CheckboxField ) ; "Value3" ) 

                        & ¶ & 

                        "Value4:"  & PatternCount ( List ( CheckboxField ) ; "Value4" ) 

                         

                        Create one line for each value in you value list. In the classic sense a field should contain only one value, however, FMP allows otherwise. One of the real gurus of FIleMaker, Kevin Frank, just released a work of art for counting items for a traditional report on FileMakerHacks.

                        • 9. Re: How to count checkboxed items?
                          UnionGuy

                          All this work just to count how many checkboxes are on! The 15-year old AppleWorks program (software way ahead of its time) had a Count2 function that just counted a checkbox as 1 if it was on and 0 if it was off, and then woulf count them all up. FMP has no similar function and requires all kinds of work arounds. Alas.

                          I appreciate all the help. This simplest solution was just to do a pattern count for each checkbox and then a calculation field to count them up. 2 functions, but it is simple and works fine, even if it does double the number of fields needed to do the count.

                          • 10. Re: How to count checkboxed items?
                            LaRetta_1

                            You can't use Count() which counts any non-empty field.  You need to use summary 'Total of' checkbox field.

                            • 11. Re: How to count checkboxed items?

                              Name the field "Own a Car"

                              Create a value List with the number 1 as the only value

                              Assing that value list to own a car as a checkbox.

                              Now you have a zero or a 1 in the field. Easy to sum...

                              You have to shrink the field so that it is just a bit larger than the checkbox and add a text label.

                              When you click the checkbox, one is put into the field. When you uncheck it, zero is put into the field. Easy to sum or summary.

                              • 12. Re: How to count checkboxed items?
                                UnionGuy

                                Good thinking. That works about as well as creating a count field to put a 1 in the field and then sum or summary that up. The extra field stays hidden in the field list rather than a text label needing to be added in a layout. Plus if that text label ever disappears, it might be difficult to figure out what the field is that just says 1 or 0, especially when there are 30 other fields that just say 1 or 0. I like seeing the field name right in the checkbox field.

                                Thanks for the creative thinking!

                                • 13. Re: How to count checkboxed items?
                                  dbail22@comcast.net

                                  Check boxes can be summed or counted if your entry is spcific.  If you make the field a number and when selected use a value list consisting of the number 1.  Size the box to not show the number.  When selected it will equal 1, when not it will equal zero.  A summary or SUM(xxx) will give you the total for the current found set.  If you have multiple check boxes and multiple records then use Sum ( field {; field…} )