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.
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.
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
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
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!
Im a relative NOOB but I need something similar to split a related contacts field that can have more than 1 contact name into separate records so I can use SendGrid to send to them separately. Will your method above do this? If so, I don’t understand the formulas (or methodology) in the Grant split table that splits the 5 records in the Grants table into the 7 records in the grants split table?
Tagging @JonathanBowen in case he’s not getting updates about this thread.
Tagging @JonathanBowen in case he’s not getting updates about this thread.
Im a relative NOOB but I need something similar to split a related contacts field that can have more than 1 contact name into separate records so I can use SendGrid to send to them separately. Will your method above do this? If so, I don’t understand the formulas (or methodology) in the Grant split table that splits the 5 records in the Grants table into the 7 records in the grants split table?
Hi @Brite_Admin - the grants in my example base aren’t split automatically if that’s what you’re asking. My example just allows you to manually split a single grant into “sub-grants” and apply them to different programs, showing you how much of the original grant is still left to allocate. What you are after sounds like something completely different.
Can you share some more detail about how your base is structured? I’m sure there is an answer to your problem, but without knowing where you are starting from it is hard to suggest a good way forward.
JB
I really appreciate your willingness to engage.
I have about 10 related tables but the only part relevant here is that I have an Events table where we calendar events. I have a contacts table that is related and I can choose more than one related contact for an Event. Contacts can also be in related to more than one event.
My goal is to use the SendGrid block to send a separate reminder email to each related contact to an event 7 days before the event.
The method I tried was to do a lookup of the related contacts email address into the Events tab When I tried to use SendGrid, it DID create separate emails to each contact (I believe it did this because the lookup email addresses come in separated by commas which SendGrid interprets to send separate emails. So far so good!
However, when I add any other fields (like the contact’s first name) into the body of the email template, SendGrid puts ALL related contacts First name into BOTH emails which is not what I need. I realize this is the expected result but doesn’t do what I need.
I could create a junction table that could accomplish this but we have LOTS of events each month and the labor involved in manually creating a record in the junction table and choosing an event and then choosing a contact for each record in the junction table make this not feasible so I am looking for some other way.
If you have any ideas, it would be greatly appreciated.
I really appreciate your willingness to engage.
I have about 10 related tables but the only part relevant here is that I have an Events table where we calendar events. I have a contacts table that is related and I can choose more than one related contact for an Event. Contacts can also be in related to more than one event.
My goal is to use the SendGrid block to send a separate reminder email to each related contact to an event 7 days before the event.
The method I tried was to do a lookup of the related contacts email address into the Events tab When I tried to use SendGrid, it DID create separate emails to each contact (I believe it did this because the lookup email addresses come in separated by commas which SendGrid interprets to send separate emails. So far so good!
However, when I add any other fields (like the contact’s first name) into the body of the email template, SendGrid puts ALL related contacts First name into BOTH emails which is not what I need. I realize this is the expected result but doesn’t do what I need.
I could create a junction table that could accomplish this but we have LOTS of events each month and the labor involved in manually creating a record in the junction table and choosing an event and then choosing a contact for each record in the junction table make this not feasible so I am looking for some other way.
If you have any ideas, it would be greatly appreciated.
This sounds very similar to a conversation I’m having in this thread:
Yes, sorry. I didn’t realize I had accounts in two names and posted in different places under different names.
Hi @Brite_Admin - the grants in my example base aren’t split automatically if that’s what you’re asking. My example just allows you to manually split a single grant into “sub-grants” and apply them to different programs, showing you how much of the original grant is still left to allocate. What you are after sounds like something completely different.
Can you share some more detail about how your base is structured? I’m sure there is an answer to your problem, but without knowing where you are starting from it is hard to suggest a good way forward.
JB
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!
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!
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.
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
I would love to see this solution. Any chance I could get access to this example?
I would love to see this solution. Any chance I could get access to this example?
