3 Replies Latest reply on Dec 26, 2012 10:29 AM by philmodjunk

    How to report on multiple tables in a single report page


      How to report on multiple tables in a single report page


           I'm nearing the finish line (if there ever really is a finish line in software development).  But I have one big issue hanging out: reporting!

           My app structure looks like this:

      •           Dealership (table listing various car dealerships)           
        •                     >>>>Various Fields
        •                     >>>>Consult (multiple associated with a dealership) - This table is a report of a consultant visit to the dealership.  The consultant creates and agenda as part ofthe consult and makes several observations/recommendations.                     
          •                               >>>>>>>>Various Fields - date, location, etc...
          •                               >>>>>>>>Agenda item (table of agenda items associated with a consult)                               
            •                                         >>>>>>>>>>>>ObservationsAndRecommendation (table of obs and recs)                                         
              •                                                   >>>>>>>>>>>>>>>>Various fields (time, location, name, etc...)
              •                                                   >>>>>>>>>>>>>>>>ParticipatingEmployee (table showing which employees were involved in the observation)
              •                                                   >>>>>>>>>>>>>>>>ParticipaingGroups (table showing which groups were involved in the observation)
              •                                                   >>>>>>>>>>>>>>>>LibraryItems (table of help PDF documents) tthat can be associated with each observation

           I want to generate a report at the consult level.  In other words, on the consult layout, I have a button that will (eventually) launch a report on that specific consult and all it's subsidiary parts.

           I can generate a report on a single table.  What confuses me is how to generate the reports on the tables associated with the consult at each level.

           Any direction would be greatly appreciated.  Thanks!

        • 1. Re: How to report on multiple tables in a single report page

               When you have this structure to your relationships:

               Table1----<Table2-----<Table3    (----< means one to many)

               Then you can base your report layout on Table3, but include fields from tables 1 and 2 on this same layout. Often, fields from Table 1 would be placed in the header, footer or a grand summary part. Fields from table 2 often get used in sub summary layout parts to serve as sub headers for different groups of records from table3.

               Thus, where possible, base your layout on the table occurrence that is the farthest down this chain of one to many relationships that you can. But this will not work perfectly when you bet a "branch" in your relationships and need to include multiple records from two or more different "child" tables:

                             Table 4

               Then, you may need to use a portal to list multiple related records from Table 4 on a layout based on Table 3 and this can create issues when  you try to format your report to look and function the way you want it to.

          • 2. Re: How to report on multiple tables in a single report page

                 That's exactly what I did.  And you're right, the Table 4 thing does create issues.  The problem is what happens when you have more data than you alot for the portal?  For now, it'll have to do.  At least it's good to know I'm on the right track.

                 I'm really surprised that FileMaker is this weak (just an opinion) in the reporting side.  I'm used to using more advanced reporting tools like Crystal Reports.

                 I guess you can't be good at everything.  Getting this project done in the amount of time I had couldn't have been done with any other solution I know of.  And, it wouldn't have got to where it's at without your assistance.  Thanks again for all your timely help.

            • 3. Re: How to report on multiple tables in a single report page

                   The best approach to using a portal like this, is to make the portal very large--so large that it has more rows than you ever anticipate needing. Then set the portal to 'slide up' and 'resize enclosing part'. This, when prinitng, previewing or saving as PDF, shrinks your portal down to just the number of rows used.

                   Key facts about sliding layout objects:

              1.           It's only visible in preview mode and when you print/save as PDF...
              3.           Sliding fields will shrink but not expand.
              5.           All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
              7.           Objects in headers and footers will not slide.
              9.           Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
              11.           Fields will slide up only if Top alignment is specified for it and will slide left only if Left alignment is specified.
              13.           Consistent side borders are difficult to achieve with sliding fields.

                   The alternative approach that can be used has the pottential to require very complex scripting and layout design work, but it is possible to import data from different tables into a "report table" that then allows you to have groups of records from two or more different related tables in the same table. Due to the differences in data types in each table and the reporting formats needed on your report layout--this can get really, really messy but can be made to work.

                   And I do agree that there is a lot of room for improvment here. FileMaker Inc has a feature request submission form if you care to post such suggestions: http://www.filemaker.com/company/contact/feature_request.html