Help

Creating multiple linked records based on a formula

Solved
Jump to Solution
343 2
cancel
Showing results for 
Search instead for 
Did you mean: 
CaraSearch
4 - Data Explorer
4 - Data Explorer

Hi! I am working with a nonprofit to get their workspace set up. Make sure you're seated for this one! 😅

The nonprofit wants to see grant funds contributing to individual budget line items broken down by month.

Say they have a grant from the Gates Foundation for $120,000 that must be used between Jan 1, 2023 and Dec 31, 2023. $80,000 of the grant is designated for Staff Member A's salary. $40,000 is designated for Staff Member B's salary. They have a second grant from the Scott Foundation for $6,000 that must be used between March 1, 2023 and May 31, 2023.

The Executive Director wants to see how these grants break out over the months that they are active. For example, that in January, $6,666.67 ($80,000/12 month grant duration) from the Gates grant is applied to Staff Member A's salary. But in March 2023, when the Scott Foundation grant becomes active, $1,000 from the Scott grant and $6,666.67 from the Gates grant are applied to Staff Member A's salary for a total of $7,666.67 in March 2023.

One more thing -- the nonprofit wants to be able to reassign these monthly chunks of funding to different line items as needed while preserving the original allocation data.

Here's what I see as critical pieces of data:

Funding Start and End Date* -- to indicate the number of months and *which* months each grant applies to.
Budget Line Item -- obviously!
Allocation Amount -- also obvious!

*Note that I have already tried calculating the grant duration in months and need more information in order to solve this use case -- e.g. WHICH months the funding applies to.

Here is the sample base with the information I need to create and ideally automate.

I would love to create a button in the "Full Grant Allocation" sheet that expands the allocation into multiple records: one for each month the grant funds with the allocation amount divided by the number of months. I looked at some outside automation options like Make and Zapier and am still stuck on how to do this. Also this forum post was helpful but didn't get me all the way there.

Any help with this is so so appreciated! Thank you.

 

2 Solutions

Accepted Solutions
TheTimeSavingCo
15 - Saturn
15 - Saturn

Hm, that forum post gives you a formula that would generate output the month-year values in between the start and end dates right?  You could then convert that outputted text into a list by using an automation to paste the text into a multiple select field, and then use that multiple select field as a part of a repeating group action to create one record per month-year value

See Solution in Thread

Thank you so much! I am going to try this now and will get back to you.

See Solution in Thread

2 Replies 2
TheTimeSavingCo
15 - Saturn
15 - Saturn

Hm, that forum post gives you a formula that would generate output the month-year values in between the start and end dates right?  You could then convert that outputted text into a list by using an automation to paste the text into a multiple select field, and then use that multiple select field as a part of a repeating group action to create one record per month-year value

Thank you so much! I am going to try this now and will get back to you.