1 Reply Latest reply on May 17, 2016 1:19 AM by erolst

    Table Occurence and Related Data


      Hi everyone,

      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!



        • 1. Re: Table Occurence and Related Data

          I would probably use a single Phase table, looking like this:


          Feedstock --< FeedstockInPhase >-- Phase (1/2) --< Product


          and I don't think you need a join table between Phase(s) and Product – if every product was created as the result of exactly one phase, then that phase is simply an attribute (foreign key) of the product.


          A report based on that Product layout should be able to show the phase it was created in, plus all its feedstocks (you wrote “the original feedstock ID”, but that contradicts the data model you described). The FeedstockInPhase join table is also a candidate for your report layout; it depends on the desired level of granularity.