0

Account flows

In the Accounts table, I have a view called Flows with the following script. It gives the correct dataset but would like to filter out from that dataset the selected account ledger line items (that is, xAccountId).

in other words, using another analogy, give me all products that a customer also purchased when they purchased Product X but do not list Product X.

"PURPOSE: SELECT ALL TRANSACTION LINE ITEMS WHERE JOURNAL ENTRY NUMBER EQUALS ALL JOURNAL ENTRY NUMBERS IN ACCOUNT";
let xAccountId := number(Id);
let xLedgerId := text(unique(concat((select Ledger)[Accounts.Id = xAccountId].Journal.Id)));
(select Ledger)[contains(xLedgerId, text(Journal.Id))]

52 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    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
      • Fred
      • 1 yr ago
      • Reported - view

      You mean export the results of a view element? Not from the export menu item. Since the view element is just a window to other tables and not really in the current table.

      There are some posts about creating an export button where you would do the same filter you do in your view element and then export that data. Or maybe someone smarter than me can jump in with their export button example.

    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    This would be a perfect example to export the records to a json and inject this data into a dynamic printreport to get a pdf.

    Using the same formula from your view with customized fields after the select.

    e.g.:

    (select Invoices){
           name: FirstName + " " + LastName,
           adres: Street + " " + Number
    }

    see this post : https://forum.ninox.com/t/60yhd2g

Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 52Replies
  • 462Views
  • 4 Following