# RollUp? Formula? Linking Info in 2 Tables

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.

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.

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.

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.

1 Like

Thank you! This makes sense. I believe that the âeasy answerâ is actually the one I need! I so appreciate your time.

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

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.