7 Replies Latest reply on Aug 12, 2014 8:49 AM by philmodjunk

    Connect to join table with third table

    jetserkok

      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

        • 1. Re: Connect to join table with third table
          philmodjunk
               

                    So for example, the 4 sweaters of size L and 3 of M, could result in two work orders:

               Ideally, that would be two line items, one for size L and one for size M, not one line item record.

               Since you describe creating one or more work orders for a given line item record (your join table), you can add a primary key field to your join table, and link it to an _fk field in your work order table.

          • 2. Re: Connect to join table with third table
            jetserkok

                 Hi PhilModJunk, 

                 Regarding this comment: 

                 

                      So for example, the 4 sweaters of size L and 3 of M, could result in two work orders:

                 Ideally, that would be two line items, one for size L and one for size M, not one line item record.

                 --> in fact, what i wish to do, is automatically separate the lineitems, into several work orders, based on the total price per line item. This might get complicated :) Example:

                 In the lineitems table i have one lineitem with: 1 clothing piece, ordered in size 4xM, 3xL (with corresponding a orderID), total price lineitem is 700$. 

                 Now i wish to automatically create work orders from that lineitem, based on the total price. If total price is >300$, then create extra work order. In this case, that should result in three work orders: 1 with 3xM, and 1 work order with 1xM and 2xL, and the last work order with 1xL.

                 If, like you say that would be ideally from the beginning two lineitems, i would still have to separate them because of the quantity related to sizes. Anyway, perhaps you have some additional advise on how to approach this...  

                  

            • 3. Re: Connect to join table with third table
              philmodjunk

                   But what I am suggesting is that each size specified be a different line item record, resulting in 3 line item records here. This simplifies a number of issues when working with this data.

                   From what I see, that would automatically separate this data into 3 separate work orders. I can't see in your example exactly where/how the total price changes what work orders are produced or what data might be recorded on them.

                   Is it that if the order total is >300$ you need a work order for each item ordered, but if the order total is less than this limit you don't create any work order?

                   Regardless of whether you have one line item record or three in your example, you can still link any work order records you need to that lineitem record by the method described in my initial post. A script can loop through the data and generate the needed work order records and link them to the appropriate line item records. (And when you link the work order to a line item, it is now also linked to the order record for that line item.)

              • 4. Re: Connect to join table with third table
                jetserkok
                     

                Is it that if the order total is >300$ you need a work order for each item ordered, but if the order total is less than this limit you don't create any work order?

                     --> No. If order total (of one design) is <300$, then 1 work order. If >300$, but <600$, then 2 work orders, and so on for every 300$ increasing. 

                     

                From what I see, that would automatically separate this data into 3 separate work orders.

                ->No, because size is not defining for the work orders. If i have one design ordered in 30 M's and 30 L's, then in your example that would be two work orders. But that would be a lot of work for one person (to which i will give the work order). So i want it divided into pieces based on the price, so that the total time to complete is less within my company. 

                Anyway, i created a PK in the lineitems table and connected it to 'Work order' table. So far so good. Now i'll need to work on that script... hopefully i'll get that working (not much experience with scripting)...

                • 5. Re: Connect to join table with third table
                  philmodjunk
                       

                  But that would be a lot of work for one person (to which i will give the work order).

                  It may be that this should be a single work order, but I don't see from here why two work orders for differently sized items is "a lot of work" when compared to a single work order that lists both sizes. It's the same data being communicated to that person to create the same number and type of garments  but in a different document format so I don't see that there would be a large difference.

                  A lot depends one what you want a "work order" to represent and how it best fits your factory operations. But if you choose to set up a work order to list multiple sizes, I'd still use lineitems with one record for each size, but then I'd link a given work order to multiple line items where are all are for the same garment, but a different size.

                  There are a number of reasons for breaking up this data into separate records like that. In particular, it makes it easy to set up a report of items ordered (and of items manufactured) over a specified timespan with statistical results that break down costs and quantities by garment and then by garment size for each such garment.

                  • 6. Re: Connect to join table with third table
                    jetserkok

                         Hey, 

                         Thanks for your quick response.

                         

                    In particular, it makes it easy to set up a report of items ordered (and of items manufactured) over a specified timespan with statistical results that break down costs and quantities by garment and then by garment size for each such garment.

                         I see your point there. This could be interesting. How would i be able to do this: so that there is one record for each size of a garment, in stead of one record for all sizes of each garment? 

                         Regarding this: 

                         

                    It's the same data being communicated to that person to create the same number and type of garments  but in a different document format so I don't see that there would be a large difference.

                         Its data that will be communicated to different people. One work order, will be for one worker. If i have one garment, ordered with 8 M's, and 4 L's, then i want to create more than 2 work orders for that. In fact, i want to make around 3 work orders for that, for 3 different people working at the same time. 
                         And more complicated: if i have one garment ordered with 7 M's and 6 L's, then i would want 4 work orders: 4 M's in 1 order, 3M's + 1 L in another, 4 L's in another and only 1 L in another one. 
                         The logic behind it, is that in my company i don't want a worker to have a work order that exceeds an amount of time, so i can't simply just create 2 work orders of 7 M's and 6 L's. Its confusing probably :) Hope you understand. 
                    • 7. Re: Connect to join table with third table
                      philmodjunk
                           

                                How would i be able to do this: so that there is one record for each size of a garment, in stead of one record for all sizes of each garment?

                           Well, I don't know the actual set up of a line item record now. My best guess is that you have multiple fields, one for the  quantity for each possible size. So if you produce garments with sizes, xs, s, m , L, XL, XXL, you have 6 fields in each line item record where you can enter a quantity.

                           The change that I am suggesting is not one to enter without a great deal of careful thought and planning. You may need to redesign significant parts of your database--including where/how your layout works for specifying the details of a given order. But the changes at the table level are to reduce your group of quantity fields to just one and to add a field for specifying the size so if you got an order using my guess as to sizes where the customer wants garments in all 6 sizes, you'd set up 6 records in your line items table, one for each size where you specfiy the garment, the size and the quantity.

                           

                                The logic behind it, is that in my company i don't want a worker to have a work order that exceeds an amount of time, so i can't simply just create 2 work orders of 7 M's and 6 L's. Its confusing probably :) Hope you understand.

                           Actually, I don't understand, but hey it's your business and you have to use what works for it. To me, you are communicating the same information to the same people whether yo use two work orders, one for each size or one work order with both sizes on the same work order. This can even still be all on the same piece of paper as this is a difference in Format rather than content.