7 Replies Latest reply on Jun 7, 2016 8:25 AM by mikebeargie

    Summary of checkboxes

    timmcmanus

      I have a field named Funding that uses a value list from another file, and the value list is displayed as 15 checkboxes. The value list in the other file can be edited, so it can change over time.

       

      I want to create a summary report counting the number of times a value appears in Funding.

       

      For example, I may have data like this:

       

      Funding
      Cash

      Cash

      Check

      Donation

      Credit

      Cash

      Credit

       

      My summary report would look like this:

       

      FundingCount
      Cash3
      Check1
      Credit2
      Donation1

       

      I tried to create a sub-summary report with Funding and a summary field that was a Count of the Funding field. This works great counting the total amount of records using the Funding field, but I cannot figure out how to create a summary for each individual value. To further complicate things, the value list that creates the checkboxes will change over time, so I cannot rely on the value list to return all possible values entered into the field. I need to be able to support historical reporting, so I need to be able to report on all possible values in the Funding field.

       

      Can anyone give me any guidance to approach this dilemma? I'd prefer not to change the schema or the implementation of the Funding field.

        • 1. Re: Summary of checkboxes
          mikebeargie

          Honestly I'd use ExecuteSQL to do this, it's context free and super easily to count this:

           

          ExecuteSQL (

          "

          SELECT COUNT(dataField), dataField

          FROM table

          GROUP BY dataField

          ";"";""

          )

           

          That's it, will return something like this to you:

          3,Cash

          1,Check

          2,Credit

          1,Donation

          1 of 1 people found this helpful
          • 2. Re: Summary of checkboxes
            jonasmn

            Hi,

             

            Time has passed, but I found myself having exactly the same problem, so I post a message here and hope for an answer.

             

            1) Question 1

            In the SQL-script above, would dataField be replaced by "Funding", like this:

             

            ExecuteSQL (

            "

            SELECT COUNT(Funding), Funding

            FROM table

            GROUP BY Funding

            ";"";""

            )

             

             

            2) Where will the output come?

            I wonder where the output will come, in a multi-line text in a RESULT-text field?

             

            (I am very new to SQL-scripting.)

             

             

            Regards

            Jonas Möller Nielsen

            Sweden

            • 3. Re: Summary of checkboxes
              mikebeargie

              Jonas,

               

              You are correct that the field name "funding" would be used. I made a generic calculation to demonstrate that you can use any field name or table name there, as this technique is useful in a number of different places.

               

              For display, You create a calculation type field in your table that runs that calculation, and outputs the result as text. You can then display that field wherever you need it.

               

              Alternatively, you can use a script that sets a global variable:

              Set Variable [ $$display ; ExecuteSQL( etc.. ) ]

               

              Then insert a merge variable on your layout to display the result:

              <<$$display>>

              • 4. Re: Summary of checkboxes
                jonasmn

                Mike,

                 

                I do not get it working, I only get a "?" in my calculated field (output as text). This is my script:

                 

                ExecuteSQL (

                "

                SELECT COUNT (RESULTAT_Ansökan), RESULTAT_Ansökan

                FROM Huvudtabell

                GROUP BY RESULTAT_Ansökan

                ";"";""

                )

                 

                the field with the check-boxes is "RESULTAT_Ansökan"

                and the table is "Huvudtabell".

                 

                Any idea?

                • 5. Re: Summary of checkboxes
                  mikebeargie

                  The field name:

                  RESULTAT_Ansökan

                  has some special characters that may not be good for use in SQL. You can escape the special characters by quoting the field name like this:

                   

                   

                  ExecuteSQL (

                   

                  "

                   

                  SELECT COUNT (\"RESULTAT_Ansökan\"), \"RESULTAT_Ansökan\"

                   

                  FROM Huvudtabell

                   

                  GROUP BY \"RESULTAT_Ansökan\"

                   

                  ";"";""

                   

                  )

                   

                  Also, what type of field is RESULTAT_Ansökan? Is it a calculation field or summary? It may not be a type that you can count.

                  1 of 1 people found this helpful
                  • 6. Re: Summary of checkboxes
                    jonasmn

                    I renamed the "RESULTAT_Ansökan" to "RESULTAT_Ansokan". The name is not a big deal. But now I get output!

                     

                    But unfortunately the script does not sort out the different check-box options, so each string combination is treated as a separate unit. To take the example above:

                     

                    1,Cash

                    1,Cash, Check, Donation

                    1,Credit, Cash

                    1,Credit

                     

                    Since the check-box alternatives are hard coded, maybe I should make a script with hard coded search criteria instead?

                    • 7. Re: Summary of checkboxes
                      mikebeargie

                      It is outputting the result correctly based on your data.

                       

                      you could script it, or you could modify the calculation to find for each criteria separately and assemble it into a list:

                       

                       

                      List(

                       

                      ExecuteSQL ("SELECT COUNT (RESULTAT_Ansokan) FROM Huvudtabell WHERE RESULTAT_Ansokan LIKE ?";"";""; "%Cash%") & ", Cash";

                       

                      ExecuteSQL ("SELECT COUNT (RESULTAT_Ansokan) FROM Huvudtabell WHERE RESULTAT_Ansokan LIKE ?";"";""; "%Check%") & ", Check";

                       

                      ExecuteSQL ("SELECT COUNT (RESULTAT_Ansokan) FROM Huvudtabell WHERE RESULTAT_Ansokan LIKE ?";"";""; "%Donation%") & ", Donation";

                       

                      ExecuteSQL ("SELECT COUNT (RESULTAT_Ansokan) FROM Huvudtabell WHERE RESULTAT_Ansokan LIKE ?";"";""; "%Credit%") & ", Credit"

                       

                      )

                      1 of 1 people found this helpful