7 Replies Latest reply on Mar 15, 2013 12:50 PM by philmodjunk

    List relational data from multiple tables

    StephanToppinger

      Title

      List relational data from multiple tables

      Post

           HI!

           I started to use filemaker a couple of weeks ago and came across some problems:

           I would like to list (maybe in a list view) all the related data, so I could have a clean print/send layout. I dont want to use portals beacause it wont adept the changing quantity of data. Is it possible somehow?

           I attached my relationship graph to ask for any advice on my database design if it is correct.

           Any help appriciated

            

      Screen_shot_2013-03-15_at_12.45.59.png

        • 1. Re: List relational data from multiple tables
          philmodjunk

               To a limited degree, a portal can adapt to "changing quantities of data". You can make the portal many rows tall and set it to "slide up" , "resize enclosing part" and when printed/previewed/saved as PDF, the portal will shrink to the number of rows needed to display the related rows of data.

               But given the fact that portals can't expand and that fields inside the portal row can't be set to "slide", it's better to avoid using a portal for a printed report when it is possible to do so.

               Often, you can base a report layout on the portal's table and include fields from the parent table rather than the reverse. A list view layout can then expand or contract as needed to display your data in a report format. The key added detail is that you will need to perform a find or use Go To Related Records to pull up the correct group of records on your layout.

               The Invoice starter solutions that come with FileMaker 11 and 12 use this method for printing invoices.

               But I can't tell from your first post, WHICH data from WHICH tables needs to be included in your report, so I can't tell you if that will work for your specific report or not.

               I can tell that certain reports could be done in this manner if based on one of these Tutorial: What are Table Occurrences?: Event_Packages, Event_Services or Event_Other. If you trace out the relationships to most of the other tables, you'll find that they have the necessary "many to one" relationships required for this reporting method.

               Key facts about sliding layout objects:

                 
          1.           It's only visible in preview mode and when you print/save as PDF...
          2.      
          3.           Sliding fields will shrink but not expand.
          4.      
          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.
          6.      
          7.           Objects in headers and footers will not slide.
          8.      
          9.           Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
          10.      
          11.           Fields will slide up only if Top alignment is specified for it and will slide left only if Left alignment is specified.
          12.      
          13.           Consistent side borders are difficult to achieve with sliding fields.
          • 2. Re: List relational data from multiple tables
            StephanToppinger

                 Hi! Thank you very much for the detailed answer. 

                 Actually I would like to do the same like in Invoice starter solution.

                 But:

                 I would liket to make a layout for printing for an event. An event contains the client who is paying for it and the hotel where he is staying. Every client can order multiple packeges, services, and other thing for his/her event. I have set up those joined tables already. 

                 My problem is that how do I set up a list layout that can list packeges, services and other things in three different sections (or at least below each other) in the same way as in Invoices starter solution.

                 The goal is to print a quote or a detailed invoice.

                 If this is not possible, how could I solve the problems. I could use portals but I dont really like the idea of that.

                  

                 Thank you

                  

                 Oh and the summary table on the graph is not needed it was just for some experimenting

            • 3. Re: List relational data from multiple tables
              philmodjunk

                   You'll note that I said "certain reports could be done in this manner".

                   If your report were limited to this information:

                   Data from Clieint table.
                   Data from related Hotel Record
                   Data from Event Services
                   Data From Services

                   The method I am describing will work just fine.

                   But you also want data from Other, Event Other, Packages and Event Packages

                   That creates a situation where the options are not nearly so simple nor elegant.

                   From simple to complex, here are some options you can consider:

                     
              1.           Create 3 reports, each one from layouts based on a different join table.
              2.      
              3.           Create the 3 reports from 1, but use Save As PDF to merge them into a single PDF document.
              4.      
              5.           Base your report on one of the three join tables, but then use portals with sliding enabled to show the data from the other two join tables and linked in tables from the other side of the Join.
              6.      
              7.           Devise a "Report Summary" table with sufficient fields to accept data from each or most of the tables involved. Use a script with Import Records to copy the needed data from your current tables. Use conditional formatting, calculation fields, layered fields, etc to produce a report layout based on this table. (Note, you usually do not have to import data from every table, just the Join table data. Additional occurrences of your other tables can be linked to this report table to supply the rest of what you need.)
              • 4. Re: List relational data from multiple tables
                StephanToppinger

                     "That creates a situation where the options are not nearly so simple nor elegant." 

                Is it because my database design is bad or the given situation is very complex?

                • 5. Re: List relational data from multiple tables
                  philmodjunk

                       Your design looks fine to me from what limited understanding that I have of your database.

                       Options 1,2 and 3 might not really be all that bad depending on the exact requirements you need to meet for your report.

                  • 6. Re: List relational data from multiple tables
                    StephanToppinger

                         I dont really understnd why should I base my report on the joined tables why not on the event tables with three portals that shows the related records

                    • 7. Re: List relational data from multiple tables
                      philmodjunk

                           That's a possibility you can consider.

                           Basing the report on one of the event tables eliminates the need for one of the three portals. You may find tha sliding potals work well for 2, but not 3 of your portals. If that's the case, base your report on the table of the portal that doesn't work well as a sliding portal.