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!

[JonathanBowen]

Hi @Education_Team - does this work for you?

Airtable

Grants - Airtable

Explore the "Grants" base on Airtable.

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

[JonathanBowen]

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

[Education_Team]

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:

[Brite_Admin]

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?

[Justin_Barrett]

Tagging @JonathanBowen in case he’s not getting updates about this thread.

[JonathanBowen]

Thanks @Justin_Barrett, back from holidays :slightly_smiling_face:

[JonathanBowen]

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

[Brite_Admin]

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.

[Justin_Barrett]

This sounds very similar to a conversation I’m having in this thread:

SendGrid to multiple related contacts problem with fields in email body Apps

I’m new to Airtable and SendGrid. I have a contacts table with a first name, last name & email address fields. I have a linked events table linked to the contact name with multiple select so that one event can be associated with multiple contacts. I want to send email through SendGrid to every contact related to the event. I did a lookup of the contacts email address in the Events table and it does send separate emails to each person because the lookup field separates the email addresses with …

Is @Brite_Admin the same person as @Dean_Moray?