Reprise: Celko Nested BOMS

[kinsham]

There were a series of posts on this topic a long time back. I have
just started on the problem, and imported a set of parts and nesting
levels into Michel Walsh's database d/l at
http://www.mvps.org/access/queries/qry0023.htm.

I ran into a problem with my data due to the presence of parts that are
used by multiple parent assemblies. Only the first occurrence of the
part number is used in the roll-up, giving an incorrect. I use the item
part number as the memberName as hence this is not unique as it is in
the example

The example in the database seem to assume that all parts are unique
which is not the typical case. Modifying the example table to add a
repeat of a part number (= memberName in the database) also adjusting
lft and rgt to suit, yields the wrong answer. The added part is not
counted in the roll up to the final assy just as I found for my data.

Did the previous threads address this point, I couldn't be sure?
Whatever, is there a way round this difficulty.

btw the nested BOM idea is really neat!

Thanks.

 

[Allen Browne]

While BOMs are a great idea, there are certainly traps.

Not only do you end up with children who have multiple parents and parents
who have multiple children, but a major issue is the infinite recursion that
occurs when a child is actually its own ancestor. In some designs you can
avoid that (e.g. with unique indexing like you use with a TreeView control),
but that's not always practical.

Whenever I have to use loosely structured BOMs, I always specify a maximum
number of accepted generations (typically between 4 and 8.) If the parentage
does not resolve elementally in that many generations, the code opts out of
whatever it's up to, reporting the branches that did not resolve, and asking
the use to fix them. The idea it to create a table that holds an
unnormalized copy of the data, and populate it by looping through the
generations in VBA. If the operation succeeds (no recursion beyond the
maximum allowed), you can then cheat and use this unrecursed table for
whatever you need to do.

Don't know if that's any use for what you are doing. Hopefully there's some
leads in there somewhere. Others may have different approaches to suggest as
well.

 

[David F Cox]

I loved the Nested Sets idea. I could see how it was appropriate for
organisational structures with strict hieachies and geneology , but did not
find a way to extend it to most of BOM. apps that I have seen. 3/8" washers
are everywhere.

The only idea I could come up with so far was to have strings as keys in a
lookup table, and append other strings to elucidate the hierarchy.

Items
Key Item BOMlow BOMhi
aax1 Boiler asdf asdg
ab34 Trailer aapq aapr
.....
wash 3/8 washer "" ""
...


QTY BOM
322 aax1asdf...........wash
48 ab34aapq.........wash

I am interested in seeing how Celko and others have approached this problem

David F. Cox