Albert, Have a Happy Thanksgiving.
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 11:11 AM To: Jim Belisle Subject: [RBASE-L] - Re: BOM 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]] >> >> > >
