I have modified a standard customer statement to show outstanding invoices and credits in date order. However on doing this it messed up the "Balance", ie the accumulating total on each line (ie each line added the line's value to the total of the previous lines). The report uses variables AUDIT_SPLIT.FOREIGN_AGED_BALANCE - with a debit function for invoice, credit function for payment. I tried to change the original "Balance" variable, but couldn't get it to work. I currently have AUDIT_SPLIT.FOREIGN_AGED_BALANCE (as before), but its lost the functionality, and so just shows the line value (without adding to previous).
So - this is kind of how it used to look
Date Inv ref Debit Credit Balance
1-1-24 Inv 1 £123 £0 £123
3-1-24 Inv 2 £123 £0 £246
4-1-24 Inv 3 £123 £0 £369
2-1-24 Credit 1 £123 £246
...but I wanted the lines to be in date order, so I changed the "Sorts" to AUDIT_SPLIT.DATE Ascending, and got...
Date Inv ref Debit Credit Balance
1-1-24 Inv 1 £123 £0 £weird value
2-1-24 Credit 1 £123 £weird value
3-1-24 Inv 2 £123 £0 £weird value
4-1-24 Inv 3 £123 £0 £weird value
I tweaked and now have...
Date Inv ref Debit Credit Balance
1-1-24 Inv 1 £123 £0 £123
2-1-24 Credit 1 £123 £-123
3-1-24 Inv 2 £123 £0 £123
4-1-24 Inv 3 £123 £0 £123
but I want...
Date Inv ref Debit Credit Balance
1-1-24 Inv 1 £123 £0 £123
2-1-24 Credit 1 £123 £0
3-1-24 Inv 2 £123 £0 £123
4-1-24 Inv 3 £123 £0 £246
Its such a complicated way of expressing a simple problem, but - can anybody advise please ?
Many thanks