Cross comparing BOMs

[MJuric]

I'm working with one of our product lines. My end goal is to attempt to narrow down what level of inventory we should keep on hand based on sales.

So, I have a product with some number of parts. Some of those parts are used in multiple products. So lets say I sold 10 of A, 5 of B and 15 of C. Let's assume that A, B and C all use the same housing. A and B use the same springs, B and C use the same seal and A, B and C all use different bearings.

What I want to do is to get a number of total used. So from the above list we only have one housing per part so we would have (30) housings and so on.

The first thing that popped into my mind was to export the BOMS of all these parts and make each part a separate tab in an excel spreadsheet. That seems like a daunting task considering I ~350-400 assemblies to look at.

So first question, assuming the best option is to pull the BOMS from all the parts and droop them into a spread sheet....what's the best way to automate that process?

Second question, is there a better way I'm missing here?

Thanks

[mattpeneguy]

How about creating an assembly with all of these subassemblies in it?...Ya sell 10 of A, 5 of B, and 15 of C. Drop 10xA, 5xB and 15xC in the assembly, create a parts only BOM from that ASM and let SW figure it out for you?...You don't even need a drawing, you can just create the BOM in the ASM.

[Frederick_Law]

Usually I just drop them in an assembly.
Some order require spare parts. So I drop them in also to get a total count of everything.

[mattpeneguy]

Beat you to it, @Frederick_Law ...
Also, you can create dummy parts with proper Custom Properties for things like loctite. You can even save these ASMs and keep track of how things go over time. I'd probably work up a scheme and name them by date range, maybe?

[MJuric]

How about creating an assembly with all of these subassemblies in it?...Ya sell 10 of A, 5 of B, and 15 of C. Drop 10xA, 5xB and 15xC in the assembly, create a parts only BOM from that ASM and let SW figure it out for you?...You don't even need a drawing, you can just create the BOM in the ASM.

I thought about this but it seems like a behemouth of a an assembly. for instance I'm looking at ~1000 copies of ~350 different assemblies each with 15-20 parts in them. So 15-20K parts in an assy with 350 S/A's.

[mattpeneguy]

Well, then I guess you may be looking at what macros can do for you then. I'd recommend moving this over to the Macro section, maybe.
Do you have a data management system? I'm wondering if you couldn't leverage the API of that system to get you what you need?
The last thing I'd want to be doing is dealing with is a bunch of unlinked spreadsheets. What if you add another part to one of those assemblies?

[jcapriotti]

I thought "Tree house" might be a good solution for this but it appears it won't give total quantities in its "parts only" list. You could still build the structure by adding a new top level assembly, then add all of the other assemblies on the order, even editing quantity of those assemblies.

image.png

Then you could "Export to SolidWorks" and open it to create the "Parts only" BOM. If this is a common task, you might invest in someone to write a program to do this via the API.

[MJuric]

Well, then I guess you may be looking at what macros can do for you then. I'd recommend moving this over to the Macro section, maybe.
Do you have a data management system? I'm wondering if you couldn't leverage the API of that system to get you what you need?
The last thing I'd want to be doing is dealing with is a bunch of unlinked spreadsheets. What if you add another part to one of those assemblies?

Hmmm, I wonder if our ERP system might me able to do this? I have not even looked into that. All the BOMS have to be in there and all the parts are under a single department so maybe I can find some magically way to export the BOMS for all the assys into a single spread sheet.

I'm not so worried about whether this is a "continually updated" number because I'm really only looking at "Raw floor space needed for inventory". Adding a single part or another assembly would only be adding "Floor space" for those new parts or additional sales of those parts. Changing existing assemblies is rather rare in any significant fashion as these are all catalog level assemblies.

[MJuric]

I thought "Tree house" might be a good solution for this but it appears it won't give total quantities in its "parts only" list. You could still build the structure by adding a new top level assembly, then add all of the other assemblies on the order, even editing quantity of those assemblies.

image.png

Then you could "Export to SolidWorks" and open it to create the "Parts only" BOM. If this is a common task, you might invest in someone to write a program to do this via the API.

That looks a bit interesting although I've only played with treehouse a little bit.

[mattpeneguy]

Well, then I guess you may be looking at what macros can do for you then. I'd recommend moving this over to the Macro section, maybe.
Do you have a data management system? I'm wondering if you couldn't leverage the API of that system to get you what you need?
The last thing I'd want to be doing is dealing with is a bunch of unlinked spreadsheets. What if you add another part to one of those assemblies?

Hmmm, I wonder if our ERP system might me able to do this? I have not even looked into that. All the BOMS have to be in there and all the parts are under a single department so maybe I can find some magically way to export the BOMS for all the assys into a single spread sheet.

I'm not so worried about whether this is a "continually updated" number because I'm really only looking at "Raw floor space needed for inventory". Adding a single part or another assembly would only be adding "Floor space" for those new parts or additional sales of those parts. Changing existing assemblies is rather rare in any significant fashion as these are all catalog level assemblies.

What's your ERP system? That's the first question to answer. It should know what's in each ASM, and all you'd need to do is create a list and parse that information from the system. I'm thinking something like a query...it's a database after all and that's what they're built to do. Whether or not you need some code will entirely depend on your system...If you can't do something like this with your ERP system, I'd be questioning what the purpose of the ERP system is in the first place...
I demoed DDM some years ago, and I would be VERY surprised if doing something like this wasn't a trivial matter...Not saying I wouldn't have needed to contact their support, but as above if it had trouble doing something like this, that alone may have disqualified it for use.

[Frederick_Law]

I thought about this but it seems like a behemouth of a an assembly. for instance I'm looking at ~1000 copies of ~350 different assemblies each with 15-20 parts in them. So 15-20K parts in an assy with 350 S/A's.

Doesn't matter. All you need is a BOM.
Put some in sub-assembly if you could.

[AlexLachance]

Hey Matt,

Have you tried detailed BOM's? We use these to export from SolidWorks to our ERP.

You could create an assembly with the desired number of sub-assemblies inside it and then have an detailed BOM generated from this assembly to show the detailed listing of the assembly, sub assemblies and parts.

Then you could use this to get your totals.
http://help.solidworks.com/2019/english ... erials.htm

[MJuric]

Well, then I guess you may be looking at what macros can do for you then. I'd recommend moving this over to the Macro section, maybe.
Do you have a data management system? I'm wondering if you couldn't leverage the API of that system to get you what you need?
The last thing I'd want to be doing is dealing with is a bunch of unlinked spreadsheets. What if you add another part to one of those assemblies?

Hmmm, I wonder if our ERP system might me able to do this? I have not even looked into that. All the BOMS have to be in there and all the parts are under a single department so maybe I can find some magically way to export the BOMS for all the assys into a single spread sheet.

I'm not so worried about whether this is a "continually updated" number because I'm really only looking at "Raw floor space needed for inventory". Adding a single part or another assembly would only be adding "Floor space" for those new parts or additional sales of those parts. Changing existing assemblies is rather rare in any significant fashion as these are all catalog level assemblies.

What's your ERP system? That's the first question to answer. It should know what's in each ASM, and all you'd need to do is create a list and parse that information from the system. I'm thinking something like a query...it's a database after all and that's what they're built to do. Whether or not you need some code will entirely depend on your system...If you can't do something like this with your ERP system, I'd be questioning what the purpose of the ERP system is in the first place...
I demoed DDM some years ago, and I would be VERY surprised if doing something like this wasn't a trivial matter...Not saying I wouldn't have needed to contact their support, but as above if it had trouble doing something like this, that alone may have disqualified it for use.

I'm looking into how to do this. I don't use the ERP system all that much over the last two years in Engineering but in my new position I've had need of it more and more so I'll see if I can figure it out.

Thanks all for the input.

[AlexLachance]

I'm looking into how to do this. I don't use the ERP system all that much over the last two years in Engineering but in my new position I've had need of it more and more so I'll see if I can figure it out.

Thanks all for the input.

Pretty sure my answer is what you're looking for buddy

You might need a little fondling with it but you'll get the end result you're looking for I believe.

[MJuric]

I just wanted to thank everyone for the input on this.

I looked at using SW Treehouse and actually started thinking this would be pretty straightforward....until I realized that we've been making this product line since the 80's and we STILL don't have the entire product line in solid models, yes I realize how screwed up that is.

I then went to the ERP and couldn't figure it out. Turns out I didn't have permissions to access that area of the ERP. After I got permission I was able to pretty easily export all the BOMS. I ended up doing a lot of futzing around in Excel in order to get to the final goal which was total sold number of individual parts. Couldn't figure out how to do that in the ERP but I suspect it's would have been much easier if I could have figured it out than what I did.

In any case thanks all for the input.