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

    Calculation for "Late" Donors

    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!

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

               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
            pnomads

                 That works great, thank you so much!