AnsweredAssumed Answered

Connect to join table with third table

Question asked by jetserkok on Aug 11, 2014
Latest reply on Aug 12, 2014 by philmodjunk

Title

Connect to join table with third table

Post

     Hi, 

     I have a clothing company and are creating a filemaker database for it. For simplicity, lets say i have these tables:

     1. Products (1 product can be part of multiple orders, 1 order can have multiple products): with clothing items

     2. ClientOrders (1 product can be part of multiple orders, 1 order can have multiple products)

     3. Product-ClientOrders-Lineitems (joining the above two)

     In the third table i now have all the information stored of a specific client order. So when a client has made its order i have in there: fk order id, fk product id and a certain quantity ordered of a certain size of that product. For example, one row in that table could be:

     1 product id: 001 with 1 ClientOrder id: 001, with 4 sweaters of size L and 3 of size M. 

     Now i wish to use that information to create 'work orders' for my employees in my production company. One row in the joined table could result in one or more work orders. So for example, the 4 sweaters of size L and 3 of M, could result in two work orders: 

     work order 1: 1 work order id: 001, 1 product id: 001, with 1 clientOrder id: 001, with 4 sweaters of size L

     work order 2: 1 work order id: 002, 1 product id: 001, with 1 clientOrder id: 001, with 3 sweaters of size M

     But that means i have to connect this fourth table 'work orders' to the joined table (Product-ClientOrders-Lineitems), having a one to more relationship. But the joined table has two foreign keys, which i both need. Can i connect this joined table to this fourth table and how would i do this, not losing any information? 

     Thanks for your help, much appreciated. I added a screenshot of my tables. 

      

Screen_Shot_2014-08-11_at_09.47.00.jpg

Outcomes