11 Replies Latest reply on May 31, 2015 2:10 PM by erolst

    Counting Value List with Multiple Selections

    alinicjones

      I've read through number of discussion on this on the forum but still can't seem to get it to work properly. I have a database that audits research studies. I have a form where they select codes on the form where i have a value list check boxes of 43 different codes they can choose from. They might choose none or 10 options. I need to come up with a way to do a report that counts the number of times each of the individual codes have been chosen. I started the process with a very lengthy option that doesn't seem to work where i created a field for each code and used this formula: If ( RT_Code = "Assent"; "1" ; "" ). Then i did a count of each code. The numbers are not matching up. Any suggestions on a better way to do this? Any help would be greatly appreciated!!

        • 1. Re: Counting Value List with Multiple Selections
          erolst

          alinicjones wrote:

           

          I've read through number of discussion on this on the forum but still can't seem to get it to work properly.

           

          I'm pretty sure that at least some of these discussion mentioned that for purposes such as yours – stats, reports– , a list field (which a field formatted as a checkbox essentially is, or will become) aren't the appropriate tools.

           

          issue #1: how to calculate this without a lengthy, maintenance-heavy calculation?

           

          issue #2: where to put the (individual) results? (“Create a field for each code” falls under heavy maintenance; not to mention you'll also need a summary field (or something else) for each code to total the found set …)

           

          issue #3: when you try to create a report, you'll find that you have a larger total of selections in your field than records to evaluate … i.e. you don't have enough records to display all results

           

          You should use a related table where each of your current checkbox selection would be a single record; then you can simply use a layout based on that table with a sub-summary part and a summary field that counts a "code" field and, when sorted, shows the correct result per group. With each code selection being represented as a record, you can use all the usual summary techniques.

           

          The problem with your first attempt to calculate the results is that a field is not necessarily equal (=) to any value if it can have multiple values; you could either use PatternCount(), or, if some values are substrings of others, play it safe and use

           

          not IsEmpty ( FilterValues ( "Assent" ; RT_Code ) )

           

          will hold a 1 or a 0, depending. But as explained above, this is just a patch, not the solution.

          • 2. Re: Counting Value List with Multiple Selections
            erolst

            btw, if you have FM ≥13, you can do this to create a report in text form:

             

            • create a summary field “List of” for your checkbox field

             

            • Write a script:

             

            Set Variable [ $resultList ; "" ]

            Set Variable [ $allCodeSelections ; YourTable::sListOfCheckboxField ]

            Set Variable [ $allCodes ; ValueListItems ( "" ; "YourCodeValueList" ]

            Set Variable [ $codeCount ; ValueCount ( $allCodes ) ]

            Loop

              Exit Loop if [ Let ( $codeCounter = $codeCounter + 1 ; $codeCounter > $codeCount ) ]

              Set Variable [ $currentCode ; GetValue ( $allCodes ; $codeCounter ) ]

              Set Variable [ $$resultList ; list ( $$resultList ; $currentCode & Char(9) & ValueCount ( FilterValues ( $allCodeSelections ; $currentCode ) ) ) ]

            End Loop

            # do something with $$resultList

            • 3. Re: Counting Value List with Multiple Selections
              thurmes

              The problem with the snippet of code you gave us [ If ( RT_Code = "Assent"; "1" ; "" ) ] is that if they chose two options (both "Assent" and "Implied"), you'd get a null for this snippet, even though it does contain "Assent".

               

              Here's how I would do it (it could be more elegant, but it doesn't sound like you have to do this very often):

              First, go to Brian Dunning's Custom Function site and get the ContainsValue CF. (I assume you have FMP Advanced - otherwise you get to do it the slow way.)

               

              Set Variable [ $ValList ; Value: ValueListItems ( Get ( FileName ) ; {your value list's name] ) ] //you now have all those codes in a variable

               

              Go to first record

               

              Loop

              Set $count = 0 //The actual formatting in the script will be like the example above, but I'd rather not type too much

               

              Loop

              Exit loop if [Let $count = $count + 1 ; $count > ValueCount ( $ValList )

               

              Set Variable $HitCount[$count] = $HitCount[$count] + ContainsValue ( RT_Code ; 1 ; GetValue ( $ValList ; $count ) ) //this last part will be 0 if it's not there, 1 if it's there

               

              End loop //checking whether each code is used for this record

               

              Go to next record, exit loop if last

              End loop //going through all records

               

              /*At this point you have a variable with 43 repetitions representing the number of times each code was used in all the records, and a value list with 43 values in it. You could not mix things up like that by having both be value lists, using the PutValue CF at Dunning's website, and starting everything off by having $HitCount be a value list with 42 ¶s. I'm not sure what kind of report you need, but this should start you on your way*/

              Hope this helps

              • 4. Re: Counting Value List with Multiple Selections
                siplus

                In such situations I use arrays (i.e. repeating fields).

                 

                In the attached example you can see 5 questions with 50 different options for each and the total of times each option has been chosen across all questions.

                 

                The rest is user interface (not included, except for an implied proposal about how to toggle values and a check for max 10 values per question).

                • 5. Re: Counting Value List with Multiple Selections
                  erolst

                  That answers the "where do I store all that stuff somewhat dynamically" question.

                   

                  Now give us that list sorted by code count ...

                  • 6. Re: Counting Value List with Multiple Selections
                    erolst

                    thurmes wrote:

                    First, go to Brian Dunning's Custom Function site and get the ContainsValue CF. (I assume you have FMP Advanced - otherwise you get to do it the slow way.)

                    […]

                    You could not mix things up like that by having both be value lists, using the PutValue CF at Dunning's website

                     

                    Or simply get familiar with the native FM functions – namely, FilterValues() and List(). That is usually the fast way.

                     

                    Some CFs not only try to reinvent the wheel, but aren't even good at it. Do you need a recursive function to check if a list contains a value?

                    • 7. Re: Counting Value List with Multiple Selections
                      siplus

                      I guess this is what you mean/want. Click on any cell to start the enchilada.

                      • 8. Re: Counting Value List with Multiple Selections
                        siplus

                        ore use this one (no initial click needed)

                        • 9. Re: Counting Value List with Multiple Selections
                          thurmes

                          You're right, that wasn't the best way to do it. Sorry, I wrote that at the end of a long day.

                           

                          A more elegant way of counting the incidences through a loop (siplus has another neat approach) involves looping through the list of RT_Code values (max 10) rather than the list of choices (43). And, as you pointed out, using List to eliminate multiple reads from fields.

                           

                          So, inside the loop for each record

                          Set Variable $RT_CodeList = List ( RT_Code )

                          Set Variable $count = 0

                          Loop

                          Exit loop If [ Let $count = $count + 1 ; $count > ValueCount ( $RT_CodeList ) ]

                          Set Variable $RC_Position =

                               Let ( [  L =  ¶ & $ValList & ¶ ;

                                         S = ¶ & GetValue ( $RT_CodeList ; $count ) & ¶ ;

                                         P = Position ( L ; S ; 1 ; 1 ) ] ;

                                         PatternCount ( Left ( L ; P ) ; ¶ ) )

                          Set Variable $HitCount[$RC_Position] = $HitCount[$RC_Position] + 1

                          End Loop //looping through RC_Codes

                          • 10. Re: Counting Value List with Multiple Selections
                            thurmes

                            I was inspired by siplus's files (I learn something new every time I examine his files), and came up with this file, in which the count of each checked option is given in a calculation field in each record. These counts would presumably be summed on a layout elsewhere in the file.

                            • 11. Re: Counting Value List with Multiple Selections
                              erolst

                              thurmes wrote:

                              These counts would presumably be summed on a layout elsewhere in the file.

                               

                              Just define a summary field sTotalOfDayCount and specify "Summarize Repetitions: Individually”.