Making Table Names usable as a Variable Value?

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:

  1. currentAccountTable
  2. savingsAccountTable
  3. purchaseOrderTable
  4. dmtrTable

currentAccountTable:

  1. “Payment to contractor abc” “50000” “savingsAccountTable” “row4” “outwards entry”
  2. “Payment for purchase of Cement” “10000” “purchaseOrderTable” “row1” “outwards entry”

purchaseOrderTable:

  1. “Purchase Order to ACC Cement” “10000” “500 bags” “dmtrTable” “row1” “outwards entry”

dmtrTable:

  1. “ACC Cement” “500 bags” “Received from ACC” “purchaseOrderTable” “row1” “inwards entry”
  2. “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:

  1. 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
  2. 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.
  3. 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? :stuck_out_tongue::stuck_out_tongue:

Dear @Apurv_Mittal, welcome to the community! :handshake:

I would say that building your wiki/doc should be possible, but the schema logic needs to be the right fit for your needs and depending on your expectations.

Would it be possible to share an dummy doc with some sample info and expected outcome (of course manually entered) to get a better understanding what’s about?

This will make it more easy to create a sample solution with the info provided, but please have in mind that the development will be most likely in steps!

As an example, I received support on a situation that I didn’t find myself a solution"

It’s just a small part of some much bigger project I am developing :building_construction:

Hi!
Sorry for the delay in my reply. I was wondering how to mask the financial information in the tables, and then went ahead and simply put in some dummy data.
Please take a look:

Additionally, over the last few days, I have found that Google Charts (and others) have the ability to model data flow in terms of “Sankey” diagrams. Check it out if you’d ever need to model flow of data like I have been trying here :slight_smile:

Hi Apurv,

Welcome to Coda and to the community!

I am a big and growing fan of Coda, but sometimes some things are just done better in a different tool.

I have requested access to your doc - but in the meantime some comments: In this instance I would recommend that you use an accounting tool. I am currently working with a friend for whom I wanted to do some things in Coda. I am now busy implementing Quickbooks for her, and will then use that as a base for accounting, and inventory management, information.

Once that is in place, I will import the information that is needed in Coda from Quickbooks using Zapier.

That allows me to focus on adding value, developing specific things that is not available elsewhere.

Having said that, before I found there is a Zapier between Quickbooks and Coda, I did try some ideas for your scenario in this document. I stopped at the stage where I needed to add two GR’s together to match to an invoice.

Regards
Piet

Hi! Thanks so much for your reply! I don’t see a document access request :frowning:
I have shared the link to the demo doc I had made in the link above…i’ll try sharing this here again…i have it essentially made it a public doc…

+1 to TableNames or TableIDs being accessible in formulas!

1 Like