Heya ! I’m having trouble with this one ! It is not a problem specific to AirTable : it would be the same with any other relational database, and it is an open one…
What I’m aware of : the most consistent solution would be to have a single ‘Ledger’ table containing both income and expenses.
Reason why I would prefer separate tables :
- the ‘Income’ table is deeply related to the already complicated CRM part of my base
- the ‘Expenses’ table is related to the Providers part of my base
- there is no correlation between those parts of the base, and I like it that way ! #K.I.S.S.
- the data in those two tables are of completely different natures
My need : I simply want to compute a monthly net revenue bar chart in a block ! (btw, I’m literally loving the power of AirTable blocks ). Obviously, I would like it entirely automated, and as simple as possible
My thoughts :
- it feels like I need some kind of outer join between the ‘$ExpenseAmount’ and ‘$IncomeAmount’ in a join table to compute the desired bar chart. I can see how I could create a join table and manually link ‘Income’ and ‘Expenses’, but that would defeat the purpose (“Just show me a quick chart of my monthly revenue”) !
- my best idea so far is to build a “MonthlyRecap” table with each record being a month, and formulas to compute total income and expenses (if that is possible…). Yet I feel there is a quicker way…
- [EDIT :] Some kind of automatically populated outer join on the date field could do the trick…
Therefore, I submit the question to the community : how did / would you do it ?
Happy Airtabling !