1 of 1 people found this helpful
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.
FM13 Certified Developer
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.
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.)
Subsummary.fmp12.zip 65.4 K
Thank you- I'm not on 12/13 yet but will be by the end of the year.
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.