6 Replies Latest reply on Jun 12, 2012 7:30 AM by gobbles

    Multiple sub-summary parts and fields on a single page.




      I have been banging my head against a wall with this for some time. I am still kinda new to FM and any help would be greatly appreciated.


      Some background:

      I have a relational database that stores information about "clients" and "appointments"

      We collect information such as "location", "background of clients", "languages spoken at home", "type of contact (phone, face-to-face)" etc.


      At the end of each month we need to produce a report about the various types of clients and appointments that have been dealt with that month.


      I have been given a fairly specific layout for the monthly report. I need to show breakdowns for each of a number of catagories as well as their totals.


      For example:



      southern outreach 5

      main office 10

      Northern Office 35


      Languages spoken at home by clients;

      russian 6

      english 45

      italian 2




      I have to do this for a whole heap of catagories. I can do them individually, no problem. But I cannot for the life of me find a sensible way of having them all show within a reasonable layout.


      I tried writing an elaborate script that sorts for each of the sub-summaries and than appeands the results to a PDF. This kind of works, however, filemaker insists on putting a page break after every append operation leaving far too much blank space. It has been rejected as I need to produce a more succinct report. Ideally only 1-2 pages. essentially i need each sub-summary to be a small table.


      The only other thing I can think to do is write a long script that manually does the sub-summary calculations and dumps the entire result into a special "report table". However, this is not ideal at all as the contents of some of the fields are going to change over time and I wont always be around to fix the script.


      What would really be awesome is if there was some way to isolate subsummary reports into blocks within the same layout and sort each block independently. Is anything like that possible at all?



      Any help at all would garner my eternal gratitude.

        • 1. Re: Multiple sub-summary parts and fields on a single page.

          I never did find a way to do this elegeantly.


          No one has any ideas?

          • 2. Re: Multiple sub-summary parts and fields on a single page.

            Do you have a table of Locations, where each location is a unique record (same question about Languages)?




            Upon re-reading, I find I don't understand if the report is about an individual client, or a group (found set?) of them, or ... ?




            Message was edited by: Michael Horak

            • 3. Re: Multiple sub-summary parts and fields on a single page.

              I see the confusion, ok.


              The report is a breakdown of information and summaries about both the Clients and the Appointments that they had during a given one month period.


              There is a table of Clients with a 1-to-many relationship to a table of Appointments.


              I can generate all the subsummaries within their own layouts and then save them all together as one PDF (my current working solution) however it is not pretty and there is a pagebreak after every section leading to needless blank space. Ideally I would be able to take subsummaries and lay them out together in a single layout to make it all more compact and more visually pleasing.


              Is anything even remotely like that possible?


              the "Location" catagory is from the appointment table and refers to the place te appointment occurred. I listed it purely as an example. It is one of many catagroies I need include in the report.

              • 4. Re: Multiple sub-summary parts and fields on a single page.

                You haven't really clarified my confusion. If the report is produced from the Appointments table, and each appointment has a location attribute, you can summarize the found records by location (or by client and location).


                If you also want an independent breakdown by languages (assuming each  appointment also has a language attribute), then it becomes more complicated, since the sort requirements are conflicting. This is where a custom function could be useful. Alternatively, you could have a script write the summaries, one by one, into a global text field. In version 12, you also have the option of using SQL to retrieve the sub-summary values.

                • 5. Re: Multiple sub-summary parts and fields on a single page.

                  The challenge is that this is not "a" report, its really multiple reports. An appointment will be counted once for location, once for languages, and yet again for each other summary. However there is only one instance of the apointment, so the report needs to be generated multiple times. There is no way around the problem.


                  The issue is therefore one of display. I'd suggest it is an ideal canditate for virtual lists: run the various summary reports sorts sequentially to generate the virtual data, then display them once as a sumarised report.

                  • 6. Re: Multiple sub-summary parts and fields on a single page.

                    Ok Virtual Lists sound absolutely awesome, I hadnt heard of them before. That is exactly what I was looking for.