[AccessD] Finding the levels in a bill of material

I wrote a commercial bill of materials processor, and, while it has an
internal level number field, it's for use only in the cost roll up function
and is not displayed to the user.  The actual level of a part in a
multi-level assembly only needs to be known in a report of the specific
assembly.  So it is displayed in my reports as both a level (not the one in
the product structure table) and as an indent on the report.  

To make the reports I create a temp table with all the report data using a
'push/pop' scheme where a component that has a BOM causes a push onto a
stack, then next level below is added to the temp table, and at the end of
that sub-assembly's BOM (assuming no parts on that level with BOMs) the
stack is 'popped' and the level above continues processing.

HTH

Rocky

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ewaldt at gdls.com
Sent: Thursday, May 22, 2014 4:46 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Finding the levels in a bill of material

In a simplified bill of material (BOM), let's say I have (first line
contains field names):

PartNumber,     Parent  , Level
001, (null), 0
002, 001, 1
003, 001, 1
004, 003, 2
005, 001, 1
006, 005, 2
007, 005, 2
008, 007, 3
002, 007, 3
009, 005, 2
010, 001, 1

I put the levels in to show what I actually need to find. In other words, I
have the part number and the parent's part number, but don't have the level.
so pretend the levels are all blank. In the real tables, there could be over
20 levels and 20,000 records.

Since it's zero-based, the first is level 0, of course. Any parts showing
the first one as the parent logically will be level 1. Those are the easy
ones. 

Part numbers can appear more than once and at different levels (not as a
descendent of themselves, of course). Note that part number 002 shows up as
a child of 001, and also as a child of 007 (with all that messing around,
you have to figure Bond has some kids somewhere).

BTW, each also has an ID number (lngID), assigned in the order the parts are
presented.

Does anyone have a good algorithm to find the Level for these?

Thanks in advance for any and all help.

Tom Ewald
Mass Properties
General Dynamics Land Systems
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com