2 Replies Latest reply on Aug 17, 2011 11:50 AM by WilliamChang

    calculation field that sums by fiscal year



      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.



        • 1. Re: calculation field that sums by fiscal year

          Your calculation field won't work as written due to the fact that any references to your related table of donations like this returns the value of the "first" related donation record.

          Thus Donations::Donation School Year = Current School Year will always compare the value of Current School Year to the Donation School Year value of the first Dontaion record for that donor.

          You can use two different relationships that include different calculation fields, Current School Year, Previous School Year as part of their relationships or you can use a summary field defined in donations that computes the total of Amount, then use two one row portals to donations with different filter expressions to display this summary field to show the two totals that you want. The first approach makes for a more complicated relationship graph, but your fields update smoothly. The second approach simplifies the relationship graph, but requires a script trigger controlled script to refresh the window each time you modify a value on this layout that affects one of the reported totals--which can produce a noticeable "flash" to your window.

          Method 1: Add a calculation field, Previous School Year defined as Current School Year - 1.

          Create two new occurrences of Donations and relate them like this:

          Donor::DonorID = DonationsCurrentYear::DonationID AND
          Donor::Current School Year = DonationsCurrentYear::Donation School Year

          Donor::DonorID = DonationsPrevYear::DonationID AND
          Donor::Previous School Year = DonationsPrevYear::Donation School Year

          Then, Sum ( DonationsCurrentYear::Amount ) will sum the current year donations and Sum ( DonationsPrevYear::Amount ) will sum the previous year donations.

          Method 2:

          Define a summary field, sTotalDonations to compute the total of Amount in the Donations table.

          Add a one row portal to donations on your layout. and add this portal filter expression: Donor::Current School Year = Donations::Donation School Year. Put sTotalDonations as the sole field in this portal. You can change the portal borders to 0 width or a transparent total so that this just looks like a simple field.

          Duplicate this portal and field, but change the portal filter expression to: ( Donor::Current School Year  -  1 )= Donations::Donation School Year

          Then use an OnObjectSave trigger on Donations::Amount in the portal and OnObjectExit on the entire portal to run this script:

          Commit Record
          Refresh Window [Flush cached join results]

          To update the current year totals each time you add/remove/modify the donation amounts.

          • 2. Re: calculation field that sums by fiscal year

            creating the additional table occurrances of the Donations table worked perfectly! PhilModJunk thank you so much!