0

Calculation of running balance

I'm trying to transfer a number of databases to Ninox and I'm struggling :-) I've got the date into Ninox but the calculations and scripts are a problem.

I have a database Accounts with a table 'Current 19/20' which has number fields '£Debit' and '£Credit' and a formula field '£Balance'. Each record in the table has either a £Debit or a £Credit entry.

I want £Balance to show the running balance, so I have the formula:

let BalanceToDate := 0;
let FirstRecordID := number(first((select 'Current 19/20').Id));
let CurrentRecordID := number(this.Id);
for Id from FirstRecordID to CurrentRecordID + 1 step 1 do
BalanceToDate := BalanceToDate + '£Credit' - '£Debit';
end;
BalanceToDate

which generates a different value for £Balance in every record - except it seems to be a random number!

I suspect part of the problem might be I don't understand the automatically generated unique Id field.

Each record in the table has a date field but there can be multiple records with the same value in the date field so I can't use that to do the calculation.

I'm wondering if I should try using arrays and doing the calculation on sub-arrays?

Thanks,

5 replies

null
    • Mel_Charles
    • 4 yrs ago
    • Reported - view

    Hi Larry

    There is an excellect video produced by Andy Marks (Nionoxus) on utube that may help you here.

    https://www.youtube.com/watch?v=eaAHmt5hy90

    It deals with bringing bwf balances/dates etc  for a given relationship.

    just might be of use....:-)

    • Larry_Stoter
    • 4 yrs ago
    • Reported - view

    Thanks. That video gives me some more ideas but doesn't quite address my problem.

    The method proposed in the video transfers data from one record to a newly created record. It operates in real time as new records are added.

    I already have lots of existing records and want to do the running balance over all those existing records. Part of the reason I want to be able to do this is that I want to enter future debits and credits into the table but may not know the exact amounts until they happen, I only know the expected approximate amount. I need to be able to go back and enter the exact amounts when I know them and have that revised data entry trigger a recalculation of the running balances.

    • Nick
    • 4 yrs ago
    • Reported - view

    Try something like this

     

    let myID := Id;
    let prev_d := sum((select 'Current 19/20')[Id <= myID].Debit);
    let prev_c := sum((select 'Current 19/20')[Id <= myID].Credit);
    prev_d - prev_c

    • Larry_Stoter
    • 4 yrs ago
    • Reported - view

    Sorry, does much the same as my initial formula - a different value shows in the £Balance field of every record but the values seem random.

    • Larry_Stoter
    • 4 yrs ago
    • Reported - view

    OK, I am begining to understand the problem.

     

    Your calculation and several of the calculations I've tried do work - except, when records are added/deleted or field values changed, not everything updates. Sometimes, hitting the refresh button helps, sometimes it doesn't. I can see sequences of 10, 15, 20 records where the running balance is working correctly then it goes wrong for several records and then starts working again.

     

    I'm wondering is deleted records aren't properly deleted or variables pick up a previous value somewhere?

     

    Puzzled!

Login to reply

Content aside

  • 4 yrs agoLast active
  • 5Replies
  • 1824Views