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

# Calculation field issue

### Title

Calculation field issue

### Post

Hi,

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?

• ###### 1. Re: Calculation field issue

You don't need these calculation fields to find records that fall in specified ranges of dates.

Are you by any chance on a Partners layout when you perform this find instead of a Donations layout?

See this thread on scripted finds and note how you can specify date range data in a script of this type: Scripted Find Examples

• ###### 2. Re: Calculation field issue

And are these calculation fields stored or unstored? to work correctly, they'd need to be unstored. but searching unstored calculations will be slow.

Better to not use them at all