12 Replies Latest reply on Feb 17, 2009 9:46 PM by filler

    Summarizing repeating fields

    filler

      Title

      Summarizing repeating fields

      Post

      I know there are inherent problems with summarizing repeating fields, but thought I'd ask anyway.  Table 1 is names of people.  Table 2 is meetings and includes a repeating field containing several of the names from table 1.  Is there any way to create a preview report that summarizes the names of people (from table 1) with subsummary lists of all the meetings that person had?  The fundamental problem, it seems, is that repeating fields are subsummarized (and I guess summarized) by first repeated field only.  The rest of the records with matching person name in lower repeated field don't show up.  Therefore, right now I can only show what meetings a person attended if he is the first name in a repeating field.

       

      A more difficult addendum to this question: I'd ideally like to define the layout ("show records from") under Table 2 so that I don't have to put the meeting info in a portal (since portal contents can't slide up). 

        • 1. Re: Summarizing repeating fields
          comment_1
            

          It's sort of possible, if you define a calculation field in Meetings that concatenates all the details of a meeting, and another calculation field in the People table =

           

          List ( cMeetingDetails )

           

          However, this is merely a patch on the real problem: you should have a third table of Participants between People and Meetings.

          • 2. Re: Summarizing repeating fields
            filler
               Could you elaborate on both?  I'm not sure how the third table would be used since participants in Meetings is based on People.  A third table of Participants seems redundant.  And I thought of concatenating the repetitions of participants in Meetings, but there's no way to use that concatenated field to create a relationship with People, since a relationship is based on exact matches and not included text.  Thanks.
            • 3. Re: Summarizing repeating fields
              comment_1
                

              The relationship between People and Meetings is many-to-many (a person can attend many meetings, a meeting can have many people attending). The proper implementation of a many-to-many relationship (in any relational database, not just Filemaker) is through a "join table".

              In the current context, the Participants join table would have a record for each participation of a person in a meeting. The People table would have fields that describe the person only. Similarly, the Meetings table would describe details of the meeting only (date, place, etc). The Participants table would have (at mimimum) a PersonID field and a MeetingID field.

               

              See a basic demo here

              • 4. Re: Summarizing repeating fields
                filler
                  

                OK - I think understand what you mean on a technical level.  I guess my only question is where is the relationship formed visually?  I want users to be able to add a new Meeting and establish multiple Staff and Leaders for each Meeting record.  But if the nexus is created in a new table Participation (which operates behind the scenes), how can the user establish the relevant multiple connections within the Meeting page?  Hope that makes sense.  Thanks.

                • 5. Re: Summarizing repeating fields
                  comment_1
                    

                  filler wrote:

                  Hope that makes sense.


                  I'm afraid it doesn't (to me, at least). The only thing that seems to be more or less clear is that you need a Role field in the Participants table. That table, BTW, is very much visible in the portals from the two "main" tables, and in the report you want to produce.


                  • 6. Re: Summarizing repeating fields
                    filler
                      

                    So I would enable users to add participants (staff and leaders) in the Participation table through a portal in each Meeting record?  Can you direct me to an example of that?

                    • 7. Re: Summarizing repeating fields
                      comment_1
                        

                      In the demo file I have linked to earlier:

                       

                      In Contacts, click the OrganizationID field in the first empty portal row, in order to add a new affiliation to the current contact. In Organizations, click the ContactID field in the first empty portal row, in order to affiliate the current organization with another contact.

                      • 8. Re: Summarizing repeating fields
                        filler
                          

                        Beauty. You have just corrected a decade of bad databasing.  Thank you.  One final (I think) issue:

                         

                        While my report can now show all the meetings individual Staff attended, I would like to show all the Leaders within each Meeting the Staff member attended.  In other words, Staff 1 and Leader 1 & 2 (all added through a portal on the Meeting page to the Connections table) attended Meeting 1.  In my Staff report, I want to show Staff 1 attended Meeting 1 and in that meeting was Leader 1 & 2.  Right now, I seem to only be able to do that by using a portal: Subsummary by Staff, then in the body the Meeting # and portal (using a self-referencing relationship) to all Leaders in that meeting.  And, of course, you can't slide up portal contents, which muddies up the report.  Solutions?  Thanks again.

                        • 9. Re: Summarizing repeating fields
                          comment_1
                             Portals do slide (i.e. the number of rows shown is reduced to the count of related records).

                          Alternatively, you could aggregate the related Staff records by using the List() function, then place the calculation field from Meetings in the sub-summary by Meeeting part.
                          • 10. Re: Summarizing repeating fields
                            filler
                               Right.  I was referring to making room within the portal row and then accounting for the potential number of rows.  Very inefficient.  Unfortunately, I'm stuck with FM8, so I don't believe I have access to the List function.
                            • 11. Re: Summarizing repeating fields
                              comment_1
                                 Since you are only speaking of up to three roles, perhaps you could concatenate three conditional  GetNthRecord() statements in a calculation field. But I am not really getting the issue with sliding here.
                              • 12. Re: Summarizing repeating fields
                                filler
                                  

                                Well, to clarify the sliding issue - I know I can set the number of portal rows (indefinitely?) and then slide up the unused ones.  That's not a problem beyond having a very large gray portal area in the layout.  But within each portal row itself, I have to determine the exact amount of space I want in the field(s) because the fields themselves don't shrink (slide up) as they would outside a portal.  That pretty much relegates me to having too little or too much space between records in the report due to the non-shrinking portal field.