calculation field that sums by fiscal/school year

[williamhchang]

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.

Am I using IF() incorrectly?

Thanks in advance for your assistance.

William

[comment]

Filemaker does not have a SumIf() function, and the expression you are using:

If ( Donations::Donation School Year = (Current School Year - 1); Sum ( Donations::Amount ) ; 0 )

will return either the sum of all related donations or 0, depending on the value of Donation School Year in the first related record.

If this is for display only, you could define a summary field in the Donations table and place it on the Donors layout within a filtered portal.

[williamhchang]

what consultant stated above about the the behavior of the If () function made sense so I tried reversing the functions to:

Sum(If ( Donations::Donation School Year = Current School Year - 1; Donations::Amount;0))

now the behavior is that if there is only one record, the Previous School Year Sum field displays the correct value, but when there is more than on related donation records, the field displays a "?"

thank you in advance for your assistance.

Sincerely, William

[comment]

so I tried reversing the functions

That won't work either. Perhaps I should have been more clear: there is no SumIf() function, and you cannot combine Sum() and If() to create an equivalent. If you had the Advanced version, you could define a recursive custom function to go over the related set and sum up records that meet the criteria. But as I said, there is a simple solution - provided you need this for display only.

[williamhchang]

you were right. reversing the orders of the functions didn't work. what did work was to create another occurrence of the Donations table and join that back to the Donors table with both DonorID and DonationSchoolYear. with that in place I can just use a straight Sum() function on the DonationAmount in the new occurrence of the Donations table.

[comment]

Yes, you can do that - but with 3 years to sum, you will need 3 relationships and 3 calculation fields.