How to create balance table from income and expense tables
Hi.
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?
15 replies
-
Hello,
I have the same question. Need the formula to view total income/expense for each month.
Please advise
-
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
Good luck
-
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?Thanks!
-
Hi,
Income and Expense are the EXACT names of your Income and Expense tables (no links needed).
Like this:
- Income (formula) -> let p := Period; sum((select YourIncomeTable where Period = p).YourAmountField)
- Expenses (formula) -> let p := Period; sum((select YourExpenseTable where Period = p).YourAmountField)
-
-
Worked wonderfully.
Thank you!
-
Hello!
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!
-
Hi Agassi,
take a look in this file
https://www.dropbox.com/s/xm08vpmqb194ec7/zMoney_Test.ninox?dl=0
-
Nick, you are a great man!
You gave a working solution, THANKS a LOT!!!
-
-
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
void
else
let c := (create Balance);
c.(Date := d)
end -
Worked like a charm!
Thank you!
-
Hi Nick
I'm not being able to recreate what you said.
The formula code is giving me an error ( field not found )
-
Hi Bruno Skëndaj
I've created a small database...
https://1drv.ms/u/s!AglD8AFJ3Q28gtwCuzAdh6Hfv3DRwQ?e=1Kgtkw
I hope it helps.
Content aside
- 2 yrs agoLast active
- 15Replies
- 2137Views
-
2
Following