BOM item number customization? 4

[diamondcat]

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

 

[jbknudsen]

Are you using the "insert bom" option?
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

 

[diamondcat]

I am using the Insert BOM option.
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.

 

[jbknudsen]

I think I can solve the problem, but I dont think I can explain how to fix it. In short, by modifying the bom temlate, the "$" should be configured to appear before the number, again I am not possitive, I will tinker around with it and see what I get. I am hopful that I will have some good results to relay.

In the mean time, any helpful advice is welcome from all the other users on this matter.


Back with ya soon,

Jay

 

[jbknudsen]

Yes, I have modified an existing assembly bom. In excel I have insert a new column "A". I renamed the column "status". I saves the bom and then opened a few existing parts in an assembly and modifed the custom properties to include "status" as the name and "$" as the value. I opened a new drawing and placed the assembly in the drawing and then inserted the modifed bom. The "$" only shows up infront of the item number that has the "$" assigned to it. The rest of the items are listed as normal.

If you want I can send you a scr shot of the bom and/or the modified assembly excel file.

Jay

Jay@dicarinc.net

 

[TheTick]

I tried playing with the format of the Excel cells when editing the BOM. The effect is temporary and is wiped out with the next rebuild.

I may make you feel, but I can't make you think.

 

[jbknudsen]

Yes, that is true if the edits are done at the drawing level, I.E. clicking on the bom in the drawing and as a result launching excel. If the spreadsheet is formatted prior to being inserted into the drawing, the bom modifications are permanent. Meaning for diamondcat, he would have to have an assembly bom specifically for his client wanting the "$" in front of purchase items.


-Jay

 

[StarrRider]

diamondcat

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

Lee

Consciousness: That annoying time between naps.

 

[diamondcat]

Your formulas work really well, thanks! I really appreciate all of the effort you put in, I would have never came up with that. I ended up adding a third option of 'S'. My customer likes their purchase suspension items to have a dollar sign in front of them also but they need to start with $100. Do you know how I can add this so that any items marked 'S' start from $100 and go up? I used the same formula =IF(C2="S",1+F2,F2) and tried making it ..."S",100+F2,F2) but then my numbers went up by 100. My numbers need to go like this:
1-x=Make
$1-$x=Purchase
$100-$x=Suspension Purchase

Thanks again for all your help,
Lori

 

[diamondcat]

Your formulas work really well, thanks! I really appreciate all of the effort you put in, I would have never came up with that. I ended up adding a third option of 'S'. My customer likes their purchase suspension items to have a dollar sign in front of them also but they need to start with $100. Do you know how I can add this so that any items marked 'S' start from $100 and go up? I used the same formula =IF(C2="S",1+F2,F2) and tried making it ..."S",100+F2,F2) but then my numbers went up by 100. My numbers need to go like this:
1-x=Make
$1-$x=Purchase
$100-$x=Suspension Purchase

Thanks again for all your help,
Lori

 

[StarrRider]

diamondcat

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 (I simply couldn’t resist that)
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

Lee

Consciousness: That annoying time between naps.

 

[StarrRider]

diamondcat

OOPS – That makes the first S value = $101 and not $100 – Change the 100+D2 to 99+D2

Lee

Consciousness: That annoying time between naps.

 

[Standing]

Thank you Lee, for your formulas.
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

 

[StarrRider]

Bradley

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

Lee

Consciousness: That annoying time between naps.

 

[Standing]

Lee,
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

 

[diamondcat]

Thanks alot Lee, the formulas work great!
Lori

 

[StarrRider]

Bradley

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

Lee

Consciousness: That annoying time between naps.

 

[brengine]

A couple other things to keep in mind:
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

 

[TheTick]

One more related bit of Excel/SW voodoo...

We have two properties for material in our files, for two lines in the title block (properties named "mat1" and "mat2&quot. 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&quot 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.

Good and evil: wrap them up and disguise it as people.

 

[Standing]

TheTick
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