Welcome to the community, Nikki! :grinning_face_with_big_eyes: That’s definitely possible. The way I’m picturing it, you would have two tables. One could be [Funders], where you track those who fund the grants and any details about them (contact info, website, etc.). The other would be [Grants], and would be an ongoing collection of all grants received. Each grant would link to a record from the [Funders] table, and have entry fields like {Amount}, {Start Date}, and {End Date}, a {Grant Period (Months)} formula field that uses the DATETIME_DIFF function to determine the number of months in the grand period, and an {Amount per Month} formula field that divides the total grant by the calculated number of months.
If the same funder offers multiple grants over time, you could create a view in the [Grants] table that shows only those active grants, in addition to the master list of all grants received since you began tracking them. Also, by linking to funders from the [Funders] table, that table would keep a running list of all grants that each funder offered over time.
Here’s how the [Grants] table might look:
Is that what you’re looking for?
Just to add to @Justin_Barrett’s solution, you could also create a table for months/years:
And back in the grants table, have a link field to this table where you can select the months that this grant will apply to:
Now, back in the months/years table, you can pull through a roll-up of the monthly amount to show your cash flow by month as the grants start and end:
What I’d really like to do is have the link field populate automatically based on the start date and end date (or grant period) but I don’t think this is possible without going outside of Airtable (maybe someone has a trick to do this?)
JB
