In Manage | Database | relationships, make a new table occurrence of Donation by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be: Donation | SameCustomerYear.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
Donations::_fkContactID = Donation | SameCustomerYear::_fkContactID AND
Donations::cYear = Donation | SameCustomerYear::cYear
your field name for the first match field is probably different from mine so be sure to use the match field that links donations to contacts in place of what I show. cYear would be a calculation defined as: Year ( DonationDate ) and again, you'll need to use your data field in place of mine.
Then you can define this calculation field to compute the YTD total for this Donor for the current year: Sum ( Donation | SameCustomerYear::Grand Total | Donations ) --- I am assuming that Grand Total | Donations is the field that sums your Donation Data entries.
Wow! I'm not exactly sure why your solution works, but it does beautifully! You explained the steps very well, too. Thank you very much.
This is called a "self join" relationship that links two Tutorial: What are Table Occurrences? with the same data source table to each other. Matching by both ContactID and by year means that you match to a related set of records with the same year and same contact ID as the current donation record. As a test, you can put a portal to Donation | SameCustomerYear and you'll be able to see all the records that are being summed to get the YTD total.