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
-
Take a look at this resonse. Some very helpful tips on how to manipulate arrays.
-
We can help build the code.
Question: This line:
let xLedgerId := text(unique(concat((select Ledger)[Accounts.Id = xAccountId].Journal.Id)));You want to find all related Journal records to a particular Account record. They are related through the Ledger table. You can try writing the code:
let xLedgerId := text(concat(unique(Ledger.Journal.Id)));This way uses the power of relationships and stays away from unnecessary select statements.
Now I don't understand what you want to do here:
(select Ledger)[contains(xLedgerId, text(Journal.Id))]Aren't you just finding the same records that you have in xLedgerId?
-
Ok, now spending a few more moments looking at things, I think I understand this:
let xAccountId := number(Id); let xLedgerId := text(unique(concat((select Ledger)[Accounts.Id = xAccountId].Journal.Id))); (select Ledger)[contains(xLedgerId, text(Journal.Id))]So line 2 finds all related Journal records to the current Account record.
Then in line 3 you want to find all Ledger records that have the same related Journal records but minus the Journal records found in line 2.
You can try, I'm thinking off the top of my head so I can't say for sure will work:
let xAccLedger := Ledger; let xAccJournal := xAccLedger.Journal; let journalrelatedLedger := (select Ledger [var j := Journal; xAccJournal[=j]]; journalrelatedLedger[var t := this; not xAccLedger[=t]]I like to work with base records so I've changed the code to remove reference to Id.
Line 1 gets all related Ledger records to the current record. This is returned as an array of rid (record Ids).
Line 2 gets all related Journal records. This is returned as an array of rid (record Ids).
Line 3 finds all Ledger records that have the same Journal records. This will find records that would match line 1.
Line 4 is where we take the second array (of Ledger records) and subtract the first array (of related Ledger records to the current record) and we are left with records that have the same related Journal records to the current record but subtracts Ledger records related to the current record in Accounts.
I hope it works. If it doesn't can you upload a sample DB so I can test things out?
-
What about:
let myact := this; Ledger.Journal.Ledger[Accounts != myact]-
Thank you very much indeed!
-
What would that script look like if instead of the Accounts table Flows view it’s the Financials table Flows view? That is, give me ledger line items related to the selected Financials record where related Financials record is not the selected Financials record? In other words, it’s the same as your script except it’s one relationship hop further away?
-
Would this give the desired result?
let myfin := this; Accounts.Ledger.Journal.Ledger[Accounts.Financials != myfin] -
amazing! Finally, if it was in the Contacts table Flows view would it be:
let myact := this;
Journal.Ledger[Journal.Contacts != myact]It’s not working.
-
"Journal.Ledger" returns all the records in table "Ledger" that refers to a record in the table "Journal", which in turn refers to the current record in table "Contacts". If you add a condition that excludes the records in table "Ledger" that refers to a record in the table "Journal", which in turn refers to the current record in table "Contacts", you will of course always get an empty set. I don’t understand which set of records you want to see.
-
Of course! I removed the flows view of the contacts table since the ledger view already gives all transaction line items for the selected contact. Thank you very much indeed!
-
I have tested the above script and it’s not working always as intended. Images below show that the account balance in Ledger view is eventually zero, but was expecting a zero balance also in the Flows view but 160.18 remains. The cause is the related line items in journal entry 81238 in Journal view is not flowing properly in the Flows view
-
So the actual data are such that the same ledger can sometimes be viewed more than once. We need to forbid this:
let myact := this; unique(Ledger.Journal.Ledger[Accounts != myact]) -
It works! Thank you very much indeed!
-
do I have the script correct when running the flows view in the Financials table? The Prepaid Expenses, for example, has different totals between its Ledger and Flows views, when the total Ledger view of 1,297.32 should also be in Flows, not 648.66
-
Your dabase is (moderately) complex, and seems to contain loads of data. So it is difficult to understand the cause of a subtle problem with just a few screen captures. Can you produce a nearly empty copy of your database, with just the minimum amount of (fake) data to activate the issue?
-
No need. It turns out I misspoke. Thank you very much indeed! I figured out what happened. Your script is working. The ledger view was incorrect - pulling in records twice! The script in the Financials Ledger view was “Accounts.Financials.Accounts.Ledger” instead of “Accounts.Ledger”
-
-
said:
Not working yet.Can you write out an example using the records in your DB?
When I use Alain's example in your view in the Flow tab of the Groceries record (Account Id = 731) it seems to work for me.
Content aside
- Status Answered
- 4 mths agoLast active
- 52Replies
- 324Views
-
4
Following