8 Replies Latest reply on May 14, 2015 8:16 AM by lijnbach

    Problems with grouping in a report based on checkbox field

    lijnbach

      Title

      Problems with grouping in a report based on checkbox field

      Post

      Hello everybody,

       

      I have problems with “grouping” in a report. The grouping is based on a checkbox field.

       

      I have a set of records with learning materials. (About 500 records). The records have a field named “Grade”, and this is a checkbox field. The reason a checkbox field is used, is because sometimes a learning material is used for more then one grade.

       

      Some of the checkbox fields have e.g. the value:

       

      Grade 2

      Grade 3

       

      or

       

      Grade 6

      Grade 7

      General

       

      Everything is working fine, selections and sorting is all working. Except I can’t get the grouping in reports working properly.

       

      If I want a report grouped on “Grades” it should result in far more then 500 records. If I group the records by grade, some records should show up more then one time. In the first example a records should show up in the group “Grade 1”, but should also show up in the group “Grade 2”.

       

      I know I can use a (slider) portal in a report to show the related records, but some Grades have over 50 related records, others only 10 or 15 and some none.

       

      I also know I can solve it by using an extra table to connect the Grades and the learning materials instead of a checkbox field. But there are also over 1000 supporting learning materials related to the learning materials. This extra connecting table to connect the grades slows down the database dramatically.

       

      Using FMA 13

       

      Anyone a good idea?

       

      Thanks in advance,

       

      Hans Lijnbach

        • 1. Re: Problems with grouping in a report based on checkbox field
          philmodjunk

          A slider and a portal are two different things on a FileMaker layout.

          Am I correct that you have two tables linked in a relationship here or do you just have one table?

          Is this the report format that you want?

          Grade 1
              Material 1
              Material 2
              ....
          Grade 2
               Material 1 --> this item has both Grade 1 and Grade 2 check boxes selected
               Material 3
               ....

          and so forth

          • 2. Re: Problems with grouping in a report based on checkbox field
            lijnbach

            O.K. Phil,

            The report format you show is correct. But it is just one tabel. (I have several tables connected, also a table with grades, but this table shows only one record, only the first one of the checkbox field).

            And I use a table with grades with the X connection. (Because I use your advanced ValueLists).

            Hans Lijnbach 

            • 3. Re: Problems with grouping in a report based on checkbox field
              philmodjunk

              Then you have a problem as a report can't list the same record from the layout's underlying table more than once.

              It sounds like you really need to set up a many to many relationship with a join table as you have many materials that can be linked to the same grade and a given material can be linked to many grades:

              Materials-----<Material_Grade>-----Grades

              Materials::__pkMaterialID = Material_Grade::_fkMaterialID
              Grades::__pkGradeID = Material_Grade::_fkGradeID

              You can place a portal to Material_Grade on the Materials layout to list and select  Grades records for each given Materials record. Fields from Grades can be included in the Portal to show additional info about each selected Grades record and the _fkGradeID field can be set up with a value list for selecting Grades records by their ID field. This can even be set up with a control that looks and functions like your current check boxes even though it's really a portal with buttons.

              But now the report you want is easily set up on a layout based on Material_Grade where you can sort records to group them by grade and fields from the Material table can be included in the layout body to list the same material in each and every grade for which it was selected.

              • 4. Re: Problems with grouping in a report based on checkbox field
                lijnbach

                Thanks a lot Phil,

                I have to think this over. I had a join table for a many to many relation, but I removed it because it slowed down the database because also the "supporting learning materials" are connected. And not only the grade is connected, but also the location, Learning material, material kind and other things, and the same for supporting learning material. (They don't have the same location, neither the same material).

                Perhaps I should go back to the joined table and find a way to speed up the database another way.

                Thanks again for your effort and time Phil.

                Hans Lijnbach

                • 5. Re: Problems with grouping in a report based on checkbox field
                  philmodjunk

                  The presence of a join table itself shouldn't be the source of the slow down. But how you implement it on your layout may be something to look at carefully.

                  • 6. Re: Problems with grouping in a report based on checkbox field
                    lijnbach

                    Hai Phil,

                    You are right. I already learned that using a lot of conditions (e.g. hiding or not hiding) and a lot of conditional formats slows down the system. The problem I am facing here is that each subject (grammar, mathematics etc) has its own color. I followed your advice from a few months ago and removed the conditional formats and put the colors in a table. I also use other table occurrences for each LayOut, so I don't have te much related tables at the same time. That helped a lot. But I Still have to load the color for each Learning material, and also in the portal with supporting learning materials.

                    And my LayOuts are as simple as possible. Not to much information on a LayOut, and another LayOut for each function. The whole system became a lot faster without the join table. So I have to work on this further when using the join table again.

                    Hans Lijnbach

                     

                    • 7. Re: Problems with grouping in a report based on checkbox field
                      philmodjunk

                      Which doesn't explain why using a join table results in slower performance. How you implement that table is what I am referring to.

                      • 8. Re: Problems with grouping in a report based on checkbox field
                        lijnbach

                        O.K. Phil,

                        I will work on this one. I understand what you wrote and I will have to figure out what causes the slow performance.

                        Thanks for your advice Phil.

                        Hans Lijnbach