Assuming a table BOM with Parent, Child columns.
A temp table with C1, C2, C3, C4 -- assuming no more than four levels of
parent and child. Put in 8 columns if you need them. The trick I think
is to fill the lowest level first, then update the next level higher.
Repeating so you end up with something like this:
NULL, NULL, NULL, Part
NULL, NULL, Assy, Part
NULL, Assy, Sub, Part
Assy, Sub, Sub, Part
INSERT INTO TempTable (c4) SELECT Part from Parts
APPEND BOM to C3,C4
UPDATE TempTable set C2 = parent where child = c3
UPDATE TempTable set C1 = parent where child = c2
-- You now have rows with NULL,NULL,Assembly,Part
UPDATE TempTable set c1 = c2, c2 = c3, c3= c4, c4=null where c1 is null
-- shifts everything one column to the left where there are only 3 levels
UPDATE TempTable set c1 = c2, c2 = c3, c3 = c4, c4=NULL where c1 is null
-- shifts everything left again where needed.
Part, NULL, NULL, NULL
Assy, Part, NULL, NULL
Assy, Sub, Part, NULL
Assy, Sub, Sub, Part
This should move the assemblies and parts to the left two columns, three
columns or leave all four levels there.
When you create the report, suppress repeated values in each of col2 1,2
and 3
Play with this idea.
I'm away for the rest of te week, so the rest is up to you.
Good Luck
Albert
On 2015-11-25 9:18 AM, Jim Belisle wrote:
I have done temp tables (never stored procedures though) in some of my
reporting.
I think my biggest problem is relating what I want to something I have already
done.
Brain freeze or maybe age!!
Would you use a cursor to assign this information to the proper level?
I ask because my sub assemblies would not always be on the same level for
different products we make.
James Belisle
Making Information Systems People Friendly Since 1990
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Albert
Sent: Wednesday, November 25, 2015 10:13 AM
To: Jim Belisle
Subject: [RBASE-L] - Re: BOM
I would use a temporary reporting table for this. The columns to report would
be as follows or both Assy/Sub part numbers and descriptions. I find that
system easier than using report variables.
Assembly
Assembly description
Sub Level 1
Description Level 1
Sub Level 2
Description Level 2
Sub Level 3
Description Level 3
Load the table with a stored procedure, repeating the values in each column and
adding the subs as needed.
Each column would be reported with repeated values hidden. That should give you
what you need.
Albert
On 2015-11-25 8:57 AM, Jim Belisle wrote:
For years we have costed our products with a simple report just throwing
together all parts making up a product not worrying about using an indented BOM.
We would like to go to an indented BOM.
Right now it is just
Model# then all parts making up the model.
We want something like this:
Level 1: Model#
Level 2: Assemblies and parts we ship
Level 3: Sub assemblies (or parts if no subs) making up
the assemblies.
Level 4: Parts making up the sub assemblies.
I can get it so all assemblies are together and then all subs together by
assigning a BOMtype to them.
So it will be:
Assy
Assy
Assy
Sub assy
parts
Sub assy
parts
Sub assy
Parts
That is not however what we want. I just can't seem to figure the proper way to
handle this.
Any blues clues would be appreciated.
James Belisle
Making Information Systems People Friendly Since 1990
[cid:[email protected]]
