I did not have enough energy to read the whole thread…
Here is what I created.
https://www.appsheet.com/portfolio/3401559 called BOM.
The way I did is to add another table called BOM because I did ran into circular references with the original two tables also.
table recipes is a child table to BOM
Products of categories, “F (Finished Goods)” and “WIP”, have an entry in BOM. recipes store non F materials and required quantities.
Though I have a bunch of non VCs defined in the spreadsheet, I have mostly done my work using VCs because of refresh timing.
You are welcome to play with it if it looks like what you are looking for..