2 Replies Latest reply on Apr 17, 2014 10:56 AM by pnomads

# Calculation for "Late" Donors

### Title

Calculation for "Late" Donors

### Post

I'm building a database of donors and one problem that I'm running into is defining a calculation for a field that will show whether the donor is current on their donations or if they are delinquent. For this calculation, I'm using two related tables: Donors and Donations. I would like for this field to display "Current" or "Late" on all of my donor records. Here is the criterion for this field, I just need a point in the right direction in implementing it.

Case(
DonorFrequency = "Monthly" and Donations::DonationDate not in the last 31 days ; "Late" ;

DonorFrequency = "Annual" and Donations::DonationDate not in the last 365 days and Donations::DonationAmount < AnnualDonationAmount ; "Late" ;

DonorFrequency = "Quarterly" and Donations::DonationDate not in the last 91 days and Donations::DonationAmount < QuarterlyDonationAmount  "Late" ; "Current" )

Where I'm running into trouble is getting the latest donation record for a given donor. To test and see if the Donations::DonationDate field displays the latest donation record for a donor, I placed it on a form layout from the Donors Table. It shows the date of the very first donation for a given partner. Is there a way to have the calculation find the last donation date rather than the first?

Also, what would be the best way to say "Donations::DonationDate not in the last 'X' days"?

• ###### 1. Re: Calculation for "Late" Donors

Presumably you have this relationship between Donors and Donations:

Donors-----<Donations
Donors::__pkDonorID = Donations::_fkDonorID  (though your field names may be different from mine.)

and you are defining a calculation field in donors to show whether the donor is or is not late for their next donation.

That matches what you have described where referring to a field in donations from the context of Donors refers to the first related donation record.

Case(
DonorFrequency = "Monthly" and ( Get ( CurrentDate ) - Last ( Donations::DonationDate ) > 31 ) ; "Late" ;
The other cases will be the same except for the number of days used in the calculation.
• ###### 2. Re: Calculation for "Late" Donors

That works great, thank you so much!