Managing BOMs

[Pack34]

I'm in charge of managing and updating of older board designs as well as developing new ones. What I'm looking to try to accomplish is to simplify and streamline BOM management and component ordering. The less time I have to worry about ordering is more time I can devote to design.

My idea is to have a master parts list which each BOM will pull their associated DigiKey part numbers into the individual BOM spreadsheet. My main concern is the duplication of data. Say if the part I normally order is no longer sold or is replaced, then I would only have to update data in one place and then each design's BOM is then updated automatically.

If possible, at the end, I'm looking at trying to pull in the BOMs for all the board designs just to be able to see the exact numbers of the various components we use in order to immediately know when I should splurge and buy larger bulk orders of components. Off the top of my head, I know of some really commonly used components that we use but I felt it would be nice if I could get a more detailed printout to get the exact numbers. I'm also thinking of weighting the components depending on how often board designs are ordered. Perhaps I can use alternate values of components to be able to better take advantage of quantity price breaks.

Has anyone done something similar? Or have any input of what you have found that works and what doesn't. I'm not asking for anyone to do my work for me. I'm just asking for input on if my idea makes sense and if anyone here has tried, or currently implementing, something similar.

Thoughts?

[Someone]

Parts&Vendors
www.trilogydesign.com/

[HackedFridgeMagnet]

Sounds like you need a database instead of a spreadsheet.
This kind of work is bread and butter to a real database, but obviously if you have no expertise in this area then it would be a lot of learing to do.

Somebody had a thread in EEVblog about a web based online Parts Management System that he had been working on. Might be hard to find though.

[cwalex]

Sounds like you need a database instead of a spreadsheet.
This kind of work is bread and butter to a real database, but obviously if you have no expertise in this area then it would be a lot of learing to do.

Somebody had a thread in EEVblog about a web based online Parts Management System that he had been working on. Might be hard to find though.

Is this what you were thinking of? www.ecdb.net

[Smokey]

The way I deal with this issue is to give each type of component an internal part number.  Each internal part number refers to an arbitrary number of equivalent manufacturer part numbers that can all be used interchangeably.  So say I use a TO-220 5V voltage regulator.  The one internal part number, say "IC-123", refers to the ON semi part and the TI part, and any other TO-220 5V regulator that I qualify.  Any of those parts can be used when I need a TO-220 5V regulator.  This way the design files use the internal part numbers and the purchasing person can go through the master parts cross-reference list and buy whatever manufacturer part is cheapest.  It is also super easy to add or removed qualified parts since all the design files refer to the internal part number.  Each project BOM only shows quantities of internal part numbers.  All you need is a spreadsheet for this.

Example (:: is a new column.  I'm not saying all these parts are interchangeable in all cases, just an example):
Internal # :: Description ::  Manufacturer#1  ::  Manufacturer#2 :: Manufacturer#3 :: ..........Manufacturer#N
IC-123 :: "TO-220 5V Regulator"  ::  OnSemi - MC7805CTG  ::  Fairchild - MC7805ECTBU :: STMicro - L7805ABV  ::  TexasInstruments - UA7805CKCS

As far as keeping track of common parts through projects, you'll have to write some scripts or get an MRP system of some sort.  I think quickbooks can do simple stuff and it's pretty cheap.

[Pack34]

Sounds like you need a database instead of a spreadsheet.
This kind of work is bread and butter to a real database, but obviously if you have no expertise in this area then it would be a lot of learing to do.

Somebody had a thread in EEVblog about a web based online Parts Management System that he had been working on. Might be hard to find though.

The main parts list is in Access but I can't get it on my computer since the company doesn't have enough licenses. What I'm doing to get around it is to write a script to export the parts table to an excel file on the network. Then the individual BOMs pull all of the detailed information from it based on the criteria I enter.

Example:

Value | Category | Mounting Type | Package / Case
---------------------------------------------------------------------
1k......| Resistor...| Surface Mount | 0805

Then a long description, digikey part number and price and pulled down next to it using this method: http://support.microsoft.com/kb/59482

The way I deal with this issue is to give each type of component an internal part number.  Each internal part number refers to an arbitrary number of equivalent manufacturer part numbers that can all be used interchangeably.  So say I use a TO-220 5V voltage regulator.  The one internal part number, say "IC-123", refers to the ON semi part and the TI part, and any other TO-220 5V regulator that I qualify.  Any of those parts can be used when I need a TO-220 5V regulator.  This way the design files use the internal part numbers and the purchasing person can go through the master parts cross-reference list and buy whatever manufacturer part is cheapest.  It is also super easy to add or removed qualified parts since all the design files refer to the internal part number.  Each project BOM only shows quantities of internal part numbers.  All you need is a spreadsheet for this.

Example (:: is a new column.  I'm not saying all these parts are interchangeable in all cases, just an example):
Internal # :: Description ::  Manufacturer#1  ::  Manufacturer#2 :: Manufacturer#3 :: ..........Manufacturer#N
IC-123 :: "TO-220 5V Regulator"  ::  OnSemi - MC7805CTG  ::  Fairchild - MC7805ECTBU :: STMicro - L7805ABV  ::  TexasInstruments - UA7805CKCS

As far as keeping track of common parts through projects, you'll have to write some scripts or get an MRP system of some sort.  I think quickbooks can do simple stuff and it's pretty cheap.

This is a good idea, it'll make my lookups simpler and probably harder to mess down the road. I could probably implement intelligent part numbers such as R10k0805 for a 10k 0805 resistor and add alphas to the end for specific use cases. Using internal part numbers does add another level of complexity but as long as you can open up the master parts list and do a search and/or filter then it shouldn't matter.

[HackedFridgeMagnet]

what we use at work which is slightly different I think to an internal part number is a part category which is basically its functional category.

so a 10k SM805 is the category
and the part might be a Vishy CRCW080510K0FKEA

each category has a default part, which can change.

[vpapanik]

Could this help ?

http://vpapanik.blogspot.gr/2013/12/for-better-bom-in-excel.html

[free_electron]

that's how i do it in altium. i link the live data.
if a part goes obsolet i change the link and push it forward to the desing database. done.
everything rolls out automated.

[DeanA]

Parts&Vendors
www.trilogydesign.com/

Agree, I'm happy with Parts&Vendors too, it's not very expensive either.  Gives you the ability to link numerous approved suppliers and manufacturers part numbers to your own internal company part numbers.

I'm still trying to find a good way to import the BOM from Altium, I've tried to use the Altium Database Link feature without much luck and also tried using Microsoft access to link the schematic BOM to the P&V company part numbers but it's still a bit cumbersome.

Regards,
Dean.