1 Reply Latest reply on Oct 8, 2014 12:03 PM by philmodjunk

    Need advice on setting up a report

    user17152

      Title

      Need advice on setting up a report

      Post

      I'd like some advice on setting up a report.  I have three tables:

             
      1. TroubleTickets
      2.      
      3. PunchItems
      4.      
      5. PunchReceivables

       

      There is a one-to-many relationship between TroubleTickets and PunchItems and a one-to-many between PunchItems and PunchReceivables.  I want to generate the following report:

      Trouble Ticket #1

      Attn: John Smith

      Punch Item #1

      Punch Receivable #1

      Punch Receivable #2

      Punch Item #2

      Punch Receivable #1

      Punch Item #3

      Punch Item #4

      Punch Receivable #1

       

      Not all PunchItems will have related PunchReceivables, as illustrated in #3.  I can't get this to work.  I can get a list of my PunchItems on the TroubleTicket and the first PunchReceivable will show up, but not a list of PunchReceivables.  Is such a report not possible?  I've tried various combinations of sub-summary and changing the table reference for the layout between the three tables.  Nothing works.

      I'm pretty bad at FileMaker reports and would appreciate some guidance.  I've thought about using the List function in a calculated PunchItems field to generate a list of PunchReceivables.  But I wasn't sure if that was the best approach, so I thought I'd ask here first.

      Thank you!

       

        • 1. Re: Need advice on setting up a report
          philmodjunk

          If it wasn't for Punch Item #3 showing that you don't always have any related Punch Receivable records, I'd suggest a report layout based on PunchReceivables with the Punch Item fields located inside sub summary layout parts. That approach works quite well but only if you always have at least one Punch Receivable record for every punch item record.

          What you can do, is base your report on PunchItems and use a portal to list the PunchReceivables records. You can make the portal many rows in size and then set it to "slide up" and to "resize enclosing part" so that, when printed or previewed, the portals shrink to just the number of rows needed for the number of Punch Receivable records in the portal.

          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.