Primary to Ref View Confusion!

[NATEDOGG]

Hi everyone, could really use your input!

TL;DR: Trying to take the tables in the table's image, and convert them to the corresponding UX!

Context:

I work at a manufacturing company and I've been tasked with developing something that can show: 

The 'status' of all the sub assemblys we have active. The status of any single sub assembly, at any moment, can be: released (prints have been released), waiting (the sub assembly is waiting on parts to arrive), ready (all the parts necessary to build the sub assembly exist at our plant), or completed (the sub assembly has been built!)!

These 4 will serve as the 'primary' views in the application. I have a 'master' sheet that contains all the sub assemblys, regardless of their 'status'. I also have 4 different sheets reflecting the aforementioned statuses. Depending on the sub assemblys status in the 'master' sheet, i have it filter to its respective 'current' status (either released, waiting, ready, or complete). 

With me so far? Lol here's where it's getting a bit tricky...

From any given primary view, if you tap on a specific sub assembly to view details, ideally you would see a list of all the parts needed to assemble the sub assembly. 

For example...Sub Assembly A's current status is "Waiting". So I tap on the "waiting" primary view, and I see "Sub Assembly A". Then I say to myself "Hmm, what parts is Sub Assembly A waiting on?", I then would tap on Sub Assembly A's card, and in the ref view, i'd see a roll up of all the parts needed to complete sub assembly A, and their current statuses! 

What I tried to do: 

Along with the 'master' sheet of sub assemblys I mentioned, I also have a 'master' sheet of parts! It's a 1 to many relationship between sub assemblys and parts. 1 sub assembly, can have many parts. For example, sub assembly A has parts A1, A2, A3. So in my 'master' parts list, I have a column for 'part number', and a column for 'sub assembly number'. Thus, in the aforementioned example, it would look something like this:

Sub Assembly Number | Part Number

Sub Assembly A             | Part A1

Sub Assembly A             | Part A2

Sub Assembly A             | Part A3

In the 'status' primary views, each one has a column containing the Sub Assembly Number. I made the type 'ref' and listed the data source to reference as the 'master' part number sheet, and it didn't work lol

Any ideas on what I'm doing wrong here? Boy if you guys could help me solve this problem I'd be over the moon grateful! Thank you!!

TablesIdeal UX Output

[1minManager]

Ok, I'll have a go at answering this one!  So the issue as i see is:

1. You create a sub assembly
2. Each sub assembly has many parts
3. You want to see which parts are missing, presumably without manually adding each one

I've had this issue in Apps I've built before for customers before and I've come up with 2 different solutions.  The choice really depends on how often the parts can change in a given sub assembly

If the parts almost never changes:

Then set it up so that when you add a new sub assembly and click save, this calls an action that will create all the parts in the subtable with a status of "waiting".  It will reference which parts to create based on your "master' sheet of parts"

Checkout Form Saved - https://support.google.com/appsheet/answer/10108214?hl=en

If the parts can change:

So in this I'll presume you only add parts as they become Ready.  Inside the sub assembly detail view you'll already see a subtable of Ready parts.  But to show the other 'missing' parts you're going to have to create a virtual list column which looks at the "master' sheet of parts" and creates a list of everything else not already added.  You'll need to also make this bit read only.  The downside of this is that you won't be able to easily show a list of all the parts and their status.

This is obviously a very rough outline as to how to do this.  By all means come back on here with more questions.  But bear in mind that both options involve multiple steps each of which you'll have to tackle or ask quesions on seperately.  So if you're completely new to appsheet this could be quite a challenge!

Simon@1minManager.com