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
-
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
-
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
-
let n := 0;
for i in (select Trans) order by Date do
n := n + i.Amount;
i.(Balance := n)
end -
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.
-
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.
Content aside
- 3 yrs agoLast active
- 5Replies
- 446Views