AnsweredAssumed Answered

calculation field that sums by fiscal year

Question asked by WilliamChang on Aug 16, 2011
Latest reply on Aug 17, 2011 by WilliamChang


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.