Recursive code to create indented bill of material

Recursive code to create indented bill of material

Post by Gary Manig » Fri, 15 Apr 2005 09:42:49


I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list the
parts and assemblies with their respective parts in a hierarchical list. I
want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found exactly
what I an looking for. I thought I would create a list of parts that
contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.
 
 
 

Recursive code to create indented bill of material

Post by Alan Web » Fri, 15 Apr 2005 10:43:25

Gary,
I wrote one and ran into problems with filling the call stack before I had
maxed out my levels on my hierarchy. The one cool way around this is to
identify how deep the tree is and then build a table with as many columns as
there are levels in the tree. *Most* trees tend to be between 3 and 7
levels meaning most hierarchies could be mapped in a table with 7 columns.
Anyway, building your report would mean iterating through the columns of
this table from a beginning point. As an example, let's say CD jewel cases
are part of the finished good, Virtual PC. The jewel case is near the
bottom of the hierarchy because it consists of four components and contains
a CD, which is at the bottom of the hierarchy. The jewel case goes into an
interior box that holds it in place which goes into an outer box. Anyway,
by my count we've got four levels meaning we need four colums to map every
combination of CD, jewel case & boxes. Virtual PC is part #vpc_42005 in my
example. So, I find the row in the first column that has my part number and
then loop through my columns to find component parts. Done, and no
recursion needed.
--
Alan Webb
XXXX@XXXXX.COM
"It's not IT, it's IS"

 
 
 

Recursive code to create indented bill of material

Post by pietlinde » Fri, 15 Apr 2005 10:48:17

 
 
 

Recursive code to create indented bill of material

Post by pietlinde » Fri, 15 Apr 2005 10:48:55

read these,,,
http://www.yqcomputer.com/
http://www.yqcomputer.com/

and if you really want a brain teaser, read Joe Celko's "SQL for
Smarties". Not being a smarty, I guess I'm not qualified...
 
 
 

Recursive code to create indented bill of material

Post by ByteMyze » Fri, 15 Apr 2005 11:02:33

Gary Manigian" < XXXX@XXXXX.COM > wrote in message
news:30j7e.1889$ XXXX@XXXXX.COM ...
the
exactly

For a TRULY recursive BOM, there needs to be some revision to the table
structure, for the sake of normalization, like so:

tblBOM: (Stores all part Keys, Part Numbers and Descriptions, no need for
this data in BOTH tables)
lngBOMID (PK)
strPartNo
strDescription

tblBOMDetail: (Stores only the parent-child relationships and the qty-per)
lngBOMDetailID (PK)
lngBOMID (FK)
intQuantity

Then, paste the following code into a new module:
'---------------<< begin code >>---------------
Public Sub EnumBOM(Optional llc As Long = 0, Optional PK As Long)

Dim rst As Recordset
Dim x As Long

If llc Then
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM INNER JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE tblBOMDetail.lngBOMDetailID=" & PK & ";")
Else
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM LEFT JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE tblBOMDetail.lngBOMDetailID Is Null;")
End If

Do While Not rst.EOF
For x = 1 To llc
Debug.Print vbTab;
Next x
Debug.Print rst!strPartNo; ": "; rst!strDescription
If IsNull(rst!FK) = False Then EnumBOM llc + 1, rst!lngBOMID
rst.MoveNext
Loop

rst.Close

End Sub

'----------------<< end code >>----------------

I tested the above with the following sample data in the tables:
tblBOM:
lngBOMID strPartNo strDescription
1 AssyNo1 Assembly No. 1
2 AssyNo2 Assembly No. 2
3 Comp1 Component 1
4 Comp2 Component 2
5 Comp3 Component 3
6 SubAssy1 Sub-Assembly 1
7 SubAssy2 Sub-Assembly 2
8 SubAssy3 Sub-Assembly 3

tblBOMDetail:
lngBOMDetailID lngBOMID intQuantity
1 3 1
7 3 1
1 4 1
2 4 1
7 4 1
2 5 1
6 5 1
8 5 1
1 6 1
2 7 1
7 8 1

With the tables populated with the above data, running EnumBOM produced this
result:
AssyNo1: Assembly No. 1
Comp1: Component 1
Comp2: Component 2
SubAssy1: Sub-Assembly 1
Comp3: Component 3
AssyNo2: Assembly No. 2
Comp2: Component 2
Comp3: Component 3
SubAssy2: Sub-Assembly 2
Comp1: Component 1
Comp2: Component 2
SubAssy3: Sub-Assembly 3
Comp3: Component 3

If you study the above information carefully, you will see how it works. If
you would like a more detailed elaboration, by all means, ask.


 
 
 

Recursive code to create indented bill of material

Post by Steve Jorg » Fri, 15 Apr 2005 14:55:30


What version of Access was this? I wrote code to deal with BOMs using
recursive code a while back in Access 2000, and it worked fine at 12 levels or
so.

You do have to make sure there are no recursive tree branches! It's harder to
prevent this than you might think, and the user error is not as hard to make
as you might think.
 
 
 

Recursive code to create indented bill of material

Post by johnsonche » Fri, 15 Apr 2005 19:07:59

Back to 10 years.... Writing a recursive BOM listing in a language
not supporting recursive (like BASICA or RPG/400) would be painfull.
Write it in BASICA, then re-write in RPG/400.

Every local variables should put to stack implemented by array.
 
 
 

Recursive code to create indented bill of material

Post by Alan Web » Sat, 16 Apr 2005 00:41:51

Steve,
It was Access 2000 on a complex bill of materials for a fulfillment house
that did finish packaging of software. It was at Schwab where I saw the
table with each column of the hierarchy and I thought it was cool.
--
Alan Webb
XXXX@XXXXX.COM
"It's not IT, it's IS"
 
 
 

Recursive code to create indented bill of material

Post by Phil Stant » Sat, 16 Apr 2005 01:43:10

If its any help I wrote a database for recipes (Actually Flavours for a
flavour making company). Each flavour consisted of ingredients and
optionally other flavours. E.G A cheese and onion flavour consisted of a
cheese flavour + oil of onion etc. The cheese flavour consists of a cheese,
a chemical etc
There was no limit on how name flavours were nested

Phil
 
 
 

Recursive code to create indented bill of material

Post by Bob Quinta » Sat, 16 Apr 2005 08:00:45

"Gary Manigian" < XXXX@XXXXX.COM > wrote in



First I'd make a couple of minor changes to your structure.It's
not normalized.

TBL_PARTNo
str_PartNo, PK
Str_Description
str_UM

Tbl_ProductStructure
str_ChildPartNo FK
str_ParentPartNo FK
currQtyPer (you may want to handle 0.5 tube of glue)

pk is the two FKs

The way I approached the problem was not to go recursively, but
to create an array of datasets, writing out to a temporary table.
a simple do until EOF() loop walks the first recordset, tests if
the current part is a parent, and opens the new recordset in the
next Indenture Level element of the array if it is. If it's not a
parent just move to the next record

Hit EOF() and decrement the Indenture level, pointing back to the
parent.

Loop.

It creates a 4000 row table in about 20 seconds, using SQL_Server
tables,

I also have a string array where I store the parent partNOs and
walk that to test for inbreeding (item is a child or grandchild
of itself) which leads to infinite depth BOMs.


--
Bob Quintal

PA is y I've altered my email address.
 
 
 

Recursive code to create indented bill of material

Post by Wayne Gill » Sat, 16 Apr 2005 10:02:36

On Wed, 13 Apr 2005 20:42:49 -0400, "Gary Manigian"



I have a couple of test databases demonstating recursive routines for
a BOM at -

www.bestfitsoftware.com.au/downloads/bomtests.zip

Files are in Access97
 
 
 

Recursive code to create indented bill of material

Post by Gary Manig » Tue, 19 Apr 2005 08:30:47

Identifying the number of levels is part of my problem. Each BOM contains
subassemlies which could contain other subassemblies. I won't know until I
traverse the entire BOM. Or am I missing something?
 
 
 

Recursive code to create indented bill of material

Post by Gary Manig » Tue, 19 Apr 2005 09:00:43

I would like to thank everyone for their replies. It will take me a while to
work my way through it.

Gary
 
 
 

Recursive code to create indented bill of material

Post by pietlinde » Tue, 19 Apr 2005 11:07:21

While you're at it, read Joe Celko's comments on recursive trees
here... LOTS of fun.
 
 
 

Recursive code to create indented bill of material

Post by Gary Manig » Thu, 21 Apr 2005 02:38:56

Here are some interesting links:

Joe Celko's Trees and Hierarchies in SQL:
http://www.yqcomputer.com/ #description

Unconventional Ways to Avoid SQL Recursion:
http://www.yqcomputer.com/

Graphs, Trees and Hierarchies:
http://www.yqcomputer.com/

Trees in SQL
http://www.yqcomputer.com/

Storing Hierarchical Data in a Database
http://www.yqcomputer.com/