Reporting Subsummary results from a CheckBox Set

[Raymack]

I've searched diligently for a solution to this problem and was surprised there are not more solutions suggested. The best I've found is an .fp7 demo file for MagicKeys that I could not open; anyone have a later demo version of Magic Keys? Any other suggestions/methods will be appreciated!

[comment]

3 hours ago, Raymack said:

The best I've found is an .fp7 demo file for MagicKeys that I could not open;

Are you referring to the demo file in this article?
https://filemakerhacks.com/2011/07/31/magic-key-and-check-box-reporting/

If so, I had no problems converting it to .fmp12 format and opening it. Try the attached.

 

3 hours ago, Raymack said:

was surprised there are not more solutions suggested

Well, the best solution I can suggest is that if you need to produce summary reports, then do not store your data in checkbox fields - use related records instead. IIUC, the demo file actually creates a related record for each checked value - so you might just as well go directly to the required format and skip the intermediate stage. Note that it is possible to build a user interface that behaves like a check box - but behind the scenes every click creates or deletes a related record.

--
P.S. Please use the default font when posting.

 

magic key check box reporting.fmp12

Edited October 12, 2022 by comment

[Raymack]

Thanks for the .fp12 version of Magic Key!

Thanks for the file suggesting related records, but I don't see the relationship!  Thanks

[comment]

26 minutes ago, Raymack said:

I don't see the relationship!

A checkbox represents a one-to-many relationship*, implemented without a related table. If you take the scenario of Volunteers and Tasks, then one volunteer has many tasks. If you use a checkbox field, then each task is a checked value. If you use a proper relational structure, then each task will be a related record in the Tasks table, with the relationship matching on VolunteerID.

If you take a closer look at the demo, you will see that it uses exactly this type of relationship (beside the relationship required for the "magic key" trick). The problem is that it does so in addition to the original checkbox, rather than instead of.

--
(*) To be precise, the relationship is actually many-to-many - since the same task can be assigned to more than one volunteer. But that's not essential to my point.

 

Edited October 12, 2022 by comment

[Raymack]

Hmm!  I'm lost....!  I will study the demo file more... 

QUESTION: Can I create a report like this:

VOLUNTEER 1, and under it their tasks, 

VOLUNTEER 2, and under it their tasks

I tried to create such a report without success...

 

[comment]

14 minutes ago, Raymack said:

QUESTION: Can I create a report like this:

VOLUNTEER 1, and under it their tasks, 

VOLUNTEER 2, and under it their tasks

Do you mean with the tasks being stored in a checkbox field? Yes, you can do that - sort of. You need to make the body part of the report layout tall enough to accommodate the maximum expected number of tasks a single person can have. Then place the Tasks field under the Name field and stretch it all the way down to the bottom edge of the body part. Then set it to slide up, also resizing enclosing part.

However, you won't be able do much in terms of formatting (e.g making it a bulleted list). And it might not look good at page breaks.

 

[Raymack]

Thanks, of course that's not what I want.  I was thinking with tasks stored in a related file I could create a subsummary report on NAME.  I'm exploring Magic Keys solution, but sure looks scary!  Thanks for your suggestions...

[comment]

22 minutes ago, Raymack said:

 I was thinking with tasks stored in a related file I could create a subsummary report on NAME

I afraid now you got me confused, because your title says "Reporting Subsummary results from a CheckBox Set". If the tasks are NOT stored in a  checkbox field, but as individual records in a related table, then of course creating such report is very easy: use a layout of the Tasks table, with a sub-summary part by VolunteerID, place the Name field from Volunteers in the sub-summary part and fields from Tasks in the body part and you'll be all set.

 

30 minutes ago, Raymack said:

 I'm exploring Magic Keys solution, but sure looks scary!

I think you are following a red herring. That demo is based on a situation where the data was initially entered into checkbox fields. Now they want to produce a proper report - so they are looking for a way to duplicate the same data as individual records in a related table. There are several ways to do that, and the "magic key" technique is probably not the best one. But the purpose of the demo is to demonstrate this technique, so that's what it uses. If you are entering the tasks as individual records in a Tasks table in the first place, then the entire exercise is irrelevant for you.

 

[Raymack]

Sorry for the confusion..... I began with checkbox data but soon discovered I could not get the reports I wanted.  Explored Magic Keys but it is pretty intimidating.  SO...I'm OK with giving up on Checkboxes if I can get the subsummary report I want.  The conversation has been helpful for me, and thanks!