REF_ROWS() - Strange child parent crosstalk or Many to Many references

[Tom_Stevens]

I have 3 tables.

Tracking Number
Invoice
Purchase

Tracking Number - Generates a unique number (I understand all of the issues with sequentail numbers) and is my “Master Table”, the one ring to rule them all.
Column structure is as follows:

Invoice - It’s pretty much what it sounds like. It references back to “Tracking Number” and does some math
Column structure is as follows:

Purchase - It records purchase data. It references back to “Tracking Number”, “Invoice” and does some math.
Column structure is as follows:

---

Here’s what I am looking to achieve. I would like to be able to have the users go into an invoice hit save and have a basic frame work. From there I would like users to be able go back into the invoice (easy and done).

I would like to be able to have both a “Related PURCHASEs”, but also I would like to be able to have a “All Purchases with Related TRACKING NUMBER”.

My plan is that they could use the “All Purchases with Related TRACKING NUMBER” to click on an item on that list and open each item that they actually used. Then update the “INVOICE KEY ID” for that item to reflect the invoice that it should appear on. Then when the user hits save it pops them back to the “Invoice” they were in. The “Related PURCHASEs” would then list what was actually used for that invoice

I hope that makes sense.

I have been fiddling around all morning with virtual columns and Select() / Filter() statements. I can’t get anything to provide links like REF_ROWS() does.

[Rafael_ANEIC-PY]

Hello @Tom_Stevens

Tom_Stevens:

Tracking Number - Generates a unique number (I understand all of the issues with sequentail numbers)

Tom_Stevens:

I have been fiddling around all morning with virtual columns and Select() / Filter() statements. I can’t get anything to provide links like REF_ROWS() does.

Why don’t you just use a REF_ROWS() in a virtual column then?

[Tom_Stevens · Accepted solution]

I embrace the darkness of sequential numbers, was born in it! (not an issue at all, and my app(s) have multiple users)

But back to the actual issue…

I think I have found my solution: School Model - AppSheet

From this thread: Help with many-to-many reference and bridge table - #19 by TyAlevizos

Basically it involves having a 4th sheet to act as a master library.

I am just in the process of testing it for my own needs, but it looks very promising.

[Tom_Stevens]

It works!