5 Replies Latest reply on Jul 22, 2009 4:58 PM by etripoli

    Relationships, multiple tables.........

    craig5005

      Title

      Relationships, multiple tables.........

      Post

      Sorry... I couldn't think of a better Subject for this thread.

       

      I can't think of a solution to a problem I have encountered, so I hope someone might have an idea.

       

      Basically... I have a table of boxes, and a table of consumable items... we would like to be able to put the consumable items into the boxes.  We have successfully done this with equipment, however the consumable data is different as each item is not it's own record.  The consumable data is organized as each type of item is a record, and we have a quantity field for each (the equipment table was one record for one piece of equipment).

       

      Is there a way to 'put' consumable items into the boxes and list the quantity in the box... and to make it more complicated, some consumable items will be in multiple boxes.  For example, in box 1 we have 4 rolls of tape, in box 2 we have 10 rolls of the same tape (same record in the consumable table).

       

      Any thoughts?

       

      Thanks

      Craig 

        • 1. Re: Relationships, multiple tables.........
          etripoli
             Create an intermediate table, to store the items placed in boxes, and normalize your consumable table, splitting the items out into their own table.
          • 2. Re: Relationships, multiple tables.........
            craig5005
              

            Is it possible to elaborate more on your idea... I don't fully understand how the intermediate table would help and also what you mean by normalize the consumable table.

             

            Thanks

            Craig 

            • 3. Re: Relationships, multiple tables.........
              etripoli
                

              The intermediate table would be between the Boxes and Items table, so that you could assign items to each box - thru a portal for example.  Normalization of the consumable table means splitting out the duplicate fields for the items belonging to a group.

               

              Let's say each group currently contains up to 5 items, so you have 1 record for the group, with 5 fields to store the 5 items (or a repeating field with up to 5 repetitions).  What happens when you need to add a sixth item to a group?  You have to add a new field, or extend the repetition.

               

              Instead, if the Items were in a table separate from their group, 1 item per record, to add a new item just requires making a new record.  Much easier.  Also, it makes it easier to add items to the 'Boxed_Items' intermediate table.

              • 4. Re: Relationships, multiple tables.........
                craig5005
                  

                Let me know if I am on the right track.  I have the 3 tables, Boxes, Consumables and Boxes_Consumables.

                In the join table I have two fields, box and consumable.

                 

                On the box layout I have a portal that displays the consumable field from the join table.  Above the portal I have a button which has the following script.

                 

                Set field [box::gbox; box:box]

                go to layout ["boxes_consumables" (boxes_consumables)

                new record/request

                set field [boxes_consumable::box ; box::gbox]

                 

                *may be errors in the above, but thats just from copying it over.

                 

                On the new layout, the user selects which consumable is included in the box and voila... the portal on the box page is updated.

                 

                However, I also need to track the quantity in the box, and also the quantity we have left.  How would I go about doing this since the quantity in the boxes for one single item could be spread out over many records in the join table.

                 

                Craig 

                • 5. Re: Relationships, multiple tables.........
                  etripoli
                    

                  craig5005 wrote:

                  Let me know if I am on the right track.  I have the 3 tables, Boxes, Consumables and Boxes_Consumables.

                  In the join table I have two fields, box and consumable.

                   

                  On the box layout I have a portal that displays the consumable field from the join table.  Above the portal I have a button which has the following script.

                   

                  Set field [box::gbox; box:box]

                  go to layout ["boxes_consumables" (boxes_consumables)

                  new record/request

                  set field [boxes_consumable::box ; box::gbox]

                   

                  *may be errors in the above, but thats just from copying it over.

                   

                  On the new layout, the user selects which consumable is included in the box and voila... the portal on the box page is updated.

                   

                  However, I also need to track the quantity in the box, and also the quantity we have left.  How would I go about doing this since the quantity in the boxes for one single item could be spread out over many records in the join table.

                   

                  Craig 


                   

                  Add a 'quantity' field to the join table, which you can use to track how many of each item are in the box, as well as calculating how many items per box.  There seems to be a few threads on the board suggesting ways to track inventory quantities, so I won't rehash them here.  

                   

                  One suggestion I have is to make the portal on the Box layout based on the join table, Boxes_Consumable.  Relate it to the Boxes table, by Box id, and 'Allow creation of records...' on the Boxes_Consumable side.  Then, setup a value list, populated from the Consumabls table, to select the consumable.