4 Replies Latest reply on Oct 14, 2013 3:03 PM by philmodjunk

    Keeping track of Items and their parts...



      Keeping track of Items and their parts...


           I work in film/television and I'm trying to keep track of equipment that is on our TV show.   When I get I light, for instance, it comes with all sorts of parts.   I could receive a various parts at various times, so my goal is to create a Transactions table that will pull equipment names from my "Equipment" table. 

           Using the picture as an example, I would create a "10K Baby Tener" record.  I'm sure I would need to create an individual record for "DTY Globe 10,000W" and for all its various parts.   

           My question is, when I add a "10K Baby Tener" to a new transaction, how do I pull up the 10K's related records, similar to the picture...




        • 1. Re: Keeping track of Items and their parts...

               So if you check out  the item shown in bold face all the other items listed below list individual components of that item and should also be checked out at the same time?

               And if you want 3 of this item, the number of components for each item have to be the number of components needed for one such item times the total number specified for the main item?

               This is another version of what retailers deal with when they sell "kits" or "special packages" made up of items that are individually listed in their product inventory. A self join many to many relationship with a looping script can be used so that when you select the "package" or "kit" that item plus all the individual components are listed in the LineItems "Transaction" table.

               That would be based on relationships similar to these:


               Show::__pkShowID = EquipmentList::_fkShowID
               Inventory::__pkInventoryID = EquipmentList::_fkInventoryID
               Inventory::__pkInventoryID = InventoryComponents::_fkKitInventoryID
               ComponentInventory::__pkInventoryID = InventoryComponents::_fkCompInventoryID

               Inventory and ComponentInventory would be two table occurrences with the same data source table. InventoryComponents lists all the components and quantities that make up a specific "kit" in the inventory table.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               You'd implement this by using a script such that when you select an Inventory item in the portal to EquipmentList a script trigger performs a script that checks INventoryComponents for any related records to determine if the item represents a kit. If it is a kit, it loops through the related set of InventoryComponent records adding a record in EquipmentList for each such item with a calculated quantity that reflecs the total number of kits times the component quantity needed for just one kit.

          • 2. Re: Keeping track of Items and their parts...

                 Great!  Thanks for the info, Phil!

            • 3. Re: Keeping track of Items and their parts...

                   Ok, I created an ERD and wanted to share it with you guys to make sure I'm on the correct path.  Any input or advice is greatly appreciated.  Thanks!


                   My thoughts are:  use a a 3-digit number with the prefix "_" so if I had a long list of items I could narrow down the results to only main items using "_" 
                   And as far as the components go; I'd have the parentkey prefix and just start 01,02,03...
                   1. Is this the correct way to go about this?
                   2. By using the method described above, will this make it simpler to pull components for items?
              • 4. Re: Keeping track of Items and their parts...

                     Looks good.

                     Since this thread was started, I ended up producing a demo file to help another person with similar questions. You might find this demo file and it's scripts/relationships helpful: