8 Replies Latest reply on May 22, 2012 1:05 AM by Sorbsbuster

    For All FileMaker Pro 11 Advanced developers/users



      For All FileMaker Pro 11 Advanced developers/users


       I need to create a multipage report that frlow continuously (cascades) with page numbering from start to finish the pulls data and has data labels.  This report will have to have a front header page from the main database table, and a section, with different data and formatting for each of 14 other tables that store data.  The report is a daily report (used in Construction).  Each report in total contains contemporaneous data (records) from each of the 14 table where the records match the date of the report.  I can easly do this in InfoMaker, Crystal Reports, Access, and other ODBC/JBDC report writers.  But, I have found no way to accomplish this within FileMaker.  Each FileMaker report addresses data from only one table.  I cannot, as I see it, use a report database wherein I combine data from different tables.  The data is different and it must be arranged differently for each section to make sense and be a good communicating report.  I also need different fonts, font sizes, bold, underline abilities.

      I had a long talk with Filemaker tech., but we were unable to come up with any way to produce this report.  So, I am putting this tackling problem out to all of you, smarted than I in InfoMaker to propose solutions.  I hope that someone has an answer.  If not I must discard all the work I have done. 

      My contact info is gordona@visionconsultants.com and 602-992-5438,  Good luck! Gordon in Phoenix

        • 1. Re: For All FileMaker Pro 11 Advanced developers/users
           Would't portals from each of the fourteen related tables do the trick? You would have control over content and formatting. RW
          • 2. Re: For All FileMaker Pro 11 Advanced developers/users

             Unfortunately no.  Portals will not expand to accomadate a variable number of records.  You must preset the max visible rows in a portal  So portals which I thought of first are not a solution.  I was told that there might be a way to show less rows, but not more rows.  Darn!

            • 3. Re: For All FileMaker Pro 11 Advanced developers/users

              You've hit one of the stickiest issues that exist in FileMaker.

              This is not a problem if your 14 records are linked in a linear, one to many relationship. Table1----<Table2---<Table3 etc...

              In that case, you can base your report layout on the 14th table and reference the other data via the related table occurrences, putting the data as needed in headers, grand summaries, and sub summaries as needed to get the report you need.

              Is this a report that only needs to look right when printed/saved as PDF or also in browse mode?

              If you only need to print/PDF this report, portals may be a workable option. You specify a very large number of rows for each portal, then set them to slide up and also to shrink the enclosing part. This will only be visible in preview mode, when printed or when saved as a PDF. I realize that you may have such extreme variability in numbers of related records, that this approach might be very difficult to use effectively, but it's simpler than some other options, so you need to carefully consider it before the other more cumbersome approaches.

              The "ugly" option we try to avoid whenever possible is to design a "report table" with sufficient fields to handle all types of records needed for your report and then import the needed records into this table. It takes a lot of messing around with layered fields, conditional format controlled field labels or calculation fields serving as field labels, etc to finally pull it off. Not pretty when you see it in layout mode and a real hassle to work with when you need to change the report design, but it can be done in most cases.

              Another trick is to use a scripted Save as PDF system that appends reports generated from different layouts into a single file. This can be done in a way that adjusts for page numbering, but each appended PDF starts a new page so getting the parts to "flow" smoothly will likely rule this option out for you here.

              • 4. Re: For All FileMaker Pro 11 Advanced developers/users

                Tables to the most part are one-many, not linear (series).  If FileMaker is on release 11 and this is one of the stickiest issues, then why hasn't a good developer at FileMaker solved this by now.  As an Oracle Gold partner and a longtime user on MS SQL with many reportwriters this is not an unsolvable problem.  Even providing the hooks (parameter passing) so that a commercial report writer can be called from within a Filemaker application (runtime database system).  I have tried the PDF option, but that does not solve my formatting issues.  This experience has dropped my love of FileMaker (Otherwise) to my shoe laces. What does it take to wake up someone at FileMaker?

                • 5. Re: For All FileMaker Pro 11 Advanced developers/users

                  Sorry to break etiquette but, how do you link crystal reports to Filemaker?

                  • 6. Re: For All FileMaker Pro 11 Advanced developers/users

                    Maybe I don't understand the question, but why not specify field contents from other data tables using the dialog "Specify Field?"  I do this.  This way I can give a user about 20 different reports using any combination of field content from within the table, related records in other tables in the file, or related records in another FM file.  I did this before FM offered portals.

                    • 7. Re: For All FileMaker Pro 11 Advanced developers/users


                      You should investigate Bruce Robertson's Virtual List technique. Google "filemaker virual list" this should find you sufficent links to adapt the technique to your needs. 

                      • 8. Re: For All FileMaker Pro 11 Advanced developers/users

                        As I understand it you have a list of records in Table 1 which you want presented in a certain way, then another list of records from Table 2 which you want presented in a different way, etc.  Maybe some of the records in Table 2 need data from some records in Table 1.  When you print them you want them to be a single report.

                        Why not create the right layouts in each table, then go to Table 1, print it to pdf, go to Table 2, append it to the first pdf, etc?