2 Replies Latest reply on Feb 19, 2013 3:03 PM by MartinLange

    Creating a list of Donors who need to be thanked after 45 days of receiving their gift

    MartinLange

      Title

      Creating a list of Donors who need to be thanked after 45 days of receiving their gift

      Post

            

      I’m running a Donor Management database on FMP 11, and I am trying to create a notification when it has become critical to thank a donor for a donation. I have two date fields that I want to compare - DonorManagement::LastThanked and DonationEntry::LastGiven. I want to generate a script that creates a list of all donors who have not been thanked within the last 45 days or more.

            

      I have tried writing a script, but it does not generate the result I want. I am performing a find, but when I try to find if the LastThanked field is more than 45 days old I get the following message: 

            

      The value of this field must be a valid date in the range of years 1 to 4000 and should look like “12/25/2003”.

            

      I am really baffled how to create a Find function that compares the “Last Given” date field with the “LastThanked” date field to determine what needs to be thanked right away and generate a PDF report that I can print.

            

      I am not a total newcomer to FMP, but I don’t have the depth of knowledge needed to tackle this problem. Any nudges in the right direction would be appreciated.

        • 1. Re: Creating a list of Donors who need to be thanked after 45 days of receiving their gift
          philmodjunk

               You seem to have two interconnected issues:

               Comparing the values in Last Given to LastThanked in each record

               Finding records where LastThanked contains a date that is more than 45 days old.

               I am interpreting that to mean that you want to find all records where LastGiven contains a later date than LastThanked and for which the date in LastThanked is more than 45 days old.

               Step 1:

               Define a calculation field:

               LastThanked < Last Given

               Select Number as the return type. Clear the "do not evaluate if all referenced fields are empty" check box. We'll call it cThankedFlag

               Now you can find all records where LastThanked is empty or contains a date that is earlier than Last Given by entering find mode and specifying a 1 in this field.

               Step 2:

               Use this calculation in a script to find all records where LastThanked is older than 5 days:

               "<" & Get ( CurrentDate ) - 45

               Step 3: combine both criteria in one script:

               Enter FInd Mode[]-->Clear the pause check box
               Set Field [DonorManageMent::cThankedFlag ; 1 ]
               Set Field [DonorManagement::LastThanked ; "<" & Get ( CurrentDate ) - 45 ]
               Set Error Capture [on]
               Perform Find []

          • 2. Re: Creating a list of Donors who need to be thanked after 45 days of receiving their gift
            MartinLange

                 Thank you very much. This has given me what I needed to figure out what I was doing wrong. I've got some refining to do, but you got me un-stuck! I appreciate your help.