How to compute 'NetRevenue' bar chart block from 'Income' and 'Expenses' tables?

[klonaway]

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 :smiling_face_with_three_hearts: ). 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 !

[klonaway · Accepted solution]

[Replying to my own problem, for someone in need in the future…]
After some research and some thought, the best way in my opinion (as in most cases with relational databases, which is perfectly explained by the AirTable team) is to merge ‘Income’ and ‘Expenses’ tables into a single ‘Ledger’ table.

To achieve that, I had to create a master table ‘Ledger’ with :

• common fields : Date / Amount / PaymentWay (card, cash, etc.)
• a single-select “Type” field, with 2 options : ‘Income’ or ‘Expense’
• fields specific to ‘Income’ records ----> left empty for expenses
• fields specific to ‘Expenses’ records ----> left empty for income

Then, I created two grid views, each one mirroring my initial tables, including the order of fields, to mimic perfectly what I already had.

Finally, COPY/PASTE all records from each table in a single CTRL+C / CTRL+V move. Worked like a charm (thx AirTable).

Once everything is set up, creating a bar chart for net revenue becomes a piece of cake (using a formula field giving +/- sign to $amount depending on Type Income/Expense).

If anyone has a better way to handle income / expense / ledger, I will listen even in a distant future :winking_face: