Account flows

Erik Ivan Bech said:
I would like a running total of the account balance on each ledger line item.

You can check out this post.

You can also put this in a formula column in the Ledger view element. This method is dependent on the view element being sort ascending by Journal.Date. If you sort by any other way the column will not make any sense.

let curLedger := this;
let relatedLedger := (select Ledger where Accounts = curLedger.Accounts);
let olderLedger := relatedLedger[Journal.Date < curLedger.Journal.Date];
let sameDateLedger := relatedLedger[Journal.Date = curLedger.Journal.Date];
let final := if count(sameDateLedger) > 1 then
        array(olderLedger, sameDateLedger[number(Id) <= curLedger.number(Id)])
    else
        relatedLedger[Journal.Date <= curLedger.Journal.Date]
    end;
sum(final.DrCr)

When you have a view element you are working from that Table, so we are in the Ledger table even when are physically in the Accounts table.

Line 1: set the variable curLedger to equal the record of the ledger record for each line.

Line 2: finds all Ledger records that have matching Accounts and puts it in a variable.

Line 3: takes the records in line 2 and finds all records that have a Journal.Data that is older than Journal.Date of the current record. Why only the Dates less than, what do you do if you have multiple entries with the same date?

Line 4: takes the records in line 2 and filters for Journal.Date that equal the current record.

Line 5: creates an if statement that checks to see if there are more than one result from line 4. If true then it will create a new array with the results from line 3 and combines it with records in line 4 that have a record Id that is lower than or equal to the current record. If not true then it takes the records form line 2 and filters by Journal.Date that is less than or equal to the current record Journal.Date. Then puts the results into a variable.

Line 10: uses the sum() command to find the total of all DrCr in the found records from line 5.

Ok take a look at the button in Accounts > Ledger tab.

I've added a number field in Ledger called Balance and the button does the math for all related Ledger records.

This can be scripted to set all of the Ledgers, but you should test it out first to make sure it does what is should. Stress test it by adding new records at the end. Or adding records that are back dated, etc.

Erik Ivan Bech said:
How do I filter the records in the button so that Ledger.Fund lookup field value is = “BEB”?

Easiest is: BEB = 2.

let xLedger := ((Ledger where Journal.Fund = 2)order by Journal.Date);

When I have time I can modify the whole script to take into account the different funds.

I would upload a file, but v3.10.10 broke the Export to Ninox function of the MacOS app.

You can put this in a new button in the Accounts table > Ledger tab:

let xFund := unique(Ledger.Journal.Fund);
for loop1 in xFund do
    let loopLedger := (Ledger[Journal.Fund = loop1] order by Journal.Date);
    for loop2 in loopLedger do
        let xLoc := index(loopLedger, loop2);
        if xLoc = 0 then
            loop2.(Balance := loop2.DrCr)
        else
            loop2.(Balance := item(loopLedger, xLoc - 1).Balance + loop2.DrCr)
        end
    end
end

Line 1 creates a unique array of the Funds associated with the Account.

Line 2 starts a loop using the array in line 1.

Line 3 filters the linked Ledger records to only have the Funds in the current loop then orders the results by Date.

Line 4 starts a sub loop using the results from line 3.

Line 5 - 9 are just the same as last time just modified to fit the new loop structure.

You can then group the Ledger tab view element by Fund and see the results.

Then you have to update the Trigger after update of the Amount field in the Ledger table to:

let t := this;
let xLedger := (Accounts.Ledger[Journal.Fund = t.Journal.Fund] order by Journal.Date);
for loop1 in xLedger do
    let xLoc := index(xLedger, loop1);
    if xLoc = 0 then
        loop1.(Balance := loop1.DrCr)
    else
        loop1.(Balance := item(xLedger, xLoc - 1).Balance + loop1.DrCr)
    end
end