5 Replies Latest reply on Jul 22, 2013 4:44 PM by keywords

    Counting items in a valuelist

    twalz

      I have a valuelist with 8 items, say A,B,C,D,E,F,G. They are displayed in the field as a checkbox set. My issue: I need to count and total up the occurrence of each value. When only one item in that list is checked, no problem. I haven't figured out how to count each item individually when more than one item is checked.

        • 1. Re: Counting items in a valuelist
          coherentkris

          ValueCount (yourfield)

          • 2. Re: Counting items in a valuelist
            erolst

            Do you mean: count and total up the individual entries across several records? (xAs, yBs, zCs etc?)

            • 3. Re: Counting items in a valuelist
              twalz

              Yes.. Record 1 might have only A checked; Record 2, A and C; Record 3, B and D, Record 4, A and E. I want to get the correct total across those four records that value A occurred three times.

              • 4. Re: Counting items in a valuelist

                Hi Twalz,

                 

                Checkboxes are not the right tool when you need to report on the results - related records would be the right choice.  One reason it's best to use records for things that are alike is that records can easily be organized into sets (found or related) and these sets in turn are easy to sort and aggregate.

                 

                If you have a single record with A and C checked, that single record cannot appear in both groups for A and for C since a single record can only appear in one place. You can of course use PatternCount() but changing the structure would be the best recommendation.

                 

                If you wish to count ALL records then you can create a self-join (required to see all records within a table) using X (Cartesian product operator).  You would then create a calculation (result is number) with something like:

                 

                PatternCount ( List ( selfjoin::checkbox ) ; "A" )

                 

                You can also just perform a find for A then count your found set, write it to a variable, perform a find for B ... and so on.

                 

                If you must get your totals based upon FOUND SET then you will need to create a calculation (result is number) as:  Self = "A"

                 

                This will return boolean true (1) and then a summary field which counts this calc.

                 

                Overall, changing structure is simplest and will prevent these issues in the future.  :-)