12 Replies Latest reply on Mar 2, 2015 1:01 PM by philmodjunk

    Report from Two Tables

    NancyatUCM

      Title

      Report from Two Tables

      Post

      After looking at the other "Report from Multiple Tables" posts, and trying several approaches I'm still confused - I'm afraid I need more step-by-step instructions as a relatively new FileMakerPro user.  I have a "UCM Mailing List" table w/ contact info including names, and a "Gifts" table with gift info including gift date and gift amount.  They are related by a key ID field, and there is a one to many relationship w/ the Gifts table being the many.  What I'd like to end up w/ is a list of donors where names don't repeat for each gift, with a subtotal of each donor's gifts, and a grand total of all the gifts of all the donors at the bottom of the report.  Something like:

      John Doe                                     4/5/2011       $200

                                                            6/2/2012      $350

                                                              Subtotal:   $550

      Jane Smith                                     5/4/2013   $100

                                                               Subtotal: $100

      Bob Brown and Sue Brown            3/2/2010   $25

                                                              4/6/2014  $25

                                                      Subtotal:         $50

                                                                   Grand Total $700

      I'd like the report to be as visually clear and condensed as possible, and be able to sort by Last name, and sort gifts by date under each donor (but maybe that will happen automatically, since they are entered in date order as they give?)  I'd also like to be able to set different date parameters (eg. to get all donors for 2014 and not see their donations from other years).  Thanks for any help - I've already spent a ton of time on this, and I know it can't be that difficult!

      Screen_shot_2015-02-25_at_12.31.05_PM.png

        • 1. Re: Report from Two Tables
          philmodjunk

          I'm going to suggest something slightly different than what you specified. Once you can get it to work, you can decide whether or not you want to complicate things in order to get exactly what you have described as that can be done by modifying the final result of what I describe, but it get's a bit tricky.

          Set up a list view layout that specifies: "Gifts" in "Show Records From".

          Use Part Setup to add a Sub Summary layout part, "When sorted by Gifts::Key". Select the "print above" option for it.

          Put your name field(s) from UCM Mailing list inside this sub summary layout part.

          Put the Gift Date and GIft Amt fields in your body. Size the body to be just slightly taller than these fields.

          Add a "print below" sub summary part with the same "when sorted by" field as the first.

          Put a summary field from Gifts inside this sub summary layout part. By what is shown, that might be your Gifts Total field.

          Now Enter Browse Mode and do the following:

          Perform a find for just the gift records that you want in your report. If, for example, you enter 2014 into the Gift Date field, you find all records with that year specified in the Gift Date field.

          Sort your records by specifying the following fields in this precise order:

          Specify a your donor name field(s) from the UCM Mailing List table
          Specify the Key Field from Gifts. (Leave out this field and you won't see any sub summary parts appear)
          Specify the gift date field from Gifts. (this last is not strictly needed, but will correct for any odd things that might have happened with this date field during data entry such as getting date records entered out of correct order.)

          Note that sub summary layout parts are not visible until their "sorted by" field is used in the specified sort order for your found set.

          • 2. Re: Report from Two Tables
            NancyatUCM

            (Don't see a way to reply above to your answer, so I guess I'll "Post a new Answer")

            Thank you so much - this looks promising but I'm not there yet.  I can see the names and some gift info (once I finally figured out I needed to be in 'form view' vs. 'list view' to see the names), but where a donor has given more than once in the specified date range, I only see one gift, rather than a list of them.

            This also brings up an old problem about my "Gifts Total" field.  I have never really gotten this to work. I have a "Total Gifts since 1/1/13" field which successfully totals all gifts since I re-vamped the database in January of 2013, but I have not been able to set up additional fields which would show sub totals on my main layout for other specified time periods (eg. total given by a particular donor in 2013, or 2014).  I can live without those, but most importantly, I need a field which could show the total just for a found specified date range - eg a report totaling gifts which followed the mailing of an annual appeal letter on November 13th through the end of the year.  So I guess we have 2 issues: how to have multiple gifts listed, and how to have the total of just those gifts show in the subtotal for that donor.  I'd also like a grand total at the bottom for all gifts in that time period (ie a total of the subtotals). Thanks!

            • 3. Re: Report from Two Tables
              philmodjunk

              so I guess I'll "Post a new Answer"

              Post a new answer is the correct way to post follow up questions.

              once I finally figured out I needed to be in 'form view' vs. 'list view' to see the names

              You need to be in list view, not form view. And you should see all of the gifts for a given donor once you are in list view, not form view. The name field needs to be in the sub summary layout part and you need to have correctly sorted your records before they display correctly.

              I have not been able to set up additional fields which would show sub totals on my main layout

              You do not need sub total fields. (but you can set such up if you want to.) A single summary field, placed inside a sub summary layout parts will compute a sub total for each donor. If you want a sub totals for each year, you can use the same field inside a sub summary layout that uses a "year" field as its "Sorted by" field, But I wouldn't get into that until your original report works. It can give you totals for a given year if you just perform a find for a year. Later, you can try adding another sub sumary layout on the same or a separate report layout and can set up a calculation field that returns just the year from your date field by using the Year function.

              You may need to upload a screen shot of your layout, taken while in layout mode, if you still can't get this to work.

              • 4. Re: Report from Two Tables
                NancyatUCM

                Thanks again - I can't find what I'm doing wrong, so screenshots are below: layout and sorting order.

                • 5. Re: Report from Two Tables
                  NancyatUCM

                   . . . and sorting order

                  • 6. Re: Report from Two Tables
                    philmodjunk

                    Your layout is based on UCM Mailing List when it should be based on Gifts. I can tell by which field names have leading :: characters in their displayed names.

                    Open Layout Setup and select Gifts from the "Show Records From" drop down. (And you might choose to create a new layout and select this same option from the show records from drop down in the new layout wizard.)

                    • 7. Re: Report from Two Tables
                      NancyatUCM

                      Yay! That worked.  I thought I had based it on Gifts, but clearly not. Changing the "show records from' didn't work, but creating a new layout did.  I have some tweaking to do, but I think I can get there now - thanks!

                      • 8. Re: Report from Two Tables
                        philmodjunk

                        After re-orienting a layout to a different table occurrence context by selecting a different name from "show records from", you usually need to check field specifications and other layout details to make sure they are still correct. So starting from a new layout may have been easier--especially for someone new to this kind of thing.

                        • 9. Re: Report from Two Tables
                          NancyatUCM

                          Thanks.  Last question (I think).  How would I get a grand total of all of the gifts at the bottom of the report?

                          • 10. Re: Report from Two Tables
                            philmodjunk

                            Take another copy of that same summary field and put it in either a footer or trailing grand summary layout part. If your report will be more than a page in length, use the trailing grand summary to only have the total shown on the last page.

                            • 11. Re: Report from Two Tables
                              NancyatUCM

                              Thank you that worked.  Unfortunately, each solution brings me to a new question!  Actually I have two now:

                              My report shows 3 odd records at the end of the report w/ no donor name.  The only info showing is the 4 fields from the Gifts table, but I am not able to find those records in another layout that shows more fields.  I have tried doing a find in each of the 4 fields shown (key, giftdate, giftamount, reason).  Looking at my records in ID (key) order, those ID #s don't seem to exist anymore.  Any ideas on how I might see more complete data for these 3 donations?  I'm not even sure what info might be pertinent to this question.  ('Mailing List' is a database, with the related table 'Gifts' - should I be able to look at Gifts as if it were its own database? Maybe I could find them there if the reason they're not showing is that the key ID fields don't match? (they're automatically inserted though).  I'll upload a screen shot of the end of my report.

                              My second (and I think much easier) question is whether I can use merge fields for my donor names to make them more compact in the report.  It would be clearer visually if the names weren't stretched out with lots of blank space in between, especially where we have a spouse first and last name in addition to the main donor first and last name. I tried, but the names didn't show even after sorting.

                              • 12. Re: Report from Two Tables
                                philmodjunk

                                It would appear that you have three records in Gifts that do not link to any records in the other table. One simple way to get an overview of all data an all fields in a layout's underlying table is to temporarily switch to table view and, if necessary, use the modify button to add more fields to the view.

                                But my best guess is that if you went to a layout based on UCM Mailing List and performed a find specifying 2671 in the UCM Mailing List::Key field, you would either not find a record or find a record where the other fields from that table that appear in your report are empty.