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!