14 Replies Latest reply on Jan 18, 2017 7:57 AM by philmodjunk

    Sub Summary Reports or Page Breaks for my report

    bokenrosie

      I have a report that consists of multiple parts.  I'm trying to figure out what the best way to go about formatting it.  The word version starts with a page that has a bunch of information about a case.  Then there comes:

       

      Page Break

      Schedule A -

      Page Break

      Schedule B -

      Page Break

      Schedule C -

      Page Break

      Schedule D

       

      I think I'm comfortable getting all the schedules in there but I'm looking for how to put a page break before each one.  I will be starting the schedules with a text box if that helps.

       

      TIA

        • 1. Re: Sub Summary Reports or Page Breaks for my report
          philmodjunk

          You really need to tell us how your data is stored and organized in your file as there are different options you might try depending on that organization.

           

          You can specify a page break after every occurrence of a sub summary part in Part Setup, for example.

           

          You can also do multiple "Save as PDF" with the append option specified. Each subsequent "Save as PDF" starts on a new page.

           

          And there are ways to set up a sub summary part to force a page break after every X records with a bit of creative layout design...

          • 2. Re: Sub Summary Reports or Page Breaks for my report
            bokenrosie

            Thanks for your response.

             

            I have the schedule items stored in their own tables. They are each related back to the main table. 

             

            Each of the schedules has a header and then a total in that header. ie:

             

            SCHEDULE A-2 - STATEMENT OF INCOME COLLECTED

             

            Total of Schedule A-2 Original Account: $ 30,244.18

             

             

            Additions to Account

             

            Item Date                Description                                    Amount

            1     6/30/2014      Interest on estate bank account      568.66

            2     7/31/2014      Interest on estate bank account      587.91

            3     8/31/2014      Interest on estate bank account      587.76

            4     9/30/2014      Interest on estate bank account      568.90

            5    10/31/2014      Interest on estate bank account      586.61

            6    11/30/2014      Interest on estate bank account      566.57

            7    12/31/2014      Interest on estate bank account      585.80

            8      1/31/2015      Interest on estate bank account      586.14

            9      2/28/2015      Interest on estate bank account      529.39

            10    6/30/2015      Interest on estate bank account      592.22

            11    4/30/2015      Interest on estate bank account      583.31

            12    5/31/2015      Interest on estate bank account      595.90

            13    6/30/2015      Interest on estate bank account      577.01

            14    7/31/2015      Interest on estate bank account      596.60

            15    8/31/2015      Interest on estate bank account      596.95

            16    9/30/2015      Interest on estate bank account      578.01

            17   10/31/2015      Interest on estate bank account      589.64

            18   11/30/2015      Interest on estate bank account      564.69

            19   12/312015      Interest on estate bank account      583.85

            20   1/31/2016      Interest on estate bank account      584.20

            21   2/28/2016      Interest on estate bank account      546.82

              TOTAL ADDITIONS 12,156.94

             

             

            TOTAL OF SCHEDULE A-2 as supplemented $42,401.12

             

            [insert page break here for next schedule]

            • 3. Re: Sub Summary Reports or Page Breaks for my report
              philmodjunk

              That's not what I am requesting. How is it organized in terms of tables and relationships?

               

              What layout parts are on your layout (if you have gotten that far...)?

              • 4. Re: Sub Summary Reports or Page Breaks for my report
                bokenrosie

                I'm trying to include all parts in that one layout.  I was assuming (maybe incorrectly) that I could simply put a page break character immediately before each section in the layout.

                 

                Is there a way to tell a text box that it should start on a new page of the report?

                • 5. Re: Sub Summary Reports or Page Breaks for my report
                  philmodjunk

                  If you will do as i ask, I may be able to suggest a way to get the results that you want.

                   

                  I was assuming (maybe incorrectly) that I could simply put a page break character immediately before each section in the layout.

                  That would be nice, but it's not possible. If each section is a layout part (body, subsummary) then there are page break options that you can set for the layout parts inside the part set up dialog for that part.

                   

                  There are other options that we can also consider, but the devil is in the details not yet shared by you.

                  • 6. Re: Sub Summary Reports or Page Breaks for my report
                    bokenrosie

                    Really trying to share the relevant details.

                     

                    I think multiple body and subsummary parts would work but I don't think that's potential either.

                     

                    Perhaps I can send a copy of what I have somehow? Is there a way to attach the file?

                     

                    Thanks for your efforts.

                    • 7. Re: Sub Summary Reports or Page Breaks for my report
                      user19752

                      I have the schedule items stored in their own tables.

                      This looks bad design of tables. Usually this should be "schedule items stored in a table".

                      Then you can use sub summary part with page break for each schedule.

                      • 8. Re: Sub Summary Reports or Page Breaks for my report
                        bokenrosie

                        Entirely possible that I have bad design.  I've done some cool stuff with Filemaker but I'm still call myself a rookie. Here is a look at the direction I'm going with tables and Interface. My project is a hybrid between a report and a merge letter.

                         

                        The first image is the interface.  It is important to realize that I'll only ever need to print records for one will at a time. The image below is for the probate of "Peter Pan's" will.  For that one person there are a number of different schedules of money coming in and out of the estate during the probate period.  I have them separated as tabs on the interface with each tab performing the same.  There are a variable number of rows that will be added to each of those schedules during the period.  I need to print out a report that both summarized this information with some calculations as well as appendices that spell out all the transactions for each schedule.

                         

                        interface.PNG

                         

                        Here is the look at the relationships in the database.  I realize the schedules all look the same but I did not know of a way to limit the entry type by schedule type any other way.  Ie) If I am on tab Sch A then I only collect and calculate for type Sch A transactions.  I'll need to add and subtract from a starting number as shown above.  Original Value (top left of the tab) plus the sum of all the principal received items (top right) equals adjusted amount (bottom right)

                        tables.PNG

                         

                        Thanks for any and all advice to help me complete this.

                         

                        The final task will be a printed report on PDF that has this spelled out (see earlier posts).

                         

                        TIA

                        • 9. Re: Sub Summary Reports or Page Breaks for my report
                          philmodjunk

                          Yes, I suggest a change in your basic design to something that will not only make the report that you want easy to print out but easier to work with all around--especially should you find--say due to a change in the law, you have to add a new schedule to your database as the changes I am suggesting would make such a change primarily a data entry task rather than needing to add yet another table and set of relationships to your solution.

                           

                          You can rename things to your conventions and preferences, but it saves me time to describe this with names that are clearer to me:

                           

                          Three data source tables: Wills, Schedules, Transactions

                           

                          Wills::__pkWillID = Schedules::_fkWillID

                          Schedules::__pkScheduleID = Transactions::_fkScheduleID

                           

                          __pk fields are primary key fields that auto-enter either a serial number or text fields that auto-enter Get ( UUID ).. _fkFields are fields of the same data type but which have no auto-enter options set as they get their values directly from the matching __pk field. THe underscores simply allow these fields to sort to the beginning of alphabetized field lists that are common in FileMaker when you work with the design of your solution.

                           

                          A Text field in Schedules can label each schedule as "Schedule A", "Schedule A1" or whatever works for you and your clients. Note that you can create as many or as few schedules as you need as far as the data model is concerned.

                           

                          With this structure, you can set up a summary report layout based on Transactions that, via a find or go to related records from Wills, lists all transactions for a given Will. You can sort to group them first by Schedule, then by transaction date to put them in proper order. A sub summary layout part "when sorted by either _fkScheduleID or by corresponding "Label" field can hold the Label field and set up to have a page break before every 1 occurrence.

                           

                          For data entry purposes, you can set up new Occurrences of Schedules and transactions like this:

                          Wills::__pkWIllID = Schedules|Selected::_fkWillID AND

                          Wills::_fkSelectedScheduleID = Schedules|Selected::__pkScheduleID

                           

                          Schedules|Selected::__pkScheduleID = Transactions|Selected::_fkScheduleID

                           

                          This would allow you to place a portal to Transactions|Selected for recording transactions for a given Schedule for the current Will on your layout. A value list can be set up for selecting a schedule using the _fkSelectedScheduleID And a Script can be used each time you start a new Will that adds the needed Schedule records.

                          • 10. Re: Sub Summary Reports or Page Breaks for my report
                            bokenrosie

                            First of all - Thank You for your generous time in starting me down this new path.  I've gone ahead and started over with what I think you meant in creating tables and relationships.

                            newtables.PNG

                             

                            I have also filed n the Schedules list with the following values (ScheduleNum and ScheduleName)

                            Schedules.PNG

                             

                            I'm stuck on the next part of your instructions.  Specifically creating the data entry portals for each of the schedule types.  I think you refer to them as "occurences of schedules". I'm going to assume I can still use the tabbed interface from the original.

                             

                            If I want to create the new portal for entry of Schedule A (Schedules:cheduleNum=A) additions I start with...  

                             

                            I have gone to the Schedules table and entered records as above

                             

                            I am on a screen with Wills and I click insert -> portal-> (now I'm stuck?) how do I tell it to make these all A

                             

                            Again, thanks for your input.  I greatly appreciate you taking the time to help.

                            • 11. Re: Sub Summary Reports or Page Breaks for my report
                              philmodjunk

                              A "table occurrence" is a box on your relationship graph. It is not something that you put on your layout.

                               

                              While keeping your tabbed layout layout design is possible, I have not provided the details for that at this time. What I have described would work from a single portal with a value list formatted field nearby to select the schedule. This requires adding another table occurrence to your new relationship graph.

                               

                              Once you have that single portal working, we can consider whether you want to set up that tabbed layout or not.

                               

                              We can get that to work using scripts and s script trigger, but it locks you in to a specific set of schedules where changing that set requires changing your layout design. This is not a limitation  that you would have if you stayed with a single portal and a value list for picking the schedule.

                              • 12. Re: Sub Summary Reports or Page Breaks for my report
                                bokenrosie

                                Here's my new interface and relationships which I think implemented exactly as suggested.  The problem is a change in the dropdown does not seem to affect the data that comes in the portal.  New records added don't get the schedule type.  Any thoughts on what I might be doing wrong?

                                newinterface.PNG

                                 

                                newrelations.PNG

                                • 13. Re: Sub Summary Reports or Page Breaks for my report
                                  bokenrosie

                                  Is there a Starter Solution that implements the "portal data entry with field type" that I might refer to?

                                   

                                  I've been plugging away at this and cannot get it to work.

                                   

                                  TIA

                                  • 14. Re: Sub Summary Reports or Page Breaks for my report
                                    philmodjunk

                                    What field and from what table did you set up above the portal for selecting a schedule?

                                     

                                    How did you define its value list?

                                     

                                    Is the portal based on Transactions?

                                     

                                    For debugging purposes, include the _fkScheduleID field

                                     

                                    The relationship between wills and schedules is incorrect. You need two pairs of match fields in one relationship:

                                     

                                    Wills::__pkWIllID = Schedules::_fkWillID AND

                                    Wills::_fkSelectedScheduleID = Schedules::__pkScheduleID

                                     

                                    Enable "create" for Schedules in this relationship and enable "create" for "Transactions" in the Schedules to Transactions relationship.

                                     

                                    And Wills::_fkSelectedScheduleID would be your drop down for selecting a particular schedule