AnsweredAssumed Answered

Calculation for "Late" Donors

Question asked by pnomads on Apr 16, 2014
Latest reply on Apr 17, 2014 by pnomads

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

Outcomes