parts explosion report

[IanR]

Hi

I wonder if anyone can help me.

I have a requirement which is similar to a �parts explosion�. In other words, I have a list of components. Each component consist of a set of sub-components. In turn, each sub- component has its own set of sub-sub-components etc. The hierarchy may be up to 10 levels deep.

I have a file of component names. I also have a link file that links the components with their sub-components.

I would like a simple way to produce a report to list all the components and the various levels of sub-components.

Is there any simple way to create a view/report that does this?

Thanks in advance for any guidance.

Ian

[Stan Mathews]

Re: parts explosion report

Have you tried the usual one to many to many to many to ... set? (by file I assume you mean table?

[IanR]

Re: parts explosion report

Hi Stan

Yes I do mean table

I think you are suggesting that I simply create a set of component to link to component to link etc.

Is my understanding correct?

Ian

[Stan Mathews]

Re: parts explosion report

Not knowing any more than I do about your database structure than I do, I would try that, yes.

Not really sure what is in "a link file that links the components with their sub-components". Some components can be sub-components of "kits" and they can also be the components to which other sub-components are linked?

You may need a many to many set with components linked twice to the link table.

[Peter.Greulich]

Re: parts explosion report

...The hierarchy may be up to 10 levels deep.
...
I would like a simple way to produce a report to list all the components and the various levels of sub-components.

Assuming you build a set based upon 10 tables that incorporates your 10 level hierarchy, you will need to use sub-reports to do the job. I wonder if sub-reports support 10-levels deep? Hhhmmm...

[Peter.Greulich]

Re: parts explosion report

Ir you can use group breaks. Much simpler than sub-reports. I assume 10-levels wouldn't be a problem here.

[CALocklin]

Re: parts explosion report

If you want to do what I think you want to do, I've never found an easy way to do it. (Like many things, what seems like it should be easy is often hard; and what seems hard is often easy.)

I'm assuming you want to build a "Bill of Materials" (BOM) report which is an indented parts list which could consist of sub-components of sub-components of sub-components, etc., etc., etc. Something like this:


I managed to create the above report (copy attached in case I ever delete this from my website) and a list at the end that shows the total number of items required.

(I'm only showing partial data since this was done a couple years ago and may still be valid data.)



The bad news is that (A) my customer was providing a list of parts that included an "item number" such as 1.6.1.4 which was used to determine the parent part and indent level and (B) it was done mostly through xbasic and calc fields in the report that padded the part numbers with the appropriate number of blank spaces. Basically the list is simply sorted by the "item number" and the indent level is determined by the number of decimals in the "item number".

In my case, these "item numbers" were being created by the CAD system that was used to make the design and it exported a text file we used for the parts list.

This "program" is only used to print out the BOM report; not to create it. It also does some other things not directly related to the BOM. However, if you think it might be applicable, I will consider deleting/editing the data and posting it.

I also built something else awhile back (actually in A5v1 and I'm not sure what it would take to convert it to v8 or higher) that did somewhat the same thing but it also has an "indent level" field that the user had to manually enter. That indent level could be different for different assemblies. For example, assembly "A" might use a Framus as a level 2 component while assembly "B" uses it as a level 4 component. Consequently each assembly has separate component level definitions.

Also, the individual parts had to be either entered in the order they were to be printed out in or you could add a sequence number to each component - a somewhat tedious and error prone method. This was fairly simple to use but it had to be carefully checked when done. The part details were stored in one table and the "nomenclature" table just stored the part number and indent level in the appropriate sequence.

I never did find a way to say simply "this part belongs under that part" then attach "that part" to another part and have the program automatically determine the indent levels. Not saying it can't be done; just that I never figured it out.

You might find a way to use the 1:M to 1:M to 1:M set but I wouldn't count on it. Basically each parent level could have different numbers of subcomponents and I can't think of any good way to represent this in typical 1:M tables.

This is the way it needs to look - possible in a report but very difficult in a form:

Parent_assembly
|-level1_component_A
       |-level2_component_A
              |-level3_component_A
                     |=level4_components_A
              |-level3_component_B
              |-level3_component_C
                     |-level4_component_B
                            |=level5_components_A
       |-level2_component_B
              |=level3_components_D
              |-level3_component_E
       |-level2_component_C
|-level1_component_B
       |-level2_component_D
              |=level3_components_F
|-level1_component_C
|-level1_component_D
       |=level2_components_E
...
etc., etc., etc.

One problem is that a typical 1:M setup designed for say 5 levels will always have 5 levels even when only 1 level is needed such as for "level1_component_C" above.

Another potential problem is that putting the parts in separate tables doesn't really work out very well. Especially when that Framus might be used at level 2 in one assembly, level 3 in another assembly, and level 4 in yet another assembly. Or, maybe even worse, that #12 screw might be used at levels 2, 3, 4, and 5 under various components of one main assembly. The only solution to this is to keep the part info itself in one table and put the part numbers in the 1:M tables where needed.

Then there's always the problem of updating assembly info when a sub-component is updated. The problem here being that changing the suffix on a sub-component can be set up to automatically show up in the component list of each assembly BUT the assembly itself often needs to be updated also to show that it is using the new component. (Some companies may do this differently but this was common everywhere I worked.) However, you don't necessarily want to update for each sub-component change because in some cases you may update multiple sub-components at the same time. You might even run into a situation where some assemblies continue to use the old version until the inventory runs out while others immediately use the newer version.

In my experience, the revision level of each sub-component needs to be part of the BOM itself and only updated manually - and the assembly revision level can be updated manually at the same time. Of course, the database can be used to get a list of possible parent assemblies to be updated so you don't forget anything.

To repeat, I don't think there is a simple solution.

As I type this I wonder if a table of components with part numbers, names, etc. and the 1:M tables with lookups to enter just the part number references as needed for each assembly might work. It would require some fancy "footwork" with the reports to get the empty levels to display with no height. However, I don't see any good way to create an easy to use data entry form for this. The data entry form would work but would be difficult for most people familiar with a BOM to use. You might be able to see all (or at least many) of the level 2 components at one time but only one would be selected. You could then see all level 3 components for the selected level 2 component but, again, only one would be selected. Then you could see all level 4 components for that level 3 component but again only one would be selected. In other words, you could do it but it wouldn't be "intuitive" because (a) there would always be "X" levels displayed even if some levels were blank, (b) it would be seen in pieces rather than one continuous list like a typical BOM, and (c) it would be harder for someone used to a typical BOM list to see what parent parts were selected. It's doable but might take some training time for someone to get used to it.

Instead of the typical layout as shown above, you would have something like this where the user selected component is shown in bold blue (yes, this is taken directly from the example above):

Parent__assembly
|-[B][COLOR="Blue"]level1_component[/COLOR][/B]
|-level1_component_B
|-level1_component_C
|-level1_component_D
       |-[B][COLOR="blue"]level2_component_A[/COLOR][/B]
       |-level2_component_B
       |-level2_component_C
              |-level3_component_A
              |-level3_component_B
              |-[B][COLOR="blue"]level3_component_C[/COLOR][/B]
                     |-level4_component_B
                            |=level5_components_A

The above works but it will probably take awhile to get used to. It's quite a bit different from the one above that is what people are used to seeing.

On the other hand, it's entirely possible that I've missed some simple way around this. If anyone has a tested solution that's easy, please post it.

[Al Buchholz]

Re: parts explosion report

There is a function to help with this that Selwyn wrote awhile back...

I takes a list of parent-components and builds a BOM list from it..

It can also be used for family trees...

I can't remember what it's called and can't find something about it.. so far.. If someone else remembers, please post a link here...