calculation field that sums by fiscal year
I'm working on a donor/donation tracking application for a K-5 school PTA.
I have a donor table and a donation table with a primary key DonorID that is a foreign key to the Donations table (one to many)
In the Donations table, I have the fields DonationDate, DonationAmount, and a calculation filed DonationSchoolYear that is defined as DonationSchoolYear = Year ( Donation Date )-(Month ( Donation Date ) <7)
In the Donors layout, I have a portal field that displays the related Donations records for the current Donor record. Below the Donations portal, I have 3 calculation fields, CurrentSchoolYearSum, PreviousSchoolYearSum, and TotalDonationSum.
The TotalDonationSum is a straight Sum of Donations:DonationAmount and appears to work fine.
The PreviousSchoolYearSum is supposed to be a sum of all donation amounts from the previous school year (August to July)
PreviousSchoolYearSum=If ( Donations::Donation School Year = (Current School Year - 1); Sum ( Donations::Amount ) ; 0 )
where CurrentSchoolYear=Year ( Get ( CurrentDate ) )-(Month ( Get ( CurrentDate ) ) <7)
The CurrentSchoolYearSum=If ( Donations::Donation School Year = Current School Year; Sum ( Donations::Amount ) ; 0 )
The problem that I'm encountering is that is there is more than one related Donations record for a given Donor record, the sums are returning 0 (zero). see first screenshot.
Where are is only one related Donations record the calculations fields work correctly (see second screenshot).
Am I using IF() incorrectly?
Thanks in advance for your assistance.