5 Replies Latest reply on Dec 7, 2011 2:30 PM by davidallen

    Trying to count items from a value list

    davidallen

      I have been, without succuss trying to come up with a way to count the items from a value list.

       

      Example: in the DB one of the fields (Vaccine Status) is a value list with: Accepted, Refused, Allergic, Not at Risk, (and others).

      I need to do a count of each for an annual report I am trying to build.

       

      I thought perhaps a calculation field to count each option, cant count the items in a value list.

      I also tried a variety of summary fields with and without sub-summary on the layout, cant count the items in a value list.

      I am unable to make this work, I have looked through the FM Help and several places on line, I have not found

      anything that helps with counting the items in a value list.

       

      If counting a value list cant be done, then is there a way to have a drop menu in a field that can be counted?

       

      Any assistance would be appricated.

       

      The end result should look like this:

       

      Vaccine Status for 2011:

      Accepted 210

      Refused 43

      Allergic 25

      Not at Risk 16

        • 1. Re: Trying to count items from a value list
          datastride

          The “brute force” method, when all possible values are known …

           

          $countOfAccepted = PatternCount( “¶” & $myValueList & “¶” , “¶” & “Accepted” & “¶” )

          $countOfRejected = PatternCount( “¶” & $myValueList & “¶” , “¶” & “Rejected” & “¶” )

          $countOfAllergic = PatternCount( “¶” & $myValueList & “¶” , “¶” & “Allergic” & “¶” )

          $countOfNotAtRisk = PatternCount( “¶” & $myValueList & “¶” , “¶” & “Not at Risk” & “¶” )

           

          Of course with a little looping, you can tally whatever values appear in the value list ...

           

           

          Peace, love & brown rice,

          Morgan Jones

           

          FileMaker + Web:  Design, Develop & Deploy

          Certifications: FileMaker 9, 10 & 11

          One Part Harmony 

          Austin, Texas • USA

          512-422-0611

          1 of 1 people found this helpful
          • 2. Re: Trying to count items from a value list
            RobWestergaard

            Hi, David.

             

            It sounds like a sub-summary report should do the trick.

            1. Create a field in the same table as the Vaccine Status field called "Vaccine Summary". Make it a Summary field that counts the Vaccine Status field.
            2. Create a list view layout. Change the body part to a sub-summary when sorted by Vaccine Status.
            3. On the sub-summary part, put the Vaccine Status field and the Vaccine Summary field.
            4. Switch to Browse mode. Sort the records by Vaccine Status, descending, check the box "Reorder based on summary field" and choose Vaccine Summary as the field. (This will generate the list ordered as you described in your original posting.)

             

            The only thing this WON'T show is any voids (i.e., any Vaccine Status values where the count is zero).

             

            Regards,

             

            Rob

             

            P.S. I wrote all of this up after I couldn't find a way to attach a file. Then I started poking around in the "Advanced Editor" and found it. See attached file if my explanation isn't clear.

            • 3. Re: Trying to count items from a value list

              Hi David,

               

              Actually you don't want to count the 'value list' but rather count the field data that contains each of the value list values.  You mention using a value list but you do not indicate the Control Style (pop-up, checkbox etc) used and it matters.  Are Users only allowed to select one value or multiple for a single record?  From your options listed it seems only one value is the case because a person couldn't check off 'accepted' and 'refused' could they?  If only one value per field (and not a multiline from using checkbox)  then you should be able to use summary fields and count them.

               

              If it is checkbox (multiline list) then it it usually best if those values are in a related table.  It is easy to make that change and split them off.  As a checkbox or multiline list, you cannot count the same record (for example patient JIM) in more than one group for a sub-summary report like this:

               

              Accepted:

              • Jim

              • Linda

              • Mary

               

              Refused:

              • Betty

              • Jim

              • Linda

               

              But Jim CAN be in more than one part of the report if the Vaccine Status' were related records and you generate the report from there.  If you cannot split them off as related, here is an alternate approach in addition to the approach presented by Morgan.  It uses filtered portal (if you use vs 11) and it was created by Comment (Michael Horak); see the last file he presents:

               

              http://fmforums.com/forum/topic/71906-getting-more-out-of-filtered-portals-3unique-values/page__hl__filtered__fromsearch__1

               

              Be aware that filtered portals can be slow but it uses existing primary relationship.  Much depends upon your particular solution, number of records etc.

              • 4. Re: Trying to count items from a value list
                davidallen

                Thanks, this worked perfectly!

                 

                The layout of the report is different than I had envisioned but it will

                work nicely.

                 

                After going through your method I see a couple of places that I had strayed.

                 

                 

                 

                David Allen

                 

                Bio-Med Technician

                 

                Database Administrator

                 

                Ozarks Community Hospital

                 

                dallen@ochonline.com

                 

                417-837-4049

                 

                fax 417-875-4609

                 

                 

                 

                 

                 

                 

                 

                P Please don't print this e-mail unless it's necessary. Reduce, reuse, and

                recycle.

                 

                CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is

                for the sole use of the intended recipient(s) and may contain confidential

                and privileged information protected by law.  Any unauthorized review, use,

                disclosure or distribution is prohibited.  If you are not the intended

                recipient, please contact the sender by reply e-mail and destroy all copies

                of the original message.

                • 5. Re: Trying to count items from a value list
                  davidallen

                  thanks this worked perfectly,

                  the layout of the finished report is different than I had planned, but looks  great,

                  Thanks.