Avoiding a circular reference
Here's a simplified version of a table I'm working on:
closingBalance = openingBalance + Adding - Subtracting
The problem I'm running into is that the openingBalance of the next month is supposed to equal the closingBalance of the previous month. I can't find a way to do this without getting a circular reference error. Any suggestions?
I had a go at this and, yes, it does seem that Ninox doesn't like that too much. It's not circular, but still...
I changed OpeningBalance to be a number field, and kept ClosingBalance as a formula
OpeningBalance + Adding - Subtracting
I then put this code in the Trigger on update of the table, where Order is a field that the sort order is based on (could be a date, etc)
let a := this.Order; let b := last((select Table1)[Order < a] order by Order).ClosingBalance; for c in (select Table1)[Order >= a] order by Order do c.(OpeningBalance := b); b := c.ClosingBalance end
It finds the record before the updated one, and gets the closing balance, and then updates all the records afterwards. This means that it will remain quick on normal use and keep its integrity if a new record is slotted in at any point.