BOM item number customization?
BOM item number customization?
(OP)
I was wondering if anyone knows if there is a way to customize the numbers in the BOM.
I want to have my make items as 1-x and my purchase items as $1-$x. What I am doing right now is editing the BOM and manually typing in detail numbers. The problem with this is that they are then not connected to my balloons (have to type the numbers in those manually also) and when I make any change to my details, my manual inputs for that detail disappear from the BOM and I have to put them back in.
I would appreciate any input.
Thanks
I want to have my make items as 1-x and my purchase items as $1-$x. What I am doing right now is editing the BOM and manually typing in detail numbers. The problem with this is that they are then not connected to my balloons (have to type the numbers in those manually also) and when I make any change to my details, my manual inputs for that detail disappear from the BOM and I have to put them back in.
I would appreciate any input.
Thanks






RE: BOM item number customization?
Do you have the ability to place the designation symbols within you "custom properties" for each part?
What I am getting at is that the bom that is inserted into the drawing should be generated from data gathered from the components with the assembly on the drawing. If this is how the bom is generated, it should be rather simple to modify the "custom properties" to present the unique information for each item. Additionally there are different options for your item bubbles. rt click on the item bubble and in the feature manager area will show up a slew of options.
If you modify the bom from within SW, you will negate the ability to update the bom automatically.
Hope this helps a little, mabe a bit more info from you could narrow down the issue and we can get it resolved.
-Jay
RE: BOM item number customization?
I do have the ability to make a custom property that has a value of '$', but how can I add that custom property before the automated BOM item number?
I really want the BOM to be fully automated, but I have a customer that requires his item numbering scheme to have dollar signs in front of the purchase and have the purchase items also start from 1.
Also, I didn't see anything when I right clicked over the balloons that would allow me to put a prefix in front of the item number.
RE: BOM item number customization?
In the mean time, any helpful advice is welcome from all the other users on this matter.
Back with ya soon,
Jay
RE: BOM item number customization?
If you want I can send you a scr shot of the bom and/or the modified assembly excel file.
Jay
Jay@dicarinc.net
RE: BOM item number customization?
RE: BOM item number customization?
-Jay
RE: BOM item number customization?
I got most of this to work. That is – the BOM part works fine but the balloon numbers ignore the changes completely.
This is what I did. I made a copy of my standard BOM Template and added 3 columns to it so that:
Col-A is the original ITEM NO.
The label for Col-B is P/M and is named PM
The label for Col-C is PCnt
The label for Col-D is MCnt
Columns C & D are used to contain a running count and should be hidden before insertion
Cell C2 contains the formula =IF(B2=”P”,1,0)
Cell D2 contains the formula =IF(B2=”M”,1,0)
Cell C3 contains the formula =IF(B3=”P”,1+C2,C2)
Cell D3 contains the formula =IF(B3=”M”,1+D2,D2)
Select Cells C3 & D3 and drag the formula down as far as you need them
Cell A2 contains the formula =IF(B2=”P”,CONCATENATE(“$”,C2),IF(B2=”M”,D2,””))
Select Cell A2 and drag the formula down as far as you did Cells D3 & E3
You can now test the formulas by putting a “P” or an “M” in Column B
Please NOTE: Placing any other value in Column B results in an empty value in Column A – This can be eliminated by removing the second IF test so that the formula is =IF(B2=”P”,CONCATENATE(“$”,C2), D2)
I opened an assembly with 15 parts and replaced the BOM with the new BOM. While editing the BOM I placed an “M” in all items except 6 through 10 which got a “P”. The BOM showed items 1 through 5 and then $1 thru $5 and then 6 thru 10 correctly – The balloon numbers never changed though and maintained the original 1 thru 15 count even after doing a Ctrl-B & a Ctrl-Q.
I don’t have the slightest idea why this is - but SW2003 might not behave this way. I hope it helps
Consciousness: That annoying time between naps.
RE: BOM item number customization?
1-x=Make
$1-$x=Purchase
$100-$x=Suspension Purchase
Thanks again for all your help,
Lori
RE: BOM item number customization?
1-x=Make
$1-$x=Purchase
$100-$x=Suspension Purchase
Thanks again for all your help,
Lori
RE: BOM item number customization?
If I understand you correctly – all that you need is to add an additional column to contain a running count for your S Category. I added it between the C & D columns to make it a little easier to do the formula in Column A – so I will include all of the formulas
This is what I did. I made a copy of my standard BOM Template and added 3 columns to it so that:
Col-A is the original ITEM NO.
The label for Col-B is P/M/S and is named PMS
The label for Col-C is PCnt
The label for Col-D is SCnt
The label for Col-E is MCnt
Columns C & D & E are used to contain a running count and should be hidden before insertion
Cell C2 contains the formula =IF(B2=”P”,1,0)
Cell D2 contains the formula =IF(B2=”S”,1,0)
Cell E2 contains the formula =IF(B2=”M”,1,0)
Cell C3 contains the formula =IF(B3=”P”,1+C2,C2)
Cell D3 contains the formula =IF(B3=”S”,1+D2,D2)
Cell E3 contains the formula =IF(B3=”M”,1+E2,E2)
Select Cells C3 & D3 & E3 and drag the formula down as far as you need them
The formula for Column A is harder because it needs to test 3 values instead of 2
Cell A2 contains the formula =IF(B2=”P”,CONCATENATE(“$”,C2),IF(B2=”S”,CONCATENATE(“$”,100+D2),IF(B2=”M”,E2,””)))
Select Cell A2 and drag the formula down as far as you did Cells C3 & D3 & E3
You can now test the formulas by putting a “P”, “S” or an “M” in Column B
Consciousness: That annoying time between naps.
RE: BOM item number customization?
OOPS – That makes the first S value = $101 and not $100 – Change the 100+D2 to 99+D2
Consciousness: That annoying time between naps.
RE: BOM item number customization?
I used them to automatically put length dimensions in the SolidWorks BOM’s. We model in inches and the formulas will change it to FT and add the FT when required. Excel does not add FT if “unit of measure” is EA.
Now for the question: After placing the excel BOM on the drawing, no text comes in under Qty. This is the column that has the formula.
=IF(H2="FT",CONCATENATE(FIXED(J2/12,2)," ",H2),B2)
If I open the excel BOM and close it, then the lengths will show up. How can I get excel to calculate the cell upon BOM insertion? I have set the Automatic setting within excel on the template. Still does not help.
Bradley
RE: BOM item number customization?
You can over-ride the value that SW places in a Column. This is what I did by placing a formula in the Item Number Column (A). The problem is that when you do that the information is gone. Normally – you do not want to do that. What you want to do is have the column that SW uses hidden and display a different column that contains your formula.
Part of the problem with all of this is that some of the information that SW places in the BOM doesn’t seem to be available to Excel until after the BOM is inserted. IE – SW inserts the BOM – Excel does it’s thing – and then SW updates the BOM – As a result – You have to edit the BOM as soon as it is inserted for some of your formulas to update correctly.
While this can be annoying – a larger problem comes into play when the Columns are not properly Named or the Custom Property has a Name that is not valid in Excel – like “Part No.” – SW side steps this by using “partno” as the Custom Property which is a valid Name in Excel and then displays “Part No.”
When you have a Custom Property like “Drawn By” placed in a column in Excel (Not a valid Name) – SW will still try to stuff the data into the Column but the results are sometimes unexpected – the data may not be visible while editing the BOM or the BOM has to be opened for editing before the changes take place.
All of this really blows the automatic updating feature away – SW still updates the BOM but Excel has not had a chance to make the changes until the BOM is edited again.
Now to specifics – I’ve done most of this before – what I did was to have 2 Qty columns (1 for SW & 1 for display), and a SpecialQty column. A formula in the displayed Qty column tested to see if a value existed in the SpecialQty column and – IF NOT – displayed the SW value by default – This allowed all of our existing parts to work in the new BOM without any changes. – When there was a values in the SpecialQty column that value was displayed instead.
This allowed us to insert empty models containing the SpecialQty = A/R for Glue / Locktite / Etc. With parts that were cut to length a SpecialQty = 6 IN. or 6 FT. was used
I hope this helps. If you are still having problems - send me an email with your spreadsheet attached and I will see what I can do. If you do, please give me a list of the Custom Properties that are normally used in your models. LeeB34@Cox.net
Consciousness: That annoying time between naps.
RE: BOM item number customization?
Thank you for your help. I have enclosed the excel files and drawing. I have done the same as you suggested for SpecialQty column. Although I used column “H” called UoM. If “FT” is in column J then excel puts in a value. If not then SolidWorks put in a value. Still did not work.
Bradley
RE: BOM item number customization?
Lori
RE: BOM item number customization?
I believe that I see the problem. I am returning your spreadsheet with the changes I made by e-mail. The BOM originally displayed:
Item No Qty
1- 4.35 FT
2- 1.21 FT
3- 6.23 FT
4- 3
5- 2.00 FT
All of that was as it should be – with the exception that the quantities are not correct. The problem is that you are not using the original Quantity that SW provides. Each part specifies a length but you are not multiplying that length by the quantity.
I used the Column I as a running total with the formula =B2*J2 in Cell I2 and stretched it down – the formula in your Qty column should then point to I2 instead of H2 so
=IF(H2="FT",CONCATENATE(FIXED(J2/12,2)," ",H2),B2) and should be changed to
=IF(H2="FT",CONCATENATE(FIXED(I2/12,2)," ",H2),B2)
I noticed that you do place a UoM value of “EA” on other parts. If that is standard on all of your parts and not a new addition - then you might consider changing the formula to:
=IF(H2="EA",B2,IF(H2="FT",CONCATENATE(FIXED(J2/12,2)," ",H2),””))
If it is a new addition then you could use =IF(NOT HASVALUE(H2), B2, Etc
This is not a major thing, but most of your parts will be “EA” so you should test for that first. As an additional bonus, it allows you to easily add more special tests like “IN” or “A/R” later by replacing the final “” with another IF statement.
Personally – I think that you should rethink your template. I do not mean to be critical – it is just my opinion. The reasons are:
1- You have the Headers for your BOM in the drawings template and are hiding the normal Header Text on Row 1 – so the lines may be close but will never be right – Kill the template and use Row 1 – It has an additional advantage – The template is much easier to work with.
2- You should still display the original Qty that SW provides.
3- Place your Units of Measurement in a column of it’s own next to the Quantity
4- Add a Length Column next to that for the Total Length.
Your header would look something like
| Item | Qty | Units | Total | Etc…..
I hope this helps
Consciousness: That annoying time between naps.
RE: BOM item number customization?
1) Your header name does NOT have to match the cell's variable name (i.e. File Property).
2) Do as StarrRider suggested, i.e. don't mess with the cells the SW populates. Add more columns, work your magic in them, and then hide them (your cells and/or the SW cells) if you're not interested in seeing there values in the drawing BoM.
3) I believe you can have the same header for multiple columns, as long as the cell's variable name is different. Not totally sure about that (seems like I ran into some trouble doing that), but give it a try (that is if you want a header on a column that you have equations in, that has the same header as a column populated by SW). And if it doesn't work that way, then just add a space or two after the last character/digit your header...now they're not the same
Just my $0.02,
Ken
RE: BOM item number customization?
We have two properties for material in our files, for two lines in the title block (properties named "mat1" and "mat2"). Getting these to go into a single cell in a BOM was a royal pain. The solution was to create a 3rd custom property (we call it "BOM_2COL") and assign it the following value:
=INDIRECT(INDEX(ADDRESS(ROW(),COLUMN()-2,4),1,1))&" "&INDIRECT(INDEX(ADDRESS(ROW(),COLUMN()-1,4),1,1))
That value in an Excel cell will combine the text of the two cells immediately to the left. So, if I have BOM columns with the properties "mat1", "mat2", and "BOM_2COL", the value in column "BOM_2COL" will be the combined text of "mat1" and "mat2".
In the BOM template, "mat1" and "mat2" are hidden. Also, automatic update of BOM needs to be off.
RE: BOM item number customization?
I like your way of making two lines of text in a BOM. Our approach was to put a return in the custom property. Using Visual Basic to enter the data into a dialog text box in the program. Shown as follows:
NewLine = Chr$(13) & Chr$(10) ' Line Feed or Enter
RetVal = Part.AddCustomInfo("Title1", "Text", UCase$(txtSfDrawingTitle.Text & NewLine & txtSfDrawingTitle2.Text))
Bradley
RE: BOM item number customization?
I don’t mean to criticize - but I do have to say - That is the most long-winded method for writing a Concatenate Statement that I have ever seen
I looked at it for a couple of minutes and then started taking it apart
Indirect – Returns the reference specified by a text string
Index – Returns a value or the reference to a cell from within a table or range
Address – Creates a cell reference as text, given a specified row and column numbers
& – This is Not listed in Fx - BUT
Adding =B2&C2 to cell A2 does exactly the same thing as Concatenate
You can add as much junk as you like =B2&” – “&B3 creates B2Junk – B3Junk
I Like that – That is a 100% Pure GOLD Tip (another Star from me) - I didn’t know the VB “&” worked in a cell’s formula – It make me wonder what else does
But I would like to know – Why are you using Indirect, Index, and Address?
Address is normally used when converting an unknown number to a cell reference like BC3
Indirect and Index are normally used when the range being combined is unknown to the programmer
Lee
Consciousness: That annoying time between naps.
RE: BOM item number customization?
Another possible scenario is having more than one set of pairs that needs to be combined. INDIRECT only looks at the contents of the neighboring cells, not at the properties in the SW file. We do have multiple properties that are broken into pairs like our material-related properties.
Actually, I like Bradley's recommendation. I think I will toy with it and see what comes.
RE: BOM item number customization?
I have to agree with TheTick – You managed to slipped your post in there while I was busy changing colors a dozen times and I missed it. – A really Good Trick – worthy of a Star
TheTick
Excuse me - Indirect does not combine anything – the “&” that you are using in your formula does the combining
Indirect returns a Cell’s Address from a text string – An address like B2 – All that the three statements do when combined like this is to make that address relative to the current cell
You don’t need to use Indirect to guarantee that your formulas stay valid - A simple formula like =B2&C2 is automatically converted to =B2&D2 when a column is inserted between them
In the second instance – I don’t understand what you are driving at. It is possible to write a macro and store it in Excel that can access the properties in the SW file – but a formula cannot do that. A formula can only access a cell or a range of cells. SW dumps the data into the spreadsheet and then Excel work on it.
I don’t mean to offend - but I don’t see how
=INDIRECT(INDEX(ADDRESS(ROW(),COLUMN()-2,4),1,1))&" "&INDIRECT(INDEX(ADDRESS(ROW(),COLUMN()-1,4),1,1))
is any better than =B4&” “&C4
To me at least – Because everything is relative in your formula – it is harder to understand. If you place your formula in Cell D4 and the formula =B4&” “&C4 in Cell E4 – They would both produce the same result but later formula makes fewer function calls and would execute faster
Lee
Consciousness: That annoying time between naps.
RE: BOM item number customization?
The "BOM_2COL" property method (forgive me if calling it the "INDIRECT" method was confusing) allows for a custom property carried within SW to be used to combine any two property strings with the use of another single property. This allows for the BOM to come in intact with zero editing by the user.
As Kerry Livgren would say, "One of many possible musicks."
I think this is one of the best threads I've seen in a long time. Many people contributed many useful items w.r.t. how Excel behaves with SW.
RE: BOM item number customization?
What? I am missing something big time here. I checked the SW-API help file (SW 2004 Beta) for Bom_2Col and it came up empty.
Oh yes – Who is Kerry Livgren? I am terrible with names – they slide out of my head faster than water flows off a ducks back.
Lee
Consciousness: That annoying time between naps.
RE: BOM item number customization?
I don't think SW is so enamored with my work that they would put it in their help files.
Who is Kerry Livgren?
http://www.numavox.com
http://www.kansasband.com
RE: BOM item number customization?
Ahhhh – The Light of day
Lee
Consciousness: That annoying time between naps.
RE: BOM item number customization?
Thank you so very much for your help. I have finally completed our automatically generated BOM. Our goal is not to manually text edit the excel BOM. I did rework the template like you suggested. We do not hide the Header in Row 1 anymore. That has helped us 10 fold. We have had problems putting in zero qty, at item zero. Not anymore, it works great. We are now checking for EA 1st. Once Documentation found out what could be done, they went wild with the requests. I do not agree with some of them. Here is the formula in qty column.
=IF(H2="EA",B2,IF(H2="FT",TEXT(I2/12,".0 FT"),IF(H2="NA",TEXT(,"0"),IF(H2="QTY",TEXT(J2,"0"),IF(H2="2PL",TEXT(J2,".00"),B2)))))
A synopsis of what we have now.
1. FT Feet - Wire, tubing, hose, tape and etc.
2. EA Each - Purchased as “each” e.g. nuts, fuses, computers and etc.
3. 0 Zero - The number zero for referenced items e.g. Do Not Use.
4. QTY Quantity - Put 1 part in Assembly, QTY can be 437 parts.
5. 2PL % - Percentage of Each e.g. Ducting.
Bradley