I was trying to create a few tables that have entities flow from one table to another, for example, money flows from one account (whose data is held in one table) to another account (and this account’s data is held in another table). Another example would be a purchase order table notes down the material ordered and a DMTR (Daily Material Transaction Report) table holds data about the material received.
I want to create a connection between the rows between two tables. For example, a row holding data of the purchase order for 500 bags of cement in a Purchase Order table should be linked to the row holding 500 bags received in the DMTR table. In my mind (and I have long ago seen a software do it as well), I see tables represented in entity database mapping kind of diagrams with lines connecting two tables, which on closer zoom/clicking would reveal connections/flows of data from one table to another.
The standard method is to of course create a reference to the table in which data is stored along with the row id (foreign key) of the row holding the connected data.
A. Is there a better method than creating foreign key pairing?
B. To do this, I have to be able to use a table name which would be stored in a column as a variable. Right now I have scoured through a lot of posts searching for ways to use table names as variables in lookup/filter formulas, but i see that it can only be used if hard coded. Have I missed a way? I am looking to do something like:
Master Table:
- currentAccountTable
- savingsAccountTable
- purchaseOrderTable
- dmtrTable
currentAccountTable:
- “Payment to contractor abc” “50000” “savingsAccountTable” “row4” “outwards entry”
- “Payment for purchase of Cement” “10000” “purchaseOrderTable” “row1” “outwards entry”
purchaseOrderTable:
- “Purchase Order to ACC Cement” “10000” “500 bags” “dmtrTable” “row1” “outwards entry”
dmtrTable:
- “ACC Cement” “500 bags” “Received from ACC” “purchaseOrderTable” “row1” “inwards entry”
- “ACC Cement” “10 bags” “Given to Contractor ABC” “contractorTable” “row 3” “outwards entry”
There are quite a few challenges that need to be addressed but I will post subsequently my question once I can have this issue resolved one way or another…Right now I am entering all the row ids of the corresponding tables manually (which kind of defeats the point of well… computer science).
My eventual hopes are:
- To enter/make the connections, I can get search, filter, options of two tables side by side and I get to simply select the row id or at the very least the row to be referenced pops up in a choosable select list
- This would probably enter data science realm, but i’d hope that there’d be an ability to find out rows which share data automatically and the references get made by themselves.
- I press a “reconcile” button and all the entries of a table are validated by the down the line connections and “red flags” are raised wherever entry mismatches or lack of flow references are found.
Lol, I know I have put in a big and complicated list of wants in a very simple titled question…hmmm…anyone want to open a company with me? 
