5 Replies Latest reply on Apr 10, 2013 9:28 PM by JG

    Reporting on checkbox values

    JG

      Title

      Reporting on checkbox values

      Post

           I've been using a checkbox set for a field to make it easy for users to keep the data consistent in a situation where any combination of items is allowed. For example:

           Event Table --> Module Field. Module Field has checkboxes for user input, and the checkbox list provides: Module A, Module B, Module C, etc up to Module F as options. User must tick at least one, and up to 6 (all) of the options.

           However, I now want to create a report that counts how many times each module has been run over a month/year. Since the checkbox turns the input into a text list, I'm getting results that don't let me pull that information out cleanly. For example:

           Event 1: Module A
           Event 2: Module B
           Event 3: Module A, Module B

           I want a report that tells me Module A has been run twice and Module B has been run twice. However, Filemaker treats this as Module A once, Module B once and Module A, Module B once. Which is correct as far as the checkboxes go, but not actually what I'm looking for. Especially since any combination of the 6 modules is allowed and expected.

           Is there some way I can separate the info in Event 3 easily in a report layout? Or is there a better option than using checkboxes for this kind of data?

        • 1. Re: Reporting on checkbox values
          philmodjunk

               You are far from the first to encounter this little puzzle. A single field with a checkbox group is easy to set up, but a report enumerating the number of times each value is selected is anything but simple.

               My suggestion is to get rid of the single field. Replace it with a related table where you select each option by creating a separate related record with one of the values from your value list. This is most easily set up with drop down lists/pop up menus in the portal row, but it's possible to use buttons and conditional formatting (or a calculation field) to produce what still looks and functions like your check box group but in reality is creating/deleting records in the related table.

               With that related table, you can then base your summary report on the related table to enumerate how many times each value was selected.

          • 2. Re: Reporting on checkbox values
            JG

                 Oh I see - so I can have a "modules used" sort of table to track which module and link it to an event ID, and have as many rows in that table for an event as are needed. So to continue my little example,

                 Modules Table (instead of Events table)

                 Event ID 1 - Module A
                 Event ID 2 - Module B
                 Event ID 3 - Module A
                 Event ID 3 - Module B
                  

                 where the event id is a key to connect the tables. And then the reporting can happen as it does for any other related table.

                 Thanks for this - I never would have guessed that adding another table would actually make things simpler and not more complex!

            • 3. Re: Reporting on checkbox values
              JG

                   I'm giving this a test run and can't seem to get a new record added via a portal? Portal is on Event record, looking in on records from the Modules table, filtered by relevant event id. I can edit the Modules records, but not add new ones. I have a feeling I'm missing something obvious here!

                   Another option might be to leave the checkboxes, but add a script that add/removes records from Modules on check/uncheck events.

              • 4. Re: Reporting on checkbox values
                symbister

                     Have you allowed 'add new records via this relationship' when setting up the relationship?

                • 5. Re: Reporting on checkbox values
                  JG

                       Nope.

                       ::facepalm::

                       Fixed now :)