1 2 Previous Next 15 Replies Latest reply on Sep 8, 2012 7:20 AM by PhilBiker

    Report Totals problem - new user

    PhilBiker

      Title

      Report Totals problem - new user

      Post

      I'm a recent Filemaker user, having transferred a membership application from Lotus Approach. I'm having difficulty in adding a grand total to a subscription report. I think the problem is in the structure I've created and maybe I have to change that to solve the problem.

      The report is selected and sorted records from the Person table, with Date and SubShare from the Payments table. I want a grand total of the subscriptions that appear on the report for the current year (usually about 1100). My attempts so far will only produce a total for a Person, drawn from a number of years. In case it is not clear from the relationship table, some of the figures are drawn twice from the Payments table.

      I would be grateful for any comments or advice.

      Relationships.png

        • 1. Re: Report Totals problem - new user
          charlesgeorge1@me.com

          If you are using filemaker 12, then the  problem is not with you, it is with filemaker.

          I am having the same problem with Filemaker 12, but no problems with filemaker 11.

                  Filemaker has very major issues with 12, I have shelved 12 for now.. It is not reliable.

          Not much help here, but, I am re-inputting 3 days work for my business from 12 to 11 because of the problems I have with 12

           

          Good Luck..

           

          • 2. Re: Report Totals problem - new user
            philmodjunk

            What you want should be fairly easy to produce with a summary report--probably on a layout based on your payments table, but there are many possible options and the exact details of your report is not really clear from your original post. Can you "mock up" an example of the report that you want?

            • 3. Re: Report Totals problem - new user
              PhilBiker

              First - I am using Filemaker 12 - I was just about to start development when the upgrade was offered.

              Here's a snapshot of the current report, with field names as the column headings. PersonID & FullName are from the Person table, DatePaid & SubShare are from the Payments table. What I want is the total of the SubShare column. My attempts so far produce a total for the final record which includes previous year figures not appearing in the report.

              Note that the SubShares for the first 2 lines in the report (Margaret Webb & David Webb) come from a single record in the Payments table. I can see the logical problem with this, but not a solution.

              • 4. Re: Report Totals problem - new user
                philmodjunk

                The many to many relationship does complicate things. If each payment were attributed to only one Person, a summary report based on the Payments table could use sub summary layout parts to group total payments by person (and this can be set up to show one row for each person--not the individual payments)

                The layout you show would appear to make your payment figures misleading as Mr. and Mrs. Webb together made a payment of 5Lbs.

                Given that a Payment is never linked to more than just 2 person records...

                It would seem like this format would represent a more accurate one for your report:

                MembershipID                Members                 Date           SubShare
                1234                             Margaret Webb        1/10/2011      5.00
                                                    David Webb
                2354                             Elizabeth Penry        1/10/2011     6.00

                and so forth...

                                                                             Grand Total: XXXXXXX.XX

                Such a report is possible if you base your layout on Payments and create it with a subSummary layout "when sorted by" MembershipID. You'd then place a portal to an occurrence of Persons to list the individual names in most cases, but given your relatiionship, You'd have to add name fields from Person 2 and Person 3--setting them to slide up and "resize enclosing part" to remove unused space when printing this report. A summary field set to compute the total of your payments field would correctly sub total payments by member ship when placed in ths sub summary part and also would compute a grand total when placed in a Grand Summary, Header, or Footer.

                Note: I'd Set up your person to payments relationship like this:

                Person::PersonID = Person_Payment::PersonID
                Payment::PaymentID = Person_Payment::PaymentID

                and to membership:

                Membership::MembershipID = Person::MembershipID

                This produces these relationships

                Membership------<Person>---------<Person_Payment>-------Payments            (-----< means "one to many" )

                With this arrangement, my layout would be based on Payments with a Portal to Person used to List the individuals. The portal can be set to slide/resize to eliminate unused portal rows when printing.

                • 5. Re: Report Totals problem - new user
                  philmodjunk

                  Sometimes time and caffiene brings up a better idea....

                  I think the following relationships would be much simpler than my last suggestion:

                  Payments>-----Membership-----<Person

                  Membership::MembershipID = Payments::MembershipID

                  Membership::MembershipID = Persion::MembershipID

                  • 6. Re: Report Totals problem - new user
                    PhilBiker

                    Thanks for both those suggestions. I may not have been clear enough in my original info - the key problem is that we have Memberships and Persons. Memberships may be single or joint (2 persons). Most data is entered by Membership (including subscriptions), with only 2 items entered by Person, one of which is an indicator for inclusion in this report.

                    It's fairly straightforward to combine 2 persons in a membership for data entry and production of mailing lists, etc. This is the top part of my Relationships graph. The problem is viewing the data from the Person table, which works fine as the lower part of the graph, except for totalling in the report. I can see why this is - the report includes 2 entries from a single record in the Payments table.

                    To clarify, the membership subscription is £6 single, £10 joint. The SubShare in the Payments table is calculated as £6 single, £5 joint per person. So the Webbs pay £5 each, as my layout. For our own use, we could simply use the the total Membership subscription, but I can't alter the need to report the shares separately - it's a Customs & Revenue requirement which gives us a significant boost in income.

                    I don't really want to change my basic structure. It's been driven by the need to minimise time and keystrokes on data entry. We are a voluntary organisation and our Membership Secretary has the most onerous job. There are about 1000 records to be entered between 15 December and 15 January each year, with the associated banking. I have the entry of renewals by membership down to  New Record > enter number > Tab to date field > press Enter to accept.

                    I've been looking at other options. This report is annually, and fixed in format. I thought a 'recurring import' might work, but I'm now looking at exporting the required fields to a new file and printing the report from there. I have all the basics working, although I can't get FileMaker to export just the found set of 1100 records - I get the full 8000. That's no big deal - a scripted find & sort on opening the file will give what I need.

                    It's a bit of a kludge, but I have to hand this over for use from 1 October, and I still have to write the menu and the manual.

                    Apologies for the length of this. I'm still open to an alternative, although implementation might have to wait until next summer's slack period.

                    • 7. Re: Report Totals problem - new user
                      philmodjunk

                      we have Memberships and Persons. Memberships may be single or joint (2 persons). Most data is entered by Membership (including subscriptions), with only 2 items entered by Person, one of which is an indicator for inclusion in this report.

                      This is exactly what I understood to be the case.

                      To clarify, the membership subscription is £6 single, £10 joint. The SubShare in the Payments table is calculated as £6 single, £5 joint per person. So the Webbs pay £5 each, as my layout. For our own use, we could simply use the the total Membership subscription, but I can't alter the need to report the shares separately - it's a Customs & Revenue requirement which gives us a significant boost in income.

                      That is a key clarification as that is not what I understood from your original post. My incorrect interpretation was that the 5 pound figure was a single payment rather than one half of a joint payment.

                      In which case, you can base a summary report on your PersonLinks table in order to list each person and their "subshare". (You can add fields as needed to this layout from Payments 2 and Person.)

                      To get a grand total, you can use one of the following methods:

                      1) Define a summary field in payments. Add a new occurrence of payments and link it to personLinks with the X operator so that it matches to all records in Payments. Put the summary field on your PersonLinks layout inside a one row portal to this new table occurrence. Use a portal filter to filter the related records to those of a specified date range.

                      2) Define a calculation field, cSubShare, in personLInks as Person::SubShare so that it just copies the subShare into PersonLinks. Then define a summary field in PersonLinks that comptues the total fo cSubShare.

                      I don't really want to change my basic structure. It's been driven by the need to minimise time and keystrokes on data entry. We are a voluntary organisation and our Membership Secretary has the most onerous job.

                      I will gently suggest that restructuring your tables to match that of my second suggestion may in fact make this job easier for you. Granted, that it's not a change to make lightly as you'd need to make some batch updates of your data to facilitate the change, but an inefficient design generally makes life more difficult for you all the way down the road from data entry to creating reports from the data entered. Please note that for your current setup, you have to create a person links record for each member every time you log a new payment and there has to be two such records created for a single payment when it is a joint membership. What I am suggesting could make it possible to log payments simply by entering the amount, the date (if not auto-entered) and selecting the membershipID, though this approach would need a bit of modification in light of my new understanding of your database structure and function--it becomes an even simpler set up.

                      • 8. Re: Report Totals problem - new user
                        PhilBiker

                        OK, I think I understand what you're suggesting, but I will have to try it on a new draft before I'm sure. Meanwhile, a further clarification or two:

                        Please note that for your current setup, you have to create a person links record for each member every time you log a new payment and there has to be two such records created for a single payment when it is a joint membership. What I am suggesting could make it possible to log payments simply by entering the amount, the date (if not auto-entered) and selecting the membershipID, though this approach would need a bit of modification in light of my new understanding of your database structure and function--it becomes an even simpler set up.

                        The person links records are only created when a new membership is entered - granted, the manual creation of these entries is an additional step, but we have about 120 new memberships a year as opposed to over 1000 renewals, so it's not a big deal. The only entry made in the payments table for a renewal is the membershipID - the date defaults to today and all other fileds are either looked-up or calculated. The personID is fixed for an individual over the past 16 years (I'm No 7 of 2700) and I can't change that - it is referenced in many places outside this database. The membershipID is a different matter. This version is a recent creation - the previous database used a new series each year, so tracking a membership through the years was not possible. The current version is allocated whenever the constitution of a membership changes - single to joint, joint to single or change of partners as well as new members joining. One person may move through 3 or 4 different memberships over a period.

                        I will explore your suggestions, and I'm not afraid of batch updates to the data if necessary (I recently transferred it from 16 tables with radically different structure). However, time constraints won't allow this for this year. Fortunately, my only real problem is this one report, and I can produce it this October from the existing Approach database - a fitting final act! I will then have a year to revise my design.

                        Many thanks for your time - I will update you as soom as I've had chance to try your report suggestions, hopefully in a day or so.

                        • 9. Re: Report Totals problem - new user
                          PhilBiker

                          I've now tried the suggested methods of obtaining the total, but in every case the total comes to more than the sum of the records appearing in the report. I can see in each case which group of records is included, but can't find a way of changing the sum.

                          I will have to leave this particular question for further consideration next year. Thanks for your suggestions - I will no doubt come back to them later. Meanwhile, I have a couple of questions about basic file management, for which I will start a new topic rather than confuse this one.

                          • 10. Re: Report Totals problem - new user
                            philmodjunk

                            Feel free to describe which of the two options you used and the details of how you implemented it. This should be very simple to set up and so the fix needed to get it to work could be something very easy to do on your part.

                            • 11. Re: Report Totals problem - new user
                              PhilBiker

                              Thanks, Phil, but I have had to abandon this particular problem for this year. I'm still working on other outputs and the menu structure, and I have a meeting on Monday to finalise things. I need to hand the database over in about 10 days time to allow the membership secretary to get familiar with it for 1 October.

                              I can produce the report for this year from the current Approach database. I will return to the problem next Spring/Summer, which is the quiet time for the membership administration. I still haven't worked out how to co-ordinate my continuing development with the memsec's active use. We each use our personal PCs, and there's no possibility of linking them.

                              • 12. Re: Report Totals problem - new user
                                philmodjunk

                                Just be aware that you may be passing on a fix that takes 5 minutes or less to implement.

                                I still haven't worked out how to co-ordinate my continuing development with the memsec's active use. We each use our personal PCs, and there's no possibility of linking them.

                                That one's quite a mess as "synching" such separate databases can raise a lot of very thorny issues and the complexity increases geometrically with the number of independent copies being edited. 360Works and SeedCode both offer synch tools--primarily for use with FMGo where this is a more common issue, but I think they may also work for synching FM Pro databases.

                                Another option may be to host your database from a FileMaker Pro hosting service so that each of your users use internet access to connect to a single copy of your database. If you can get that to work and the monthly fee charged is within your budget, you are much better off than trying to synchronize independent copies of your database.

                                • 13. Re: Report Totals problem - new user
                                  PhilBiker

                                  I was afraid I would get that answer. Fortunately there will only ever be two copies - mine used for development, and the memsec as actual user. Whatever we do has to be a zero-cost option. Our total income is about £10,000, of which half goes to the national organisation. The remaining £5,000 is soon used on admin for 1600 members. Purchase of 2 licences for FM Pro, even at non-profit prices, was a stretch.

                                  I think I will have to rely on updating copies via Dropbox, and arranging for only one of us to work at a time. This wasn't a problem with Approach, where the data tables were in a separate file from the layouts and indexes.

                                  • 14. Re: Report Totals problem - new user
                                    philmodjunk

                                    In that case, you can just do the same as you did with approach. Have two files with one the interface and one the data file. Then you can update the interface file anytime you need to and just swap out the old interface file for the new.

                                    For updates to the data file--which is much less common, you can script a data import so that your user need only click a button in the new copy, select the original copy in an open file dialog and the script does the rest--importing data and updating serial number fields.

                                    Note, from your previous post I was picturing multiple users all entering data into independent copies of your database. Surprised

                                    For how to use a data separation method with FileMaker: Convert to Seperation Model

                                    1 2 Previous Next