3 Replies Latest reply on Sep 21, 2013 8:59 PM by SBC

    Report creation confusion... FMP 11 Advanced

    SBC

      Title

      Report creation confusion... FMP 11 Advanced

      Post

           Hi, I'm new to FileMaker and I'm confused on how to create a simple report that is going to pull part of its data from two related tables.  They are 'line item' tables that are used as Portals in the main table named "Incidents". (see graphic)

           I understand hypothetically that if I only had one line item table I would simply make my report in the one line item table and add my related fields from the Main "Incidents" table.  But where to I create the report if I need data from both of the Line Item tables?

           Thanks in advance!  laugh

      Screen_Shot_2013-09.png

        • 1. Re: Report creation confusion... FMP 11 Advanced
          FentonJones

                

                

               The obvious answer would to do the report in Incidents, and show whatever values you need from the other two tables, since Incidents has a good relationship to each. 

               But you've said that you have thought of this. So we (me anyway) kind of wonder that is this report which has a problem with this. Perhaps it just doesn't look like a report, since a lot of records would likely have the same value in the field.

               The tool to put a layout in "group" order is the Sub-summary part, "Sub-summary when sorted by" (then choose which field to use); you also set the sort in a script. The sort should be using the same order as the sub-summaries on the layout, and should have a "leading" then "trailing" (automatic) if you want to show the same sub-summary part twice on the layout (for totals, etc.). 

               It is optional whether to include the Body part. If so, it would be in between. If not, you might want calculation fields, to put together numbers (or text) for the sub-summaries. 

               That's the general idea. Let us know a bit more about the report, and someone will tell you more (and/or better :-).

          • 2. Re: Report creation confusion... FMP 11 Advanced
            philmodjunk

                 Option 1:

                 Set up your report layout based on Incidents and include two very tall portals to each of the related tables. Use sliding and visibility settings in the inspector to set them to "slide up" and "resize enclosing part". these portals will then shrink to just the number of rows needed when you print, preview or save as PDF from this layout. The portals can't expand so you need to start with very tall portals when you design the layout.

                 There are limitations to this type of report so it may or may not work for you, but it's the simplest approach to try as your first approach.

                 Option 2:

                 Look at the data in your two related tables if the data from one or the other does not work well when presented in the report from a portal set to slide, base your report layout on that table, include your fields from Incidents on the layout and then add a portal to the third table sized to slide and resize as described in option 1.

                 Option 3: (Don't go here unless all else fails, this one's very messy and takes a lot of effort to get it to work.)

                 Set up a 4th table as a temporary report table and define sufficient fields to hold data from either of the two related tables in your current setup. Link it in a relationship to Incidents. When you need a report, a script finds and imports the needed records from both related tables into this combined temporary table. Design your report layout to be based on this new table. To get the report to look right, you often have to "layer" different fields that only hold data for records from one of the two original tables on top of each other and use conditional formatting or calculation fields that function has field labels for a record from one table and which are invisible for  record from the other...

                 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.
            • 3. Re: Report creation confusion... FMP 11 Advanced
              SBC

                   Thanks so much!  

                   You both solved my problem and gave me some great info on using the those features.  I'm finding FileMaker to be a great product along with a great  community of users!

                   Best wishes!