I’m an Executive Director for a non-profit who uses airtable as an additional layer for grants and program budgeting.
The primary piece of data/source of truth is our transaction details exported from quickbooks. There are linked tables for many things but specifically I’m looking for support on building out functionality for our Chart of Accounts related to our Programs and Staff tables.
From Transactions Details all transactions are linked to the tables:
-Chart of accounts (i.e. Wages, Taxes, Benefits etc…)
-Programs (i.e. Events, Residencies)
-Staff (i.e. Joe Shmo, Jimmy John)
Specifically to figure out the budget for a program we need to take Staff - within the Staff table, and assign a percentage of time to a program (i.e. Joe Shmo spends 50% of their FTE on Events). Because we have many programs it's easier to sum Wages, Taxes, and Benefits and then multiple them by the relative FTE %.
However, an issue comes into play if two Staff members work different FTE % on a program. So lets say Joe Shmo spends 50% FTE on Events and Jimmy John spends 25% FTE on Events. This is easy enough to sum with a formula in the staff table, but now we want to bring the cost of staff back into the chart of accounts.
So ultimately we’d like a column in Chart of Accounts to reflect - 2024 Events Actuals. But to get those actuals from Staff - Taxes, Wages, Benefits we have to pull in many lookup or roll up fields again, all of which copy the same value over and over. I can’t figure out how to elegantly pull in the respective Staff Actuals from the staff table but back into the correct row in Chart of Accounts.
