0

# 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?

15replies Oldest first
• Oldest first
• Popular
• Elena
• Rhoa Homes
• Elena
• 2 yrs ago
• Reported - view

Hello,

I have the same question. Need the formula to view total income/expense for each month.

Like
• Nick
• Nick
• 2 yrs ago
• Reported - view

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

Like
• DBS
• DBS
• 2 yrs ago
• Reported - view

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!

Like
• Nick
• Nick
• 2 yrs ago
• Reported - view

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)

Like
• Nick
• Nick
• 2 yrs ago
• Reported - view

Like
• DBS
• DBS
• 2 yrs ago
• Reported - view

Worked wonderfully.

Thank you!

Like
• Agassi
• Agassi
• 2 yrs ago
• Reported - view

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!

Like
• Nick
• Nick
• 2 yrs ago
• Reported - view
Like
• Agassi
• Agassi
• 2 yrs ago
• Reported - view

Nick, you are a great man!

You gave a working solution, THANKS a LOT!!!

Like
• Nick
• Nick
• 2 yrs ago
• Reported - view

Like
• DBS
• DBS
• 2 yrs ago
• Reported - view

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.

Like
• Nick
• Nick
• 2 yrs ago
• Reported - view

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

Like
• DBS
• DBS
• 2 yrs ago
• Reported - view

Worked like a charm!

Thank you!

Like
• Hi Nick

I'm not being able to recreate what you said.

The formula code is giving me an error ( field not found )

Like
• Nick
• Nick
• 7 mths ago
• Reported - view

Hi Bruno Skëndaj

I've created a small database...