I am creating a "Product Manufacturing Database". I have 4 main tables plus a 5th that is a "2nd occurrence" of one table, and 4 "Join" tables between them all. The tables are:
Join Feedstock to Phase1
Phase 1 Production
Join Phase1 to Products
Join Products to Phase 2
Phase 2 Production
Join Phase 2 to Products
Products (Occurrence 2)
Basically, to use the database you create new feedstock records.
Then you create a Phase 1 record and "assign" feedstock records to it.
After Phase 1 is complete, output Product records are created and stored in the Products table (through a portal on the Phase 1 layout)
Then, Phase 2 Production occurs, with Products created in Phases 1 and 2 assigned as its feedstock.
Phase 2 results in additional products that are recorded as output Product records in the second occurrence of the Products table.
What I want is a Product summary page that shows me in one list, all of the products created in Phase 1 and Phase 2, and I want them both to show the original feedstock ID, phase 1 production ID, and phase 2 production ID (if applicable).
I have created a layout that is based on the second occurrence of the Products table. It shows all of the Products, created in either Phase 1 or 2. But the other fields are incomplete. It doesn't show the feedstock ID, phase 1 ID, or phase 2 ID for the products produced in Phase 1. It does show them for products created in Phase 2. When I switch the layout to the Products occurrence 1 table, I can only see products from Phase 1 and the feedstock ID, phase 1 ID are there.
I seem to be so close yet so far away. Any help would be greatly appreciated!