Splitting one field between two linked records

[Education_Team]

Hello all! I am in need of some brainstorming.

My organization uses grant funds for some of our programs and I am trying to figure out the best way to keep track of how much we have left and which programs they are linked to. In order to do that I have one table with all of our program information and one table with the information about the grant, i.e. how much we received. For most of the programs we only use funds from one grant, so right now what I have is on the program records table a field for how much grant money was used, then I link it to a specific grant and in the grant table a rollup and then formula field calculate how much of the money is left. However, on occasion we do pull funds from two grants for one program. This creates an issue as then both grants are pulling the same number from the program records table and subtracting the full amount from both totals, rather than the partial.

I considered having two used funds fields in program records, but then I realized I didn’t know how to make the rollup select the correct field to pull from. I could also have multiple funds used fields in the grant table and manually enter the numbers but then some of the records would have unnecessary blank fields if the grant doesn’t cover as many programs as another one does.

Any suggestions are appreciated!

[Brite_Admin]

Yes, sorry. I didn’t realize I had accounts in two names and posted in different places under different names.

[Tierney_Smith]

I’m also using airtable to allocate various purchases to different grant lines. In my case, each year we have $800k(ish) in purchases that get distributed between 45(or so) funding sources. Each of the funding sources have different amounts, so sometimes a purchase is split between several funding sources to “use up” their allocation.

My current table has a purchases tab and a sources tab, with a junction to link the two. I use the junction to help me populate the fiscal reports that get sent to each funder. In my example below, you can see the linked field for each of the items that are getting attributed to Amherst, the total cost of that item (as a lookup) and the actual amount of the item that will end up on that grant source, which I’ve entered as a text field so that the individual amounts are listed correctly on the reports. Is there a function that could do a sum of those values? Or, any other ideas on how I can achieve the same result?

I’m also wondering if either the OP @Education_Team or @JonathanBowen could share an updated version of the tables they are referencing here? Might be helpful for additional inspiration!

[Justin_Barrett]

Tierney_Smith:

Is there a function that could do a sum of those values?

Unfortunately not. The function would need to split your text into an array (a collection of items), and there currently isn’t a function that does this.

Would it work to add a field to the table from which the original values are being looked up, and enter the actual amount into that field? That way you could use a rollup field to collect and sum all of those values easily, with a similar lookup field if you need to see them individually in a similar fashion as the original values.

If that won’t work, you could use a button-triggered script to pull all of the values, add them, and put the result into another field.

[Mo_Rheeds]

I would love to see this solution. Any chance I could get access to this example?

[Mo_Rheeds]

Sorry @JonathanBowen