2 Replies Latest reply on Sep 16, 2014 6:24 AM by philmodjunk

    Calculation field issue



      Calculation field issue



      I have a database of donors and their donations. There are two related tables in this database that are relevant to this question: Partners and Donations, which are related through the __pkPartnerID and _fkPartnerID fields. Each month there are 70-100 donations made, and we like to run reports to send thank you notes for the donations based on certain criteria. We have monthly, quarterly, annual and one-time donors, and like to send handwritten notes every other month for our consistent donors (monthly, quarterly and annual) and every month for our one-time donors.

      In order to make this happen, the donors are divided into groups: Groups 1 and 2 are made up of all the consistent donors and Group 3 is all the one-time donors. Each month, only one Group of donors are sent thank you notes (Group 1 or 2) plus the one-time donors (Group 3). I have designed a script that performs a find of all donations from all of the donors that need to get a thank you note if they have given within the specified criteria: Monthly donors have given at least one gift in the last 2 months, Annual donors gave a gift in the last year, Quarterly donors gave a gift in the last 3 months, and One-Time givers gave a gift in the last month. 

      Critical to this system is the way that donation dates are calculated. In the Donations table, I have four calculation fields to specify if a gift falls within a certain time period: "OneMonth", "TwoMonths", "ThreeMonths", "LastYear". These calculation fields result in a number. What these fields do is take the current date and subtract 1, 2, 3, or 12 months and evaluate that date versus the date of the gift. For Instance, OneMonth reads like this:

      If (
      Date ( Month (Get ( CurrentDate ))-1; 1; Year (Get ( CurrentDate )))  ≤  Date ( Month (GiftDate); 1; Year (GiftDate)); "1" ; "0")

      "TwoMonths", "ThreeMonths", and "LastYear" are identical except "Date ( Month (Get ( CurrentDate ))- " is -2, -3, and -12 respectively.

      What this allows me to do is perform a find in a script and print out a report of all donations made according to the criteria stated above. This report is based off of the Donations table. When I tested this, I had limited dummy data and it worked flawlessly. Now that I have imported all data, I am getting some inconsistent results. For instance, when I ran the report today and was getting donations from all my Group 2 donors plus Group 3 donors, I should have only seen donations from August 2014 and July 2014 from my monthly donors, but there were donations listed from August 2014, July 2014, May 2014, April 2014, and March 2014. It did not pick up the donations from June 2014 or anything from February 2014 or earlier for monthly or quarterly donors (it did for annual donors).

      So I then performed a find that only searched for all donations in the last month based on the "Donations::OneMonth" field. I got donations from 8/14, 5/14, and 4/14 from this search. I do not understand where this calculation is failing, clearly 08/01/14 is NOT less than or equal to 05/01/14 or 04/01/14 yet the calculation is saying it is! Where am I going wrong here?