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.
Great! Thanks for the info, Phil!
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...Questions: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?
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: