Non profit budget tracking - multiple donors, split expenses

[Line_Didelot]

Hello community!

I am working in a non profit and I am trying to figure out the best way to track my budget. The issue is that I have income from multiple donors, all of which will give funds for specific expenses. This means that a donor might cover a percentage of my yearly expense. I also use a "transactions" sheet wherein i input all of the financial transactions. My questions is if I am about to pay an expense (for example rent) that is in total 1000euro and 200euro is covered by donor A and 800euro is covered by donor B is there a way to:

1. input one transaction line that splits the total cost according to the allocation mentioned above?

2. at the end of the year I will have to provide all my donors with reports that show the transactions above and they need all this information: total amount paid, total amount covered by donor, line by line transactions relevant to this donor?

I am not sure if this information is enough, in my head it is but if feel free to ask clarifying questions 🙂

thank you!!!

[TheTimeSavingCo]

Hmm, I think you'd need a table where each record represents a single transaction and a single donor

With reference to your example about rent, you'd have:
1. "Transactions" table where you have a record called "Rent" with the cost of 1000
2. "Donors" table where you have two records, one for "Donor A" and one for "Donor B", with the percentage each donor covers
3. A new "Donor X Transactions" table, where you'd now have two records, both of which are linked to the "Rent" record, and one is linked to "Donor A" and one is linked to "Donor B"
  - You'd have a lookup field to retrieve the transaction cost, a lookup field to grab the Donor's percentage cover, and a formula field to calculate the actual amount based on the lookup field data

This would allow you to provide the donors with said reports

There are various ways to populate the data in this table, and one of the ways would be to:
1. Create an automation that triggers whenever a new record gets created in "Transaction"
2. Have a "Find Record" action that'll retrieve all the records in the "Donors" table
3. Have a repeating group, and within it place a "Create Record" action to create one record in the "Donor X Transactions" table per donor, linked to the newly created "Transaction" record

[ScottWorld]

@Line_Didelot 

Yes, the way that you would do this is to setup what's called a many-to-many relationship.

Airtable has a guide on this here: https://support.airtable.com/docs/understanding-linked-record-relationships-in-airtable

If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld