5 Replies Latest reply on Sep 11, 2014 12:49 PM by barraa007

    Subsummary report with multiple field values per record


      I have a field with a checkbox set that allows more than one choice per record for that field. I want to run a subsummary (and ultimately make a chart) for the choices, knowing all my choices will be higher in number than my records (multiple choices per record). How do I run a subsummary on all of my field choices? I can only get per record results.


        • 1. Re: Subsummary report with multiple field values per record

          Any list or portal in FM is going to show one row per record, which is a limitation in the case you are describing. There are several ways to work around this, not limited to those here:


          1. Create a table for your options in the checkbox set, and join records between your main table and that table. Then run a report of the join table. This probably seems like a lot of moving parts, but has some side benefits like allowing a power-user to update the checkbox values easily and also giving you a place to store more information about record A and the selection of option x, so sometimes it's the way to go.


          2. Create a report layout, with a global variable in the header for where you can set one of the checkbox options. Run the report for each relevant option, replacing the global variable, and instead of printing create a pdf with an append option, so you end up with "one" report that's really a report for each option, combined into one report.


          3. Explore virtual lists, perhaps, but not necessarily, with ExecuteSQL. If you're not familiar with virtual lists, I'd suggest other sources rather than this brief response. They're powerful, but there's a bit of a learning curve involved.


          4. Create a table of your options (as with item 1) and create a relationship from each to the records you want to report. Then include a portal under each for your report. You'll want to use print sliding so that you only see as many portal rows as are needed under each option. The big downside here is that you have to define the maximum number of records that might show under each option (as your number of records) but if it's a safe assumption that you won't have more than, say 100 rows, this might be a good down and dirty approach.


          I'm sure there are other options, and which of the above are good ideas depend a lot on other factors and what you're comfortable with. I hope this gives you some good ideas to try, though.


          Chris Cain

          FM13 Certified Developer

          Extensitech, LLC



          1 of 1 people found this helpful
          • 2. Re: Subsummary report with multiple field values per record

            The field with the checkboxes will be stored as a return-separated list of values, so if your report field is only tall enough to show a single value you wil only see the first value that was checked. This could be addressed in either of the following ways, depending on your need:


            1.     If you need to keep the list format for this data, make the field tall enough to display all possible checked values, and set the field to sliding up when printed


            2.     If you want to display all checked values in a sinlge row, create a calc field which substitutes say a comma for the carriage returns, so that the checked values display thus:  value 1, value 2, value 6, value 8. The calc can also add other text if needed, eg:  Values checked are:  value 1, value 2, value 6, value 8


            I use the second method to display an address on a single line, principally fora value list, but occasionally for other purposes too.


            Hope that's of some help.

            • 3. Re: Subsummary report with multiple field values per record

              Are you using FMP 13? If so, you may want to try the new Summary (List Of) feature. I think it would provide you with the data you want, whether you use it in a Subsummary report or otherwise.


              Attached is the file from which the following screenshot was taken. I've checked various choices for each record, and my collective choices appear in my Summary List. (I've also tried to incorporate keywords' suggestion regarding a csv list alternative to a return delimited list.)





              Screen Shot 2014-09-02 at 10.42.44 PM.png

              • 4. Re: Subsummary report with multiple field values per record

                Thank you- I'm not on 12/13 yet but will be by the end of the year.


                • 5. Re: Subsummary report with multiple field values per record

                  Thank you- I need to ultimately create a chart with all the answers, though. I am going to try the Virtual List option and see if I can make it work.