1 Reply Latest reply on Nov 14, 2013 12:01 PM by philmodjunk

    How do I insert most recent date/donation from related table into a database layout?

    NancyatUCM

      Title

      How do I insert most recent date/donation from related table into a database layout?

      Post

           How do I insert the most recent date/donation amt. from a related "gifts" table into my "thank you letter" layout in my "mailing list" database?  When someone makes a donation, I enter the information into our "main layout", then switch to the thank you letter layout (which has merge fields for the person's name, address, date and the amount of the gift).

           For the first gift it works like a charm and I can print the letter.  If they give twice, the first gift amount and date still show in the letter layout rather than the most recent gift amt/date.  I have tried sorting the table by gift date but it didn't help.

           At one point I tried using portals rather than merge fields and somehow got it to work once (can't even remember how at this point), but the portal doesn't shrink or expand depending on the information (eg. a $25 gift takes up less space in the text of the letter than a $250,000 gift (should we be so lucky!) and the spacing looks odd.

           This seems like an issue every small not for profit would face so I'm surprised that it is so difficult. I'm self-taught in FileMaker though - am I approaching it in completely the wrong way?

           I'm including screen shots of thank you letter layout and main layout in case that helps.

      Screen_shot_2013-11-14_at_1.14.29_PM.png

        • 1. Re: How do I insert most recent date/donation from related table into a database layout?
          philmodjunk

               When you place a reference to a field from a related table directly on your layout, such as is done with your merge fields here, the relationship references the "first" related record. That will be the oldest related record unless you use a sorted relationship to make some other related record the "first" such record. To specify a sort order for related records, open Manage | Database | relationships and find the relationship line linking your layout's Tutorial: What are Table Occurrences? to the table occurrence named "gifts". Double click that line and you'll see a button for specifying a sort order for Gifts.

               Here are two sort orders that you might specify to get the results that you want:

               Option 1: IF you have a date field in the gifts table, select that field for your sort order and sort the records in descending order.
               Option 2: If there is an auto-entered serial number field in gifts, you can sort in descending order on that field.

               The only difference between the two options is if you get two gifts records for the same donor with the same date, the first option may not show the most recent gift but the second option will.