3 Replies Latest reply on Jan 7, 2014 2:06 PM by philmodjunk

    Reports w/one to many relationship

    DennisTyner

      Title

      Reports w/one to many relationship

      Post

           Using FMPro13.

           Trying to create a report using three tables. Household (one) to Household Members (many) and Household (one) to Visits (many).  I would like to print Household (one) info followed by all Household Members (many) within the Household followed by all Visits (many) made to that Household.  So far I am only successful in printing Household followed by all Household Members.

        • 1. Re: Reports w/one to many relationship
          philmodjunk

               Visits>-----Household----<Members

               With that relationship, you will need to use at least one portal to list either Visits or Members. The portal will need to be set to be many rows taller than you ever expect to need and then you set it to "slide up" resize enclosing part to shrink it down to just the number of rows needed to display all the data.

               There are limitations to this approach that can be frustrating, but it's the simplest option to try first. Other options would require importing both Members and Visits data in to the same special "report table" so that you can set up a list view layout based on this report table--that can open a major can of worms but you can often produce results not possible with portals.

               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.
          14.      
          15.           In FMP13, hidden objects (”Hide object when”) will slide/resize.
          • 2. Re: Reports w/one to many relationship
            DennisTyner

                 Thanks for quick response.  I used to portals one for household members and one for visits.  Resizing not optimal but adequate for my needs.

            • 3. Re: Reports w/one to many relationship
              philmodjunk

                   You can reduce this to just one portal if you base your layout on either visits or household members. In such case, you need only one portal for the other of these two tables. That sometimes provides some better layout options as you'd use list view to display the data for one table and a portal for the other.