3 Replies Latest reply on Dec 5, 2016 12:26 PM by jbu

    Multiple reports on same layout


      Hey there --

      I have a table that  records 5 dates per record: a reservation date, a materials due date, a cancellation date, and start and end dates.


      I want to run a report that would list all the items which have a date in the above fields that matches today's date (or other specified date).


      The report would be divided into subsections with the above fields as headers, underneath would be listed all the items that match.


      Reservations Due

      Item Name, Item Cost, Due Date, Date Run Dates


      Materials Due

      Item Name, Item Cost, Due Date, Date Run Dates


      Cancellation Deadline

      Item Name, Item Cost, Due Date, Date Run Dates



      Item Name, Item Cost, Due Date, Date Run Dates


      I can more or less pull the reports individually (or at least I'm getting there), but how do I pull all this on one report that can be emailed or printed?

      Thanks in advance for this noob question!

        • 1. Re: Multiple reports on same layout

          It would be better if you had a related, say, Events table where one record is a date and an event type.


          Then you would simply search in a single date field, sort the found set by type and use a sub-summary part to display that type as header. The other data (Item name, cost) come from the related "parent" table.


          The report needs only little preparatory work on the layout, and just a brief script.


          With your current structure, OTOH,  you need a lot of calculations to determine the correct event type, field placement and headers. Should a record have the searched date in more than one field, it could still only appear in one section, which in itself would be a show-stopper.

          • 2. Re: Multiple reports on same layout

            If you can create the reports individually, You can use a script to do the same but save them as PDF's. By using the option to append PDF's you can create a single document will all of the individual reports in it, but with each sub report starting a new page.

            • 3. Re: Multiple reports on same layout

              Hi Kyle,


              Since all four reports share the same fields, that makes this easier. I recommend the following procedure.

              1. Create a new table with those fields (Item Name, Item Cost, Due Date, Date Run Dates). This will be a temporary table for our report. Let’s say we call it “Report”.
              2. Add 1 additional field to hold the subsection name. Let’s say we call it “subsection”.
              3. You may already have scripts that pull your data for each report, for brevity, let’s say you do and we’ll refer to them as the 4 sub-scripts.
              4. Create a new script that will be performing your entire procedure. We’ll call it “Complete Report”.
              5. In that new script, you want to run sub-script 1, then go to your new “Report” table layout and import the results from the sub-script you just ran.
              6. After the import, perform a “Replace Field Contents” step to set the “Section” field to the name of the subsection you just imported.
              7. Repeat steps 5 & 6 for the other sub-scripts, always importing into the same “Report” table and setting the subsection name accordingly. You could loop all of this if that’s your style.
              8. Once you’ve imported all the data into the “Report” table, you simply show all records and sort by the “subsection” field. That’s it for the script.
              9. The “Report” layout would be setup with a Sub-Summary when sorted by the “subsection” field, and it would only have the “subsection” field on it. The Body part would have the other fields.


              Please note that the report will only work if you’ve sorted after all the imports. You should probably also add a step that shows all records and deletes them from that “Report” table at the start of the script so that you’re only ever viewing just your data.


              However, if there will be more than one person running the report at the same time, then you may want to add an additional field that sets a unique identifier on all the records you import (you can add an extra “Replace Field Contents” step in #6). Then later in step #8, instead of “Show All” you perform a search for the unique identifier. This is actually a better method, as deleting all records can be dangerous if there's more than one person using the database. If you use this method, then you may want to delete all records after your done viewing the report or maybe have a server-side schedule wipe the table on an intermittent basis so it doesn't grow on you.


              Anyway, that’s the cleanest method I could think of that allows you to display all the data in one big report with sub-headings and such, and you can customize the layout and the scripting as needed.


              WARNING: I just wrote this down off the top of my head, so there may be other things to consider. I presume you've got a decent understanding of FileMaker as well as importing and scripting. Your mileage may vary. I make no warranties whatsoever. Good luck!