Splitting one field between two linked records

#1

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!

0 Likes

#2

Hi @Education_Team - does this work for you?

You have a grants and a programs table, but also a Grant Split table. The grants are divided into one or more splits and this is the Grant Splits table. The programs are then “funded” by the Grant Splits, not the Grants.

The Grants table shows how much the grant has been allocated and how much is remaining. The Programs table can have amounts allocated from different grants.

You might need to do a bit more work to get it exactly how you need it but hopefully this is useful.

JB

0 Likes

#3

Just to add, this allows you to split one grant to multiple programs, but also allocate full grants to a single program - works either way round

0 Likes

#4

Aha, thank you!! I tweaked it a little when I tried it in my base, but that’s exactly what I needed. I appreciate the fresh eyes on the problem! :slightly_smiling_face:

0 Likes