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?

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • John Halls
    • John_Halls
    • 8 mths 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

    Like 1
      • Alex Mumm
      • Alex_Mumm
      • 8 mths 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.

      Like
Like Follow
  • Status Answered
  • 8 mths agoLast active
  • 2Replies
  • 57Views
  • 2 Following