12 Replies Latest reply on Jan 17, 2012 1:33 PM by philmodjunk

    Grouping Records on a Layout

    MikeHaddon

      Title

      Grouping Records on a Layout

      Post

      I am trying to get Filemaker to Group records in a layout.  I first tried this by creating a layout as a report, but the leading grand summary part doesn't seem to be working correctly.  Here is what I am trying to display on my layout.

      This is an expansion on the Legislative Tracking application I am working on to track the Utah Legislature.  In this case, I am trying to develop a layout that shows what bills are up for consideration in different committees.  It would be helpful to have all of the committees listed in a single view - e.g. the user doesn't select the committee he wants to see the records for.

      I would like to create a layout that groups the records by committee.  So, for example:

      House Judiciary Committee

           HB100  Funding Amendments
           HB234  Department Adjustments

      House Ethics Committee

          HB020  Legislative Rules
          HB015  Sunset Review

      That is the basic way I would like it to layout.  So, basically, it groups the bills by committee.  What are the basics for making this type of record grouping possible?  Thanks so much for any advice. 

        • 1. Re: Grouping Records on a Layout
          philmodjunk

          Sub summary layout parts can do this, provided the records grouped below them all have a common value such that sorting on that value's field groups them in the order you want for your report.

          If the layout is based on "bills" and each record has a single field with the IDnumber of the committee currently considering the bill, you can add a sub summary layout part 'when sorted by" this committeeID field. Put your Committee name field in the sub summary part and sort your records by this field.

          • 2. Re: Grouping Records on a Layout
            MikeHaddon

            I thought I had it solved, but FileMaker seems to have a mind of its own.  I started from scratch this time.  I went to Manage -> Layouts and created a new layout.  I created it as a report, as that seems to allow me to create the sub summary via their version of a wizard.  I set everthing up the way I needed.  When the Layout/Report displayed, it was doing exactly what I wanted. 

            From there, I began to tweak things a little, just to display a bit more information than just the Committee and Bill number.  At each step, I saved and exited the layout, just to make sure it was still working.  I finished up the Layout, and it was exactly what I needed.

            Then, I went back to another layout to create a button that would take the user to this, just completed, layout.  I created the button on the other Layout and set it to Go To Layout - and targeted this new layout/report I had created.  Once finished, I exited and saved.  I then tried out the new button that would take me to the newly developed Layout.  Well, it did go to it, but it was back to the problem I was having before.  The Bills are listed, but they aren't grouped by Committee.

            I'm stumped because when I completed the work on the new layout, it was working great.  It was listing the bills and grouping them under the appropriate committee.  I don't understand how this could have changed simply by creating a button on another layout to take the user to this new Layout.  I even used the Layout dropdown at the top of Filemaker to see if, perhaps, I navigated to this new layout that way the problem would be resolved.  However, when I went this route, I got the same thing.  List of bills - no groups.  HELP!!!

            • 3. Re: Grouping Records on a Layout
              philmodjunk

              The records must be sorted in a sort order that groups your records in a sort order that works for you sub summary part(s). Your button to take you to this layout should (perhaps) perform a find and (definitely) perform a sort.

              Enter layout mode and double click the sub summary part to see what field is designated as its "sorted by" field. Then make sure you always sort the records in a sort order that includes this field or the sub summary part will be invisible.

              • 4. Re: Grouping Records on a Layout
                MikeHaddon

                That worked perfectly.  I am slowly learning....  One adjustment I would like to make to this layout, I think, involves changing the underlying recordset.  The way this is currently working the layout shows something like the following:

                House Judiciary  1/25/2012 8:00 AM Room W125

                     HB0013 Offender Registry Review
                     HB0010 Department of Corrections Amendments

                House Law Enforcement & Criminal Justice  1/25/2012 8:00 AM  Room W120

                     HB0012 Corrections Education Amendments

                There are four different agencies using this system to track bills.  It would be really nice if I could place some buttons at the top of this layout that would allow a specific group to only have bills they are tracking reported in this layout.  For example, I am only interesting in Corrections bills.  When I go to this layout, it would be very helpful to select a Corrections button and have this layout update to only reflect bills I am tracking.  The following is my current database structure/relationship:

                I have created a Join table as follows:

                iPadCommitteeBillsJoin (CommitteeID, BillID)

                The tables I am joining to are copies of other tables in the database, named the following:

                tblBillsForiPadCommitteeAgenda (BillID, UDC, Sentencing, CCJJ, Governor) - the UDC, Sentencing, CCJJ are Yes/No indicating if the group is tracking the bill
                iPadCommitteeAgendaBills (CommitteeID, Committee) - Committee is the full name of the committee

                Relationships are the following:

                iPadCommitteeBillsJoin::BillID = tblBillsForiPadCommitteeAgenda::BillID
                iPadCommitteeBillsJoin::CommitteeID = iPadCommitteeAgendaBills::CommitteeID

                Do I perform a Find, for example, of tbleBillsForiPadCommitteeAgenda::UDC="Yes"?  Somehow, I need the layout to update, via a script, to reflect only those bills being tracked by the selected group.  Also, I would need a "View All" button to allow the user to go back to square one and show all committee agendas and all bills.

                Does this make sense?

                • 5. Re: Grouping Records on a Layout
                  philmodjunk

                  Yes, you can script each button to perform a find. "Please note that the complications inherent to your separate fields for each tracking group" design is continuing to proliferate.

                  • 6. Re: Grouping Records on a Layout
                    philmodjunk

                    Each script must then sort the records back into the needed order after performing the find.

                    • 7. Re: Grouping Records on a Layout
                      MikeHaddon

                      The Find I need to perform is on a related table - not the table underlying the Layout.  Will this still work?  Again, I want to do the find, for example, on whether or not Corrections is tracking the bill.  That tracking information (Yes/No) is in the tblBillsForiPadCommitteeAgenda table.  I'm just not sure if the relationship established carries over into a Find scenario.

                      As always, thanks so much for your help and insight.

                      • 8. Re: Grouping Records on a Layout
                        philmodjunk

                        You can specify criteria in fields of a related table as long as you keep in mind what this "means" when you perform the find. With criteria entered into a related table you are telling FileMaker "Find all records in the layout's table that have at least one related record matching this criteria."

                        Thus, for many to one and one to one relationships, this works just as though the field from the related table is part of the layout's table. If, on the other hand, there is more than one matching record in the related table (one to many), your find may produce unexpected results.

                        • 9. Re: Grouping Records on a Layout
                          MikeHaddon

                          Here is the script I wrote:

                          Enter Find Mode []
                          Set Field [tblBillsForiPadCommitteeAgenda::UDC; tblBillsForiPadCommitteeAgenda::UDC="Yes"]
                          Set Error Capture [On]
                          Perform Find []
                          Sort Records [Restore; No dialog]

                          It appears to be clearing the Layout.  Once I run the script, there are no records found.  The table tblBillsForiPadCommitteeAgenda includes the BillID and whether or not it is being tracked by the groups.  It is joined to iPadCommitteeBillsJoin by BillID.  Functionally, this should be a one to one relationship, as a bill can only be considered by one committee (it won't be found in multiple committees).  Still, I am getting no records found.  Did I mess up on the Set Field statement?

                          • 10. Re: Grouping Records on a Layout
                            philmodjunk

                            Use:

                            Set Field [tblBillsForiPadCommitteeAgenda::UDC; "Yes"]

                            tblBillsForiPadCommitteeAgenda::UDC="Yes"

                            will put either zero (false) or 1 (true) into the specified field as search criteria.

                            • 11. Re: Grouping Records on a Layout
                              MikeHaddon

                              Worked brilliantly.  One last question.  I want to place a final button the user can press that takes them back to all records again (basically removing the filter).  I would guess that would be fairly easy.

                              By the way, I think I owe you lunch - or several : D  You have been incredibly helpful.

                              • 12. Re: Grouping Records on a Layout
                                philmodjunk

                                A script that uses show all records, then sorts (to bring back the sub summary parts) is all you need for that button.

                                By the way, I think I owe you lunch - or several

                                It's too bad this is the Utah legislature, not the California Legislature. Regulatory changes in CA are about to adversely affect my employer's business and his customers. A little "influence" with the regulators involved would be darn useful about now... Wink