Multi Level Bill of Materials
Will this work? It plays off of the “parent” concept I mentioned previously, which will mean an extra step as you’re setting up your hierarchy because you have to choose the parent by its ID. I didn’t put in every row from your example, but enough to ensure that the hierarchy symbols build correctly.
I wish it were possible to do all of this inside the primary field, but unfortunately that’s something that can’t be worked around due to all the field and table referencing going on (i.e. problems with circular references). However, if it’ll work, I’ll break down the steps for you.
I guess I probably wasn’t the clearest. Building the table itself shouldn’t be a problem, I’m more curious if its possible to generate a report that digs down and gets the children as a new row below. @Justin_Barrett - in your screenshot example, how would your table work if a component had 2 parents?
To directly answer your final question, a component wouldn’t have two direct parents because it mimics the standard tree structure of the sample you posted originally, where any given item only has a single parent. However, to use the “Axel cap, red” item in that original diagram as an example, an item might be listed under more than one BOM (or assembly, to use the terminology of your second screenshot). That doesn’t mean it’s got two parents. It just means that some quantity of that item is needed in more than one assembly.
Your second diagram makes it a little more clear what you’re trying to accomplish, and also makes me think that the setup I proposed isn’t what you really want. However, there are still some fuzzy areas for me, the main one being why you’re tracking assemblies and parts in the same table. Again, I don’t have a complete grasp of what you’re aiming for, but based on what I understand now, my gut tells me that parts should live in one table, and assemblies in another. On top of that, I wouldn’t directly link to parts in the assemblies table. I would make a third table to do the connection, which would allow you to specify the quantity for each part tied to each assembly.
Here’s how these three tables might look. First the [Parts] table:
Then the [Assemblies] table, with the link field to the [Final Assembly] table visible. (This field was hidden in the [Parts] table, bit it’s also there.
And finally the [Final Assembly] table, where you link to both of the other tables to indicate how many of each part is needed for a given assembly. (The first field is an Autonumber field for simplicity.)
Back in the [Assemblies] table, the links from [Final Assembly] will let you look up all of the linked parts, collect their quantities, etc. for reporting.
Will this work for what you need?
