9 Replies Latest reply on Sep 5, 2012 5:33 PM by DavidMaffucci

    Modification Date Problem

    DavidMaffucci

      Title

      Modification Date Problem

      Post

      Hello, I've got a simple two table database. One table is "Donors" which has very basic information like name, address, phone. The other table is "Gifts" which stores individual gifts given by donors. The "Gifts" show up in a porthole on the Donor layout. My problem is that we use the modification date to see when donors have given but the modification date only changes if we make a change to the donor's contact info such as a new phone number or change to their address. 

      Is there a way to make modification date in the Donors database change if a new gift is added OR if their contact info has been tweaked? If so, how?

      Thanks for any help you can provide.

        • 1. Re: Modification Date Problem
          Sorbsbuster

          Why not show the Gifts in a portal (as now) sorted by the date of Gift, descending?  Then the top-most line in the portal will be the most recent date they gave.

          Not sure why you would want the use of the modification date as a tracker of gift date to be compromised with it changing if all you did was change a phone number.

          You can search on the Gift Date in the portal to find any Donors who contributed on any date or in any date range.

          • 2. Re: Modification Date Problem
            JimMac

            You need a new field int the Gifts table... Gifts::DonationDate.  The Modification date changeing from a Phone number change must be int the "Donors" table.

            the as Sorbsbuster says, the portal to Gifts will show Gift:Amount and Gift:Donationdate.

            Jim...

            • 3. Re: Modification Date Problem
              DavidMaffucci

              Thanks guys. I know that what you are saying makes sense however the user searches based on the mod date to see which donors are active vs inactive. We already have a donation date in the Gifts portal but they don't use it. 

              Can you think of a way to incriment the modification date in Donors when a change is made in the portal?

              • 4. Re: Modification Date Problem
                philmodjunk

                Max ( Gifts::DonationDate )

                Will return the date of the most recent donation. You can set this up as a calculation field in Donors.

                • 5. Re: Modification Date Problem
                  davidanders

                  There is a free open template by Richard Calton Consulting called Donations.

                  It may be worth investigating.   http://www.filemakerdonations.com/

                  • 6. Re: Modification Date Problem
                    JimMac

                    You keep worring about the Auto Modification date.  Create a new date field that you set whenever there is the ACTIVITY you think is correct.  Use a script trigger to set your new Donor:ActivityDate date field, for example.  Then set up a button with a Script that shows the USER which is ACTIVE.

                    Jim...

                    • 7. Re: Modification Date Problem
                      DavidMaffucci

                      PhilModJunk,

                      Thanks for that tip. It ALMOST works for me, I can use the Max function to get the newest donation date but having that present on the Donor's page does not increment the Modification Date (like say changing the phone number would) is there any way to automatically make that Mod date change when a portal record is added?

                      Thank you...

                      • 8. Re: Modification Date Problem
                        philmodjunk

                        Why would you want that? That's a question several of us have asked here.

                        You could set up set up this calculation:

                        Max ( DonorModDateField ; Max ( Gifts::Donationdate ) )

                        Thus, if a field in the donor table is modified, DonorModdateField updates with the current date. If a new record is added in gifts, that new date is returned by Max ( Gifts::Donationdate ) and the above calculation returns whichever of those two dates is most recent.

                        Note that if you specify a sorted relationship that sorts Gifts by DonationDate in descending order, you can simplify this to:

                        Max ( DonorModDateField ; Gifts::Donationdate )

                        • 9. Re: Modification Date Problem
                          DavidMaffucci

                          Thanks, once I saw the how the Max function worked I figured that a new field combining the actual mod date and the highest donation date would give me what I wanted so thanks for spelling it out. 

                          As far as why...I can't really answer that, seriously it's a user's request. My read is that it's 3 people working on the database and they just want to know the last time someone "touched" a given record. Be it to add a donation or to correct an address. In any case, this gives them what they want and that makes me and them happy.