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
  • Newest first
  • Active threads
  • 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.

    Please advise

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

    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

    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

    Screen Shot 2020-04-15 at 20.21.44

    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
    • 2 mths ago
    • Reported - view

    Hi Bruno Skëndaj

     

    I've created a small database...

    https://1drv.ms/u/s!AglD8AFJ3Q28gtwCuzAdh6Hfv3DRwQ?e=1Kgtkw

    I hope it helps.

    Like
Like Follow
  • 2 mths agoLast active
  • 15Replies
  • 2067Views
  • 2 Following