AnsweredAssumed Answered

Need HELP w/Equations in BOM

Question asked by Jake Everett on Sep 20, 2012
Latest reply on Sep 21, 2012 by Daen Hendrickson

Like • Show 0 Likes0
Comment • 24

It has been almost 4 years now since I first asked this question. I'm hoping there may be someone with an answer by now...

I have a SW BOM in which I have three columns that display X_DS, Y_DS & Z_DS (x, y & z values of a part)

I want to combine the three columns into one column called "FINISHED SIZE" that would display a callout something like this: .375 x 2.250 x 4.000 (smallest no. first & largest no. last) Here's the kicker, X_DS=2.250, Y_DS=4.000 & Z_DS=.375 BUT sometimes the values may be different, ie. X_DS=.375, Y_DS=4.000 & Z_DS=2.250....... I always want the value in the "FINISHED SIZE" column to display as (small # x medium # x large #)

Any help is appreciated

Alin Vargatu Sep 21, 2012 2:31 PM

Correct Answer

Try this one. If you like it, please save the BOM as a template.

See the reply in context

No one else had this question

Outcomes

24 Replies

Alin Vargatu Sep 20, 2012 1:48 PM
Mark Correct
Correct Answer
Aha, lots of ifs and (if cannot nest them properly) possible need to add a few dummy columns.

Give me until the end of the day (if no one else provides a solution until then). A bit busy at the moment.
Like • Show 0 Likes0
Actions
Jake Everett @ Alin Vargatu on Sep 20, 2012 1:52 PM
Mark Correct
Correct Answer
Hey Thanks Alin for looking... I have no idea on this.. I'm don't pretend to be a programer in any fashion although I did know that I may need more columns and then hide them afterwards... thanks again
Like • Show 0 Likes0
Actions
Daen Hendrickson Sep 20, 2012 3:24 PM
Mark Correct
Correct Answer
Jake,

Here is one approach. Note, this requires a rebuild after the dimensions are changed to get the description to update. Also, the formatting drops trailing zeros. Perhaps someone else can address that if it is an issue.

Summary:

I created custom properties set to the dimensions. I created equations with nested iif() statements to compare the custom properties and assign them to variables for baby bear, mama bear, and papa bear. These are broken into three different equations more for clarity that necessity. I then use a VBA statement to concatenate the three variables and update the combined description custom property. The VAL1 variable is just a dummy to house the VBA code.

Thanks again to Josh Brady for showing how to do the VBA in equations presentation. Check out his SWW presentation on the subject.

Hope this helps. I look forward to the much more simplified approach Alin will cook up!

Daen

Attached part in SW2011 SP3 format.
Attachments
- Changing Size Description.SLDPRT.zip35.5 KB
Like • Show 0 Likes0
Actions
Jake Everett @ Daen Hendrickson on Sep 20, 2012 4:54 PM
Mark Correct
Correct Answer
Hey Dean... thanks for looking at this... I don't know much about doing this but I think I kinda follow what you are doing... I just need to put these equations in each of my parts and substitute Sketch1 out for whatever my base sketch is called, right? Then put them in my part template so they will be there for every part... One question though, what is the custom property that I will pull into my BOM for the Finished Size?
Thanks
Like • Show 0 Likes0
Actions
Daen Hendrickson @ Jake Everett on Sep 20, 2012 7:11 PM
Mark Correct
Correct Answer
Jake,

You are on the right track. If you followed this approach, you would want to copy the custom properties and the equations into your part template. For that matter, you might want to create a part template with the starter part already built... Not sure if that will work for your process.

And yes, you would need to repoint the custom properties to the dimensions of choice if you put them into your existing parts.

You would point your BOM to the ChangingSizeDescription property. All the other properties and equations exist to create this property.

I set the dimensions to the custom properties for clarification. You could set up your equations to point directly at the dimensions but makes for a messier text string.

The three equations are this:

Smallest_Dim:
If x < y then check if x < z. Yes? X must be the smallest.
If x < y then check if x < z. No? Z must be the smallest.
If x NOT < y then it cant be the smallest. Y or Z must be. Check if y < z. Yes? Y is smallest. No? Z is smallest.

Largest_Dim:
Basically the same logic as the smallest.

Middle_Dim: This is slightly more involved...
If x < y then it must be the middle or the smallest. Check if x < z. Yes? Then it is the smallest and we have to check if y < z. Yes? Then Y is the middle. No? Then Z is in the middle.
If x < y then it must be the middle or the smallest. Check if x < z. No? Then X is in the middle.
If x NOT < y then it must be the middle or the largest. Check if x < z. Yes? Then X is in the middle.
If x NOT < y then it must be the middle or the largest. Check if x < z. No? Then it is the largest and we have to check if y < z. Yes? Then Z is the middle. No? Then Y is in the middle.

Daen
Like • Show 0 Likes0
Actions
Alin Vargatu Sep 20, 2012 5:00 PM
Mark Correct
Correct Answer
Done! It is not as nice as Dean's and I am sure you can make it much more efficient by reducing the number of hidden columns. Did not have time to streamline the logic.

In the attached drawing, I have inserted the model dimensions. Feel free to modify those dimension directly on the drawing and rebuild after that.

Thanks a lot, Jake, for such a nice challenge.
Attachments
- xyz.zip139.9 KB
Like • Show 0 Likes0
Actions
Alin Vargatu Sep 20, 2012 5:10 PM
Mark Correct
Correct Answer
Of course, you can move the columns around so they look nice (in order).

See attached.

Also, I have to confess that I have not tested for all possibilities... but it seems to work...

The advantage of leaving the BOM to do all the work is that it does not matter which 3 dimensions you pick from the model, they will always be sorted correctly. Save the BOM template and that is pretty much the extent of your work.
Attachments
- xyz - abc.zip138.9 KB
Like • Show 0 Likes0
Actions
Daen Hendrickson @ Alin Vargatu on Sep 20, 2012 8:24 PM
Mark Correct
Correct Answer
I am still on SW2011 so I cannot view Alin's approach. I tried to create my own version of Alin's work with equations in the BOM table but ran into some of the "idiosyncrasies" of equations in tables.

Firstly (at least as of SW2011) you cannot have a nested IF() statement in the TRUE side if the parent IF() - only the FALSE side (KB S-049596). You might be able to get around this with multiple IF()s in separate hidden columns, but I did not pursue that.

Secondly, be aware that the part file version of the equation construct is slightly different than that of the drawing table version:

Part iif(test,True,False) use commas to separate
Table IF(test;True;False) use semi-colons to separate

This is only a problem if you aren't aware of the discontinuity (yeah, I'd like to have that half hour back)

These issues make the table approach cumbersome. However, the slick part is that all the logic is in the table which can be saved as a table template.

Daen
Like • Show 0 Likes0
Actions
Alin Vargatu @ Daen Hendrickson on Sep 20, 2012 9:01 PM
Mark Correct
Correct Answer
Interesting Daen. I have just discovered some of these limitations this afternoon, working on that BOM. The If bug you mentioned is present in 2012 still :(
Like • Show 0 Likes0
Actions
Alin Vargatu @ Daen Hendrickson on Sep 20, 2012 9:44 PM
Mark Correct
Correct Answer
There you go Daen. The 2011 Fileset is attached (I added a column where all the dimensions are coming together).

I guess, I was lucky that the BOM templates are compatible between versions.
Attachments
- 2011 xyz.zip124.2 KB
Like • Show 0 Likes0
Actions
Daen Hendrickson @ Alin Vargatu on Sep 21, 2012 12:44 PM
Mark Correct
Correct Answer
Alin,

Thanks for the backwards compatible post. Your approach is a great alternative.

Specifically for Jake's situation, the much easier method to implement.

Daen
Like • Show 0 Likes0
Actions
Alin Vargatu @ Daen Hendrickson on Sep 21, 2012 2:42 PM
Mark Correct
Correct Answer
Thanks for the kind words, Daen. Is too bad that the functions available in the BOM are so limited and so buggy.
Like • Show 0 Likes0
Actions
Jake Everett Sep 21, 2012 8:09 AM
Mark Correct
Correct Answer
Guys, THANK you both for looking at this for me... Y'all are so much advanced in this and I hardly know enough to ask an intelligent question.. I kinda follow both of the logics (BTW i'm still on 2011 as well) and here's my situation... I'm a tool designer (for many years on autocad) now using SW and a 3rd party die design software... This software has what's called PRL's (production ready libraries) You drop blocks, punches, plates, punch holders or whatever you wnat in and resize them or some resize automaticly... With that said, you will know that I already have many parts in these libraries.

SO.. Dean, with your approach it looks like I will have to go into the properties of every part in every library and set the X_DS, Y_DS & Z_DS with the expressions as shown and set the equations in the part. But the BOM is then simple, just pull in the ChangingSizeDescription property and you have it ....

On the other hand Alin seems to be working on the BOM itself, which would be a 1-time change for me. My only concern here is that Alin, you also set X, Y, & Z in the part properties to the initial part sketch. Is there any way to just use X_DS, Y_DS & Z_DS (since these properties already exist in every part) in the equation in the BOM?

Am I really seeing what I tried to describe?

Thanks again for the help
Jake
Like • Show 0 Likes0
Actions
Alin Vargatu @ Jake Everett on Sep 21, 2012 8:56 AM
Mark Correct
Correct Answer
Jake Everett wrote:

On the other hand Alin seems to be working on the BOM itself, which would be a 1-time change for me. My only concern here is that Alin, you also set X, Y, & Z in the part properties to the initial part sketch. Is there any way to just use X_DS, Y_DS & Z_DS (since these properties already exist in every part) in the equation in the BOM?

No problem Jake. I will update the BOM template for you. Just need to replace x with X_DS and so on. Simple editing...
Like • Show 0 Likes0
Actions
Alin Vargatu @ Jake Everett on Sep 21, 2012 9:07 AM
Mark Correct
Correct Answer
Jake, please test the attached fileset.
Attachments
- 2011 xyz_ds.zip129.1 KB
Like • Show 0 Likes0
Actions
Jake Everett Sep 21, 2012 10:02 AM
Mark Correct
Correct Answer
OK... I see that maybe I have left out a little piece of important information...
Like I originally stated "I have three columns that display X_DS, Y_DS & Z_DS (x, y & z values of a part)"

The parts are dropped into my assembly using PRL's as I mentioned in previous post... The X_DS, Y_DS & Z_DS properties are all set to (0) zero in the individual PRL part... Before I do a BOM, I run a command (in the 3rd party software) called Component Update. This sets those same properties to the actual X,Y & Z dimensions in each individual part.

That's why I was looking for help with an equation to use my existing X_DS, Y_DS & Z_DS columns to be sorted and then concatenate into a "Finished Size" column.

Does this make sense?
Like • Show 0 Likes0
Actions
Alin Vargatu @ Jake Everett on Sep 21, 2012 10:13 AM
Mark Correct
Correct Answer
Well, post a dummy assembly with your BOM and I can include the logic on it. Or you can copy&paste the equations in yours.

Your choice.

I will be away for a few hours, but if nobody helps you until I get back, I will do it for you.
Like • Show 0 Likes0
Actions
Jake Everett @ Alin Vargatu on Sep 21, 2012 12:08 PM
Mark Correct
Correct Answer
Here ya go Alin.. I think I have the file attached
Thanks again for your help
Attachments
- DummyAssy.zip214.8 KB
Like • Show 0 Likes0
Actions
Daen Hendrickson @ Jake Everett on Sep 21, 2012 1:01 PM
Mark Correct
Correct Answer
Jake,

I notice that the X_DS, Y_DS, and Z_DS custom properties in each of your parts are manually updated text - not linked to the actual dimensions that they represent. There may be conditions of your process that won't allow - but I can see a great opportunity for streamlining, removing the manual data entry, and the potential for typing error by linking those properties to the dimensions.

Doing this as each part is created from the library version isn't much of a savings. You will have to open the properties dialog and point to the dimension versus typing the text. But if you can update your library PRLs I think the effort will pay off in time.

If you decide this is something with merit, you could include the equations at the same time. They would not interfere with Alin's BOM formula approach. Then when you get your entire library updated, you could modify the BOM table template again to use the part properties.

You might do a little experimenting with this by copying some of your library to a temporary folder that can be fiddled with and then deleted. Get a sense of how much effort to update each file and make the proposal as a continuous improvement. Hopefully your third-party software does not update the library PRLs with new releases. If so, the updates would overwrite your modifications.

I also expect a relatively simple macro could be developed that would add the equations and prompt you to select the three dimensions to link.

Daen
Like • Show 0 Likes0
Actions
Jake Everett @ Daen Hendrickson on Sep 21, 2012 1:14 PM
Mark Correct
Correct Answer
Dean... Man again, you and Alin have been too good to help out with this.... Hear's the deal though on the custom X_DS etc.... Before I do a BOM, I run a command (in the 3rd party software) called Component Update. This sets those same properties to the actual X,Y & Z dimensions in each individual part. So I really don't enter those values manually..... About them being linked, I'm not sure... I do know that if I update a block in my design (say I change the size) I can run the Component Update again and it populates the BOM with the correct info.
So that should follow through to the Finished Size column I think

I hear you though.... I actually went back to the post that I had made 4 yrs ago and added another post letting viewers know that my question has been answered here in this post by two different approaches... Boy who gets the kudos when wer're finished? both of you!!
Like • Show 0 Likes0
Actions
Daen Hendrickson @ Jake Everett on Sep 21, 2012 5:31 PM
Mark Correct
Correct Answer
Jake,

FYI - If the values were linked, you would see a reference to the dimension in the custom properties Value / Text Expression cell such as D1@Sketch1. And the adjacent Evaluated Value cell would show that dimension's value.

Regardless, if your third party software updates it for you (either by adding the link or by reading the dimension values and rewriting the value in the custom property) there is no manual input. Inefficiency averted.The equations don't know how the X_DS property get updated.

I have to agree that in your situation, Alin's solution is the preferable direction to go.

Daen
Like • Show 0 Likes0
Actions
Alin Vargatu @ Jake Everett on Sep 21, 2012 2:01 PM
Mark Correct
Correct Answer
It is funny. It is much easier to add your columns on my BOM than the other way around.
Like • Show 0 Likes0
Actions
Alin Vargatu Sep 21, 2012 2:31 PM
Unmark Correct
Correct Answer
Try this one. If you like it, please save the BOM as a template.
Attachments
- Jav - Sorting BOM.zip230.9 KB
Like • Show 0 Likes0
Actions
Jake Everett @ Alin Vargatu on Sep 21, 2012 3:49 PM
Mark Correct
Correct Answer
Great job Alin and thank you again.. This seems to work perfectly. I'm anxious to give it a try on a real BOM... gotta give you the correct answer for working it out with what was given.
Like • Show 0 Likes0
Actions

Need HELP w/Equations in BOM

Outcomes

Attachments

Attachments

Attachments

Attachments

Attachments

Attachments

Attachments

Related Content

Recommended Content