3 Replies Latest reply on Jul 8, 2011 5:43 AM by Corné

    Sum of Filtered Portal Records



      Sum of Filtered Portal Records


      Warning: Novice Question:

      My main table is a list of donors, the related table contains a list of donations.  I have a portal on my layout showing the donations, each record in the donations table has "Gift_date" and "Gift_amount" fields.

      I want to calculate and display the total giving between two specified dates: e.g. Total gifts for the last year, the last five years etc.

      It was easy to create a field and calculate the total of all the gifts for a single donor, I just can't figure out how to filter the gift records so I can add up only the gifts received between specified dates for a donor.

      Thanks for the help

        • 1. Re: Sum of Filtered Portal Records

          There are a number of ways to accomplish this, this suggestion is quick, dirty and flexible. This is for viewing only, in the end you may really want to do a sub summary report for printing. 

          You have the two tables, Donors and Donor Gifts, hopefully linked by the Donors::DonorID to DonorGifts::DonorID fields.

           In the Donor table create four new fields, DonorID_g (global), StartDate & EndDate (global date), SumGifts (calculation, Sum( Gift Amount) (number)

           Go to manage Database > Relationships and create new TOs for Donor and Donor Gifts. Create a relationship from Donor::DonorID_g to DonorGifts::DonorID. Then open the relationship box and add  realtionships from Donors::StartDate to Gift Date, and Donors::EndDate to GiftDate. Change the StartDate equijoin (=) to less than equals (≤) and the EndDate equijoin to greater than equals (≥).

           Create a new layout from the new context of Donors, place all four of the new fields on this layout. Create a portal from the new context of Donor Gifts, with the Gift_Date and Gift_Amount fields.

           Go to browse mode and enter data in the DonorID, and StartDate and EndDate fields. You should only see the records that meet the criteria and SumGifts should reflect a total of the records in the portal. You can sweeten the UI with value lists, etc.

          • 2. Re: Sum of Filtered Portal Records

            If you have FileMaker 11 and want to use Portal filtering to filter by date range (Bumper's global start and end date fields would work well for the filter), you can also use a second one row portal and a summary field defined in your Donations table to display the portal total.

            Your relationship could be: Donors::DonorID = Donations::DonorID

            Your portal filter expression could be: Donations::Date ≥ YourTable::gStartDate And Donations::Date ≤ YourTable::gEndDate

            Use that for your donations portal and also for the new one row total donations portal.

            Now all you need to do is define a summary field in Donations that computes the "total of" the donation amounts if you do not already have such a field. Put this summary field as the sole field in your new one row portal and you'll see your donations totals for the current filtered set of portal records. (The new portal's boundaries can be made invisible so that this total looks just like any other field on your layout.)

            Note: The following script is needed to update both portals whenever a date in the global field is changed or when data in the donations portal is edited that would change the total donations:

            Commit Record
            Refresh Window [Flush cached Join results]

            Script triggers can be used on the two global fields and on the donation amount field to perform this script.

            • 3. Re: Sum of Filtered Portal Records

              Great discussion and tips guys! Helped me with my project as well. Thanks a lot!