Consolidating data from three different tables into one automatically updating view

User Flow:

  1. Order # , Order Item and Order Date is recorded by Operator 1
  2. QC is performed on the Ordered item by Operator 2.
    —> If QC Check passes, select order number, record transaction and send to Pack and Ship.
    —> If QC Check fails, select order number, record transaction and send order back to warehouse for repair.
    —> After repair, perform QC check again on the ordered item, select order number. record transaction,
  3. Ship date is recorded for each Order number by Operator 3.

I have three tables and am following a traditional Relational Database logic:

  1. ORDERS , which basically just has an order number and an Order date (along with some other order details).
  2. QC CHECK, which is linked to ORDERS table, and where I store PASS or FAIL statuses as well as date of QC check linked to ORDER NUMBER field from ORDERS
  3. SHIPPING, which is linked to ORDERS, and where I store ship date for every order after it has gone through QC.

I have linked all tables correctly, all works well, etc.etc.
Where I am stuck completley, is that I want to be able to somehow automatically generate what is called a VIEW in Relational Database language (or virtual table) that JOINS the records in all three tables using ORDER number (which is correctly linked in every table).

I’ve spent 4 hours trying to figure it out, doing more lookups, rollups , etc., but the thing is, I want the VIEW (Virtual table) to update automatically so that I can then use that view to have a consolidated view of each order , when it was placed, how many QC checks it went through, and when it was shipped.

Any automations would need to be scripted with JavaScript using the scripting block, which you could then trigger via the new automations beta or the button field.

Or, my preferred way of automating things is using Integromat, because it’s easy & no JavaScript is required!