0

Running sum balance based on record field sorting rather than record id

Hi 

I do have the following tables 

Table Invoice with the fields date , INV #, description ,amount and client 

Table Receipt with the fields date, Rct # , description, amount and client

I managed successfully to merge both tables data in a single table as to have 1 report showing such transactions. This report is sorted by date. I would like to have a formula where i can have the running sum balance which sum the amount of every record based on what date sorting position is rather than based on its record ID. Any suggestions ?

5 replies

null
    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi

    Would you like this as a number field that permanently holds the running balance (updated periodically), or a formula field that calculates it on the fly.

    Regards John  

    • gkapps
    • 3 yrs ago
    • Reported - view

    Hi John

    from what i understand i just want this running sum to be available when i generate the report and then it will automatically be deleted. 

    thanks 

    • John_Halls
    • 3 yrs ago
    • Reported - view

    let n := 0;
    for i in (select Trans) order by Date do
    n := n + i.Amount;
    i.(Balance := n)
    end

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Trans is your table, Amount is the invoice or receipt amount (if these are both positive you will need to multiply the receipt amount by -1). Balance is a number field. No real need to clear this between reports, just run the script again.

    • gkapps
    • 3 yrs ago
    • Reported - view

    John 

    Thank you so much a brilliant idea and thinking, it worked perfectly.

    I am new working with NINOX and the more I go deep the more I get assured its a solid Data Base app.

Login to reply

Content aside

  • 3 yrs agoLast active
  • 5Replies
  • 437Views