0

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?

2 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Alex

    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.

    Regards John

      • Alex_Mumm
      • 2 yrs ago
      • Reported - view

      John Halls Thanks again John! I struggled with this for a while, getting unexpected results, until I finally realized that I had typed "Order <=a" by accident. It's all working now.

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 2Replies
  • 115Views
  • 2 Following