How to create balance table from income and expense tables
I have two tables:
1. Income table - consisting of several fields and a formula named "total income"
2. Expense table - with "expense amount" field.
How can I create a balance table that will show monthly total income, expenses, and balance?
Is it possible to do this?
Add a formula field to your tables: yearmonth(Date)
let's called Period. Example Date 30/3/2020 gives -> 2020/03.
Create another table e.g. Balance with 4 fields:
- Period (text)
- Income (formula) -> let p := Period; sum((select Income where Period = p).Amount)
- Expenses (formula) -> let p := Period; sum((select Expense where Period = p).Amount)
- Balance (formula) -> Income - Expense
Thank you for the reply Nick.
I'm having a hard time understanding the formula..
When I enter it exactly as you've give me, I get "Table not found: Income at line 1, column 41"
Should I link from other tables first? I've been trying that and other various things but to no avail.
Could you kindly elaborate on what I should do to make the formula work?
Income and Expense are the EXACT names of your Income and Expense tables (no links needed).
- Income (formula) -> let p := Period; sum((select YourIncomeTable where Period = p).YourAmountField)
- Expenses (formula) -> let p := Period; sum((select YourExpenseTable where Period = p).YourAmountField)
I also was looking for such a solution, so would like to Thank Nick for the answer!!
Is it posible to populate automaticaly the table Balance with data from each month?
Because now i have to manually put the Period (2020/02 for example) to get the Income and Expenses of that month? It would be great to see the list of all months!
Agassi and Nick,
I've been trying to make this happen with a different approach.
When I enter a new record with a date on the Income table, I wanted the Trigger after update function to search the Balance table for a record for the matching yearmonth(Date).
If there is a match, it won't do anything. If there is no match, it would create a new record on the Balance table a new record for the month.
I think the logic is sound, but I can't figure out how to actually make the formula for the Trigger after update..
Would this be possible?
Thank you so much for the help.
Well, I don't have time to test right now, but I think it's OK.
Put this code in the Trigger After Update box of the Amount field (tables Income and Expenses of my example db):
let d := Date;
let p := yearmonth(Date);
let u := first((select Balance)[Period = p]);
if u then
let c := (create Balance);
c.(Date := d)