Calculation for "Late" Donors
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.
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"?
Thanks for any help you can offer!