2 Replies Latest reply on Mar 31, 2014 2:44 PM by DavidLake

    Complex many-to-many reporting question



      Complex many-to-many reporting question


           Here's my table structure (names have been changed to protect the innocent :-)   and my report need:

           Grocery Stores ----<  Join  >----   Cookies -----< ingredients

           Cookies can be sold in multiple stores, stores can sell multiple kinds of cookies, each cookie has it's own list of ingredients.

           I have the easy reports finished.  Now I'd like one that summarizes in this order:

           One summary line for each Store in the Join Table

           One line for each cookie sold in each store

           (now the trick) And the detailed list of ingredients for each cookie sold in the store.


           The cookie list and the related ingredients needs to repeat for each store in which they are sold.




        • 1. Re: Complex many-to-many reporting question

               Base your layout on Join.

               Put fields from Store in a sub summary layout part when sorted by Store ID.

               Put fields from Cookies in the body and include a portal to Ingredients to list the ingredients.

               Assuming that you need to print/save as PDF from this layout, make the portal a great many rows tall and set the portal to "slide up", "resize enclosing part" so that it will shrink down to just the number of  rows needed to list ingredients for that cookie.

               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.
          15.           In FMP13, hidden objects (”Hide object when”) will slide/resize.
          • 2. Re: Complex many-to-many reporting question

                 Absolutely fantastic.  I was over thinking it..... thank you so much!