4 Replies Latest reply on Jun 8, 2014 10:44 AM by willem67

    report on related records of more than one related table

    willem67

      Title

      report on related records of more than one related table

      Post

           Maybe I am now asking the impossible, but I need to double check.

           My database has three tables:

             
      1.           paintings
      2.      
      3.           exhibitions
      4.      
      5.           documentations

           With many-to-many relations records are linked.

           For reports showing related records I use layouts of lists with details of record X in the header and details of related records in the main part. That works very good. E.g. painting X and all related exhibitions. Of report I make a PDF.

           But, I would like to make one report of painting x and all related exhibitions and all related documentation. Then I can make one PDF instead of making two separate PDF's.

           Any solutions I overlooked?

        • 1. Re: report on related records of more than one related table
          philmodjunk

               Please describe the relationships in more detail so that we can see which tables are linked to which. I can guess this much:

               Paintings---<join table>----Exhibitions

               as that would be a many to many relationship between paintings and exhibitions. But is documentations linked to paintings or exhibitions?

          • 2. Re: report on related records of more than one related table
            willem67

            Paintings---<join table>----Exhibitions

            Paintings---<join table>----Documentation

            Exhibitions---<join table>----Documentation

            Yes: all table are linked with a many-to-many relation.

            Instead of making separate reports of linked records I would like to combine reports. E.g one report of painting x showing related exhibitions and related documentation.

                 [the problem: there is only one main part to show related records when using the list layout]

                  

            • 3. Re: report on related records of more than one related table
              philmodjunk

                   You have Documentation linked to both Paintings and Exhibitions, these are two occurrences of the same data source table?

                   Please outline what you need to see in your report.

                   When you have this relationship:

                   Table1----<table2----<Table3

                   The method you describe works well as you can base your layout on table3 and still include fields from tables 1 and 2. But when the relationships do not fit this nice, linear one to many structure, that method cannot be used as you have discovered.

                   There are several possible options to consider

                   1) using scripting, global match fields and different occurrences of these tables, it may be possible to temporarily set up the needed one to many chain for a specific base record such as a specific painting. Scripts would load the needed global fields to establish the needed links for this specific record.

                   2) Sometimes the only practical option is to use portals to display the related data on your report. Portals have significant limitations in how they work in a report, but sometimes they are the only practical option. Note that by making a portal many rows taller than you expect to need, but setting it to "slide up" and "resize enclosing part", the portal will shrink down to just the number of rows needed for the number of records shown in the portal.

                   3) With FileMaker 12, you also have the option of using calculation fields with ExecuteSQL to pull data into the report from other tables.

                   4) scripts can either print your different reports in sequence (with page numbers that start on page 1 of your first layout and that run through the last page of your second layout) or that can use Save As PDF to merge a PDF from the first layout with a PDF from the second. This approach requires that the second part of your report start at the top of a new page--which isn't always acceptable.

                   5) The "worst case" scenario that sometimes cannot be avoided is to set up a "report table" where your script finds records in different tables and imports them all into a common table just for report purposes. This can require both very large numbers of defined fields in the table and very complex layout designs but can result in a report not possible for your data model via other methods found in FileMaker.

              • 4. Re: report on related records of more than one related table
                willem67

                     thank you for your detailed answer. now going to research the options you mentioned!

                     to be continued!