I currently manage a budget on Airtable.
I have a list of all of my live expenses (In a table called live expenses) which I upload daily. E.g. supplies expense which the matching GL code.
I have another table called actual which I compare to the budget.
I’d like to have all transaction from the live table added in to this table.
For example all of the Sales income be added up and added into the actual Sales income line.
Any insights would be highly appreciated.
You need to link the two tables together. Create a new Link to a record field in [Live Expenses] that links to the [Budget] table. For each record in [Live Expenses], link to the corresponding category in the [Budget].
In the [Budget] table, you will now see that there is a new field with links to the [Live Expenses] table. There may be multiple expenses for some rows.
In the [Budget] table, create a new Rollup field that combines the data for all the related expenses. Have that rollup pick use the field in the [Live Expenses] table that has the expense amount. You will probably want to use the
SUM aggregation formula here.
The [Budget] table should now contain two numbers–the budget number, and the rollup of the live expenses. You can compare them in a formula field.
Keep in mind that this method does not take into account that most budgets are monthly. You will need to find a way to deal with having a budget for each month.