4 Replies Latest reply on Jul 9, 2012 12:35 PM by VeronicaDaigle

    I have a working database, now I need to generate printable reports :)



      I have a working database, now I need to generate printable reports :)


      OK, so I have just finished all the data entry layouts for a fairly complex project database (YAY!), and now I need to figure out how to generate meaningful and organized printable reports. 

      The most important of these is the daily project report, which is based on a data entry layout I have already created, however there are several portals on this layout that show subrecords for each daily report. For example, the user would enter a report each day (and each date must be unique), and part of that report he would generate a list of tasks completed each day on a separate but related table. The tasks are related to the daily report by their date and the portal shows the work location, work task, and a description of the work completed for each task that day. There are also similar portals for fuel deliveries, tracking soil use and tracking water use. The portals only show the most important information from these subrecords, and the description data may be bigger than the field itself. Now I am generating a second layout which should be very similar to the layout used for data entry, except I want to show the full data that is in the portals when it is printed. I don't think I can do this using the portals themselves since from what I have read on the forums, portals can't be told to expand based on the contents in their fields or the number of rows in the portal. Is there another way to show related data from multiple tables in a way that will print nicely or that is able to be exported to a pdf to be sent to clients? 

      Generating the reports is a totally new aspect of Filemaker for me so sorry if this is a dumb question :)

        • 1. Re: I have a working database, now I need to generate printable reports :)


          FileMaker Pro printing secrets
          by William Porter, Macworld.com   Feb 1, 2011

          There's no practical limit on the number of related records, but there is a limit on the number of rows that a portal can display without scrolling. For example, if you use the portal to display notes that are related to a personal record in a contacts database, you might define the portal to show 10 rows. If the notes are all brief, and you never have more than 10, then you could, in theory print the portal. But notes are unpredictable lengths, so an individual note might contain more text than can be displayed in the portal row. Moreover, you don't always know in advance how many notes will be created for any given person, or how many line items an invoice will have.

          The way to print the records displayed in a portal is to build a layout based on the child table that “owns” the records in the portal, and do your printing there. In other words, if you are printing an invoice, don't print from a layout based on the INVOICES table, with the line items in the portal; instead, jump to a layout based on the child table, LINE ITEMS, and print from there. By doing so, your invoice can go on for as many pages as it needs to show all the line items, because in a layout based on line items, every line item is a separate record.



          Sub Summary Reports in FileMaker Pro

          • 2. Re: I have a working database, now I need to generate printable reports :)

            As David Anders has posted, there are a number of options you can explore for getting printable reports.

            Basing the report layout on the portal's table is a key report method for FileMaker, but it doesn't work for all possible situations where you might use portals for data entry. If you have more than one portal and your report needs to list data from both, you may need to set up a report that has one or more portals on your layout. In those situations, you have to make the portals very large, with more rows than you expect to ever need and then set the portal to "slide up" and "resize enclosing part". This shrinks the portal down during print, preview or save as PDF to remove unsused rows 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. 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.
            3. Objects in headers and footers will not slide.
            4. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
            5. Fields will slide up only if Top, alignment is specified for it and will slide left only if left alignment is specified.
            6. Consistent side borders are difficult to achieve with sliding fields.
            • 3. Re: I have a working database, now I need to generate printable reports :)

              I think there might be another method that isn't mentioned here.

              That is importing the data from your portals in a temporary table. Used only for ptinting a report or creating a pdf to be attached to an e-mail.

              In my database I have the following structure:

              I have an Invoice. And every invoice can contain up to 7 different types of items. They are in 7 different portals and are 7 different "Line Items" tables.

              - Vehicles
              - Rental
              - Sales
              - Misc.
              - Wages
              - Kilometers
              - CellPhoneBills

              What I do when it's time to create an invoice is I have a script that goes in every "LineItems" table, finds the line items related to this particular InvoiceId and I import them into a temporary table (actually I use three temp tables, one for a front (summary) page. And I have two types of "Detail" pages.

              So I have three temp tables and three different layouts. I then create a PDF from the first layout and save it to a location created by a variable. I then create a pdf from the second layout and append that to the pdf. And then from the third layout.

              That way I end up with a nice looking PDF with a Summary Front Page, A second details page with the details from the first four categories, and then a third details page with the last three categories.

              The only thing you need to be carefull for is when two users make an invoice at the exact same time and thus use the temp tables at the same time.

              But that's not that likely to happen if your DB is only used by a few people.

              I have never used a portal in a report, so I don't know if that works well, and if that breaks well over multiple pages or not.

              sorry if this is a dumb question :)

              There are no dumb questions, only dumb answers. :)

              • 4. Re: I have a working database, now I need to generate printable reports :)

                Thanks, I am trying the resizing portals for now since that is the easiest to implement. If I need a more flexible approach later I will try out your method DaSaint :)