3 Replies Latest reply on Oct 10, 2013 12:01 PM by philmodjunk

    Calculating year-to-date for current year for an individual donor to include on a receipt

    JoRo

      Title

      Calculating year-to-date for current year for an individual donor to include on a receipt

      Post

           I am using FileMaker Pro 12's Starter Solution for Invoices as my model for building the donation and donation receipt section of our database. It is working well, however, I need to include our donors' year-to-date gifts on each receipt they receive. I've checked the forum for calculating year-to-date, but I haven't found one that allows year-to-date based on the current year per individual donor. I have the following tables:

           Contact - information about the individual or donor organization including name, address, etc.

           Donation - this table includes the information for the donation such as receipt number, date, total amount, payment method etc.

           Donation Data - specific line items for the donation. Much like an invoice or regular order, our donors can give to multiple projects in one donation. This table is linked to the Donation table via the Donation ID field.

           The Starter Solution for Invoices includes a calculation that allows a sum total by contact when a portal showing Donations is used from the Contact table. (Screen shot provided.) I redesigned my receipt and based it on the Donation table, using a portal for the donation data. I included the Total Donations | All field on the receipt and (amazingly) it appears to be working. However, I need some help limiting the calculation to the current year. I'm assuming you use a get year function, but I don't know how or in which order to add it to the calculation.

           Any suggestions offered will be very much appreciated!

      Screen_shot_2013-10-10_at_9.08.15_AM.png

        • 1. Re: Calculating year-to-date for current year for an individual donor to include on a receipt
          philmodjunk

               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.

          • 2. Re: Calculating year-to-date for current year for an individual donor to include on a receipt
            JoRo

                 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.

            • 3. Re: Calculating year-to-date for current year for an individual donor to include on a receipt
              philmodjunk

                   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.