2 Replies Latest reply on Dec 14, 2010 4:59 PM by SteveWehba

    Subsections and repeating sections in a report



      Subsections and repeating sections in a report


      Without getting into a lot of details, I'm trying to create a stock trading database, and I'm a bit stuck on how to design printed reports. Let's start with a really simple database schema:

      • Trade <--(1..1)-----(1..n)--> Unit
      • Trade <--(1..1)-----(0..n)--> Log Entry

      In other words,

      • Every Trade is associated with one or more Units.
      • Every Unit is associated with one and only one Trade.
      • Every Trade is associated with one or more Log Entries.
      • Every Log Entry is associated with one and only one Trade.

      Let's say that every Trade has three fields: T1, T2, and T3.  Every Unit has three fields: U1, U2, and U3.  And every Log Entry as three fields: LE1, LE2, and LE3.  I'd like to create a report/layout that generates the following output for each Trade included in the report:

      T1, T2, T3


      Unit 1: U1, U2, U3

      Unit 2: U1, U2, U3


      Unit n: U1, U2, U3

      Log Entries

      Log Entry 1: L1, L2, L3

      Log Entry 2: L1, L2, L3


      Log Entry n: L1, L2, L3

      In other words, my report/layout would have two "subreports" -- Units and Log Entries --- each of which repeat data for all the associated records.

      This is a pretty common need in most database applications, but I just don't see how it's supported in FileMaker.

      I assume a solution would involve sub-summary parts of reports/layouts, but I don't see how it's possible to define two sub-summaries at the same level.

      Any help would be appreciated.

        • 1. Re: Subsections and repeating sections in a report

          Yep, you've put your finger on the key issue. Assuming that Trades, Units and Log Entries are separate tables, this report is difficult to create in FileMaker.

          If the report had just Units or Just Log Entries, you could base the report on that table and pull data from Trades to insert into headers or Sub Summaries. If the tables had this structure: Trades---<Table1 ---< Table 2, you could base the report on Table 2 and pull data as needed from Table 1 and Trades into those same layout parts, but you appear to have Units>---Trades---<Log Entries and that's a problem here.

          To do this in FileMaker you have two options--both are problematic here:

          Base the report on Trades and place two portals in the body, one to Units and one to Log Entries. Make these portals many rows tall and set them to "slide up, also resize enclosing part". Since these portals can be set to shrink but not grow there's always the potential problem that you may not have made a portal tall enough--plus portals that cross a page break don't always break "cleanly" in FileMaker 11.

          Define a merged "report" table that combines the needed fields for both Units and Log Entries and import data into this table each time you need the report. You then have to either create calculation fields that selectively display values depending on whether a given record is a Unit or Log record with other fields or conditionally formatted text that selectively display the appropriate field labels. Often, you end up layering fields and conditionally formatted layout text on top of each other. (Such layout text must use a drastic text size change to "disappear" for fields where they aren't needed.)

          • 2. Re: Subsections and repeating sections in a report

            Thanks for the quick response and for the suggestions. I had been using the portal approach, but I thought there might be something better.

            I have to admit, I'm a bit surprised that something so basic is so difficult to do in FileMaker. Two follow-up questions: (1) Are there any third-party add-ons that add reporting features like I need? (2) Is the FileMaker team planning on beefing up this (IMHO tremendously glaring lack of) functionality?

            While I'm at it, I'd like to throw out a few more suggestions having come at FileMaker from a pretty long career of using other database tools: (1) Enable/disable, show/hide fields and other objects on layouts. (2) Object orientation. In other words, layouts=objects, fields=objects, etc. Every object has properties and methods. (3) "Unbound" fields/controls on forms. Something like your "merge fields" but they are calculated fields that are not necessarily bound to any particular field in a table.

            I can't say I entirely dislike using FileMaker, but I'm coming to understand that to master it you have to keep track of a long list of "arcane" features and work-arounds. I just wish that needed features were more directly implemented in the product. Suggestion #1, above, is a perfect example.

            Thanks again for your help.