Skip to main content

RollUp? Formula? Linking Info in 2 Tables

  • November 2, 2019
  • 3 replies
  • 50 views
A

I am the Executive Director of a non-profit. We have a team of people who have individual fundraising goals which includes a total amount given by recurring monthly donors.

On table 1 (Individuals) I include the following fields:

  • The name of the person raising funds (primary field)
  • Their individual yearly goal
  • Total number of donors
  • Total dollar amount of donations each month by all their donors
  • Monthly donors (this is a linked field with the 2nd table)

On table 2 (Monthly Donors) I include the following fields

  • The name of the donor (primary field)
  • Monthly Donation Amount
  • Start Date
  • Projected Yearly Amount (A simple formula that multiplies the monthly donation amount times 12)
  • Individual (this is the Individual that brought in the donation and is linked field with table 1)

SO… a commitment for a monthly donation comes through and I enter the information in table 2. I want it to automatically populate table 1 fields after I choose the individual who brought in the donation.

The two fields in the table 1 that I’m having a brain block on are…

  • Total number of donors should match the number of times I selected the individual in table 2.
  • Total dollar amount of each monthly donation is a total of the monthly donation fields for all donations linked to the individual

I hope this makes sense. Thank you so much for any help you can provide.

    3 replies

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • November 2, 2019

    Easy answer: You want to designate {NumberOfDonors} in the [Individual] table as a count field that counts {MonthlyDonors}; {TotalAmount} will be a rollup field that follows the link in {MonthlyDonors} to roll up {Amount} using an aggregation formula of SUM(values). Here’s a quick demo base (open the link and copy the base to your own workspace to be able to examine it):

    Not-so-easy answer: The same as the easy one, except that if by “monthly” you really do mean monthly, it gets more complicated. In that case, you’ll need to implement a method for breaking down donations by month and tallying them separately; you could do this by, say, creating a [MonthlyDonors] table for each month, with separate rollup fields in [Individuals] for each monthly table; alternatively, you could do some fancy calculating in [MonthlyDonors] to indicate for which month[s] a donation should be counted. If you’re trying to keep cumulative tabs on a fundraiser’s total YTD donations, things become even trickier, as you need to track annual donations on a prorated basis. (Since you are tracking ‘projected’ yearly amount, you may not be looking at things with such a degree of granularity…)

    Alternatively, you could just clone the base on the first of each month, delete all the records in [MonthlyDonors], and start over. :winking_face:

    There are relatively straightforward solutions to however you wish to approach this — and whatever ‘this’ is you want to approach — but the details depend on what you need to accomplish.

    A
    Forum|alt.badge.img+3
    • AndrewR
    • New Participant
    • November 3, 2019

    One more tip, in the “Monthly Donors” table, group the records by the “Individual” field. That way you’ll be able to see the sum for monthly donations and annual projections.

      A
      • Amy_Van_Riper
      • Author
      • New Participant
      • November 3, 2019

      Easy answer: You want to designate {NumberOfDonors} in the [Individual] table as a count field that counts {MonthlyDonors}; {TotalAmount} will be a rollup field that follows the link in {MonthlyDonors} to roll up {Amount} using an aggregation formula of SUM(values). Here’s a quick demo base (open the link and copy the base to your own workspace to be able to examine it):

      Not-so-easy answer: The same as the easy one, except that if by “monthly” you really do mean monthly, it gets more complicated. In that case, you’ll need to implement a method for breaking down donations by month and tallying them separately; you could do this by, say, creating a [MonthlyDonors] table for each month, with separate rollup fields in [Individuals] for each monthly table; alternatively, you could do some fancy calculating in [MonthlyDonors] to indicate for which month[s] a donation should be counted. If you’re trying to keep cumulative tabs on a fundraiser’s total YTD donations, things become even trickier, as you need to track annual donations on a prorated basis. (Since you are tracking ‘projected’ yearly amount, you may not be looking at things with such a degree of granularity…)

      Alternatively, you could just clone the base on the first of each month, delete all the records in [MonthlyDonors], and start over. :winking_face:

      There are relatively straightforward solutions to however you wish to approach this — and whatever ‘this’ is you want to approach — but the details depend on what you need to accomplish.


      Thank you! This makes sense. I believe that the “easy answer” is actually the one I need! I so appreciate your time. :slightly_smiling_face: :heart:

      Edited to Add: I finally got a chance to sit down and apply your solution… It worked perfectly!

        Terms & ConditionsAccessibility statement