5 Replies Latest reply on Apr 27, 2012 7:48 PM by ezz

    Report using multiple related records (some with null sub values)



      Report using multiple related records (some with null sub values)



      I am new to Filemaker although have vast experience with Access and relational database design / vba. I cannot seem to find a way to make a report in Filemaker show ALL the correct records although this is probably just due to lack of knowledge with Filemaker, so if anyone can help explain the solution to the below I would appreciate it.


      Databse Layout Example

      I have 4 tables related as follows:


      Table1 (top level 1)

      Table1-1 (related to table 1)

      Table1-1-1 (related to table 1-1)

      Table1-2 (related to table 1)


      Now i am trying to do a report showing ALL records regraldess of if Table1-1-1 has data for the single master reecord of of Table 1:


      Table1 (show single record)

      Table1-1 (show all records)

      Table1-1-1 (show all related records if any)

      Table1-2 (show all records)


      The layout I would like to achieve is:


      Table1 (header)

      Table1-1 (sub summary header - show all regardless if 1-1-1 has no data)

      Table1-1-1 (body - multiple records or none)

      Table1-2 (footer - multiple container records on a page at the end of the report)


      The prolem is:


      a) a sub summary report of 1-1-1 works to a point but if no records are valid for Table1-1 then it does not show these group headers at all. Understandable as its only showing records that are present and related from 1-1-1.


      b) a sub summary report on 1-1 works to point but only shows the first related record of 1-1-1 in any set of 1-1 instead of all of them. I thought i could put a portal in this section to solve it but that works to a point but only shows the fixed number of rows I set where there could be none or one or more records.


      c) I have not even tried to place all the photos onto the footer from 1-2 yet as I can not get past the first part. I am not sure if this will work so any tips here would also help to create photo pages at the end of the report.


      *In Access this is chaieved by modifying a query relationship join to (show all records from table a and only those from table b) or a right/left join (as opoosed to show records where both are equal) to force the records of 1-1 to ALL display regardless if 1-1-1 has a related record.



        • 1. Re: Report using multiple related records (some with null sub values)
          /files/7d9d6613cb/Filemaker.jpg 800x600
          • 2. Re: Report using multiple related records (some with null sub values)

            b) is probably your best bet for this report.

            On your layout make the portal to the defects table many rows taller than you expect to need, but then set the portal to "slide up" and "resized enclosing part". This will work as long as you have as many or more portal rows as you have defect records for a given part. The "slide/resize" settings will remove the unused portal rows and close up the unused space.

            An alternative--and yes it has its potential problems, is to create a blank, related defects record as an initial related record for every part record. Then you should be able to base your report layout on the defects table.

            For the pictures in the footer, you may want to use a trailing grand summary instead. If each picture is in its own related record, you can use one row portals to display them in the grid format you show in your example.

            • 3. Re: Report using multiple related records (some with null sub values)

              Thanks for your input... I have tried this now (portal with more rows than needed and a sliding layout) and it works with one problem.

              On the layout in preview mode it will show about a half page of records and then oddly shows a blank white section taking up the bottom half of the page (the blank white box goes from left edge to right edge overlapping all background objects or themes). This causes the page to break afterwards and the next records to continue onto the next page until it happens again taking up the bottom half or third of the page with nothing. It seems like a bug to me as it makes no sense at all why or where this black section is coming from.

              • 4. Re: Report using multiple related records (some with null sub values)

                What version of FileMaker are you using?

                Did you select both slide up and resize options?

                Are there other layout objects in the same layout part next to or below the portal that are not set to slide and resize?

                Also, make sure that the border of the portal does not touch or cross a layout part boundary.

                Key facts about sliding layout objects:

                1. It's only visible in preview mode and when you print/save as PDF...
                2. 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.
                3. Objects in headers and footers will not slide.
                4. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
                5. Fields will slide up only if Top, alignment is specified for it and will slide left only if left alignment is specified.
                6. Consistent side borders are difficult to achieve with sliding fields.
                • 5. Re: Report using multiple related records (some with null sub values)

                  Yes slide and resize are selected and the rules complied with. Browse data looks good. Rebuilt layout and blank box is fixed (must have been a theme element) but preview does wierd things like where 3 sub records exists it only shows 3 lines but only the second line has the field data showing with line 1 and 3,4,5 all blank. Odd, problematic, unexpected behaviour.


                  Anyway surely there is a simpler way to report on relational data in Filemaker without using portals?


                  A simple example using related tables of say Customers and Invoices - is it not simple to show ALL customers in a sub summary header and each related invoice below (even if any customer has no invoices there must be an easy way to still show this customer in the sub summary with a tally of 0), I mean the customer still exists and needs to show on the report even if they have no invoices yet... this is relational database reporting 101 so I am surely missing something so simple? or not in Filemaker?