Here we use an Excel Spreadsheet to calculate our Cutlist and the information is inserted manually.  What I suggested we try to do is create a Part Template file and use the Custom Properties that are already in SolidWorks rather then having someone retype Descriptions, Part and Assembly information.  Plus use the Bounding Box information to update our Cutlist accordingly, so that when we choose certain custom properties that the length and thicknesses are calculated automatically.  All of our Panels are processed in our Rogh Mill, where they cut the boards to length and rip the boards to width, run them through a Glueing Machine and then they put them in a Clamp Carrier to dry.  To process the order they need the finish size of the panel and they add 1/2" to the width and 3/4" to the length, so the Cut List spreadsheet was supposed to take the inserted size and calculate the final cut size to our Mill Department, plus panels need to be a little thicker for final sanding.

 

We do have (9) Part ID designations and depending on the choice it could change the final thickness, width and length, plus we take the Bounding Box thickness and add 1/4" and then divide then total by 1/4" and round up one, this tells the Mill Dept which Quarter Size of Lumber they need to start with.

 

So my question is...

 

Can I setup a Part Template file with all those formulas in the Custom Tab in the Custom Properties - see the attached Sample Part, (check the Custom Properties) and the Excel Spreadsheet that the one gut was putting together here.  The spreadsheet needs additional work to fully function, but the formulas are there....

 

Attached is a sample part, the excel spreadsheet and the Custom Property Tab Builder file that I use.

 

Thank you in advance for taking a look..


Categories: API∕Macros

Comments
Last comment By: John Stoltzfus   Fri, 02 Nov 2018 13:39:50 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

That is great for simple calculations, but here we have 9 Part ID's and two other select items and if a certain Part ID is selected they have an adder for the thickness and length, but if I choose PURCH witch is a purchased item then we want the other items blank etc...  so there are what "IF" statements needed.  Plus the goal would be to have everything in the Part Templates so when we start with a new product our Cutlist info would be set for that part..

By: John Stoltzfus  Tue, 14 Aug 2018 13:32:05 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Would or could this be something that DriveWorks can handle?

By: David Matula  Tue, 14 Aug 2018 13:38:42 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

You can add variables for different Part ID: ID1_Add_Length, ID2_Add_Length etc. and designer use the correct one in Property.  I guess they know what the part is when they model it.

They can leave the property blank if not needed.

 

Macro need to be run every time model is changed.  Boundary box need to be updated.  Macro/Addin need to watch part update/change events and update properties.  It will slow down everything.

By: Frederick Law  Tue, 14 Aug 2018 13:58:34 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab
By: John Stoltzfus  Tue, 14 Aug 2018 14:47:58 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

I'd love to take a look, just as well that it's probably out of my league anyway!

 

Dave.

By: Dave Bear  Wed, 15 Aug 2018 07:40:33 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Nice job John!

By: Vladimir Urazhdin  Wed, 15 Aug 2018 18:24:01 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

looks like your getting there.  Good luck.

By: David Matula  Wed, 15 Aug 2018 18:56:33 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Ok – here is a little twist to the tale, I noticed a post by Igor Fomenko - https://forum.solidworks.com/message/881163 and that is the direction that I’ve gone but that has issues which I’ll try to explain below.

 

The “Ultimate Goal” is to create an equation right in the Custom Property Tab here

 

 

Not here (the Configurations Specfic Tab)

 

 

 

So what I did was saved an Excel File with the formula that we are using manually and having that open off to the side. Then I opened a new part and just drew a rectangle and extruded it, then I added the Bounding Box and a Design Table.  I copied all the stuff over from the Excel spreadsheet and closed it and did a rebuild and what happens is all of the Design Table data return goes directly to the Configurations Specific Tab and not the Customs Tab.  Sending all the information there creates issues for me downstream in our workflow, because number one, the Task Scheduler doesn’t allow you to update Configuration Specific properties, only custom properties in the Customs Tab, plus my main macro would need to be changed and I want to avoid that totally.

 

What I want is to be able to apply the formula required right in the Custom Tab and in my part template, the question is where do I calculate that information?  I didn’t try the equations yet, that is another option.

 

I was able to set it up using the design table, however the information return was only a two decimal place number instead of a three decimal place number..

 

So what I need is…… When I extrude a part or loft or whatever, the Bounding Box returns Thickness, Width and Length and I can use those values to create an automatic return for our Cutlists, we have one called Mill and another called TSaw and it makes a difference if the material is a PANEL, or a S4S RAZ or PLY etc, the thickness also changes if it’s a RAZ or Panel etc, this leaves enough of material for sanding after certain operations. The PART ID needs to drive most of those returns

 

Anytime you use a design table this way you can run into rebuild issues, where changes are made and the rebuild doesn’t work correctly, unless you edit the Design Table…

 

 

 

 

I really want to make the changes here (if needed) - Custom Property Tab Builder

 

 

 

 

By: John Stoltzfus  Wed, 15 Aug 2018 18:12:16 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

I don`t understand the situation clearly but

Why don`t you create several templates, one for each type of part?

or

Why don`t you use equations (may be in file) to link them to properties?

By: Igor Fomenko  Tue, 21 Aug 2018 16:16:14 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Igor Fomenko  wrote:

 

I don`t understand the situation clearly but

Why don`t you create several templates, one for each type of part?

or

Why don`t you use equations (may be in file) to link them to properties?

Igor Fomenko

 

That is exactly what I want to do, I've tried to take the Excel formula  and change the information like the Cell names to SW Custom property names...

 

Example - L3 (to SW-Bounding Box Thickness) etc.  but that don't work.  So basically what I want is if the Part ID custom property is PANEL then I need to add .004" to the thickness for final sand and if the Part ID custom Property is S4S RAZ then I need to add .031" to the thickness for final sand, otherwise everything else returns the actual SW-Bounding Box Thickness.

 

The Quarter Size reads the SW-Bounding Box Thickness and adds 1/4" or .250" and divides it by .25 then rounds it "Up" to the nearest 1, so a finish board being 1.125" it takes the 1.125 and add .25 which makes it 1.375" thick and then when you divide by .25 it returns 5.5 so when you round up it would tell our Mill Department to use 6 Quarter Lumber to start their process.

 

The formula from Excel - Quarter Size cell is below and the Part ID choice - PLY ASM SQUARE MOLD or PURCH and the value return is 0.

 

=IF(L3>0,IF(OR(B3="PLY",B3="ASM",B3="SQUARE"),0,IF(OR(B3="MOLD",B3="PURCH"),0,IF(L3>=0.8125,IF(MROUND(L3+0.1875,0.25)/0.25=7,8,MROUND(L3+0.1875,0.25)/0.25),4))),"")

 

I've tried to replace the L3 with SW-Bounding Box Thickness and the PLY and other Part IDs' with Part ID "PLY" etc and no success, which isn't surprising for me because I don't do formulas very easily, but show me one that works and I think I can take it further. 

 

The ultimate plan is to have these formulas in my Part Template file so that when I extrude the model the Cutlist is starting to form..

 

Thanks

 

John

By: John Stoltzfus  Tue, 21 Aug 2018 16:49:09 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Function.jpg

This might help.

By: Frederick Law  Tue, 21 Aug 2018 17:01:16 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

John Stoltzfus

I suggest to create a template with all formulas in the equations so the model will be automatically updated accordingly. then you can use a simple macro to link the equation to the CusProp

EDIT: I should read thru all the messages, @igor already suggested it to you

By: Christian Chu  Tue, 21 Aug 2018 17:11:43 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Christian Chu - That's what I'm missing - I'm not good with equations and how they work, but I can edit one after it's already in place.  As soon as I have time I will see what I can do, but I'm not sure how to call SW-Bounding Box Thickness

By: John Stoltzfus  Tue, 21 Aug 2018 17:17:48 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

John Stoltzfus wrote:

 

Christian Chu - That's what I'm missing - I'm not good with equations and how they work, but I can edit one after it's already in place. As soon as I have time I will see what I can do, but I'm not sure how to call SW-Bounding Box Thickness

Basically, most of the equations from Excel can be transferred to the SW equations

By: Christian Chu  Tue, 21 Aug 2018 17:37:01 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

OK John....I did a little test.

 

It turns out that you can assign your custom properties a type.  So if you assign your PartID the type "Number", then you can use that Custom Property in your Global Variables and in equations inside Global Variables.

 

So, here is my suggestion:

Instead of a drop down menu in your Property Tab Builder, have the PartID be a set of Radio Buttons. 

 

Then, inside your Custom Proerties, assign the PartID type to be "number"

 

Now, you can use this in a Global Variable equation.

 

In my part, I have a Custom Property that is assigned using the Property Tab Builder called "NumberRequired".

 

Then, in my Global Variables, I have this:

 

Then, I created a line and a dimension for its length and made it equal to Variable Test2.  Then went into my Property Tab builder and started changing the number:

 

Once I change the number....after a rebuild, it updates the line.   I assume that you can do something similar. 

 

So the key behind using the Custom Property field to assign your property to be a number and then use that number in your global variable equations.

 

As far as Changing SW-Bounding Box Thickness.....

Isn't that driven by a dimension in your model already?   Why not just use your equation to update that dimension....?

By: Dan Pihlaja  Tue, 21 Aug 2018 17:46:16 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Thanks, @Dan

I was not aware of the feature which you can link CusProp to equations without using API for it

Just a new thing to learn today !

By: Christian Chu  Tue, 21 Aug 2018 18:01:57 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

John Stoltzfus wrote:

 

Dan Pihlaja - My head hurts - you see there are things I need to "See" then I can get it, but it has to be close to what I need etc and then a light will light and I'm on a roll, till then my mind is a fog for stuff like this. I could never figure out a riddle, I doesn't matter how simple and I think that is part of my issue.

 

Initially I did use the Radio Buttons, then I replaced it with a List and a dropdown to select the choices, I think it is because with the Radio Buttons you can overwrite the values...

 

The final Goal is to have everything within the Part Template file and go from there.

 

There is one thing that I noticed that is different between Excel and SW equations and that is; In the Excel formula everything is tight together, where in the SW equation line, there are spaces and if someone could give me specific direction there, I think I could get it, plus how do you call, like the SW-Bounding Box Thickness??

You can still overwrite the values (if you go into Custom Properties and do it) even with a drop down menu.  Its just that, when you hit apply again in the property tab builder, then it brings it back to what the builder is set to.

 

Let me try something.....stay tuned

By: Dan Pihlaja  Tue, 21 Aug 2018 19:00:45 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Dan Pihlaja - My head hurts - you see there are things I need to "See" then I can get it, but it has to be close to what I need etc and then a light will light and I'm on a roll, till then my mind is a fog for stuff like this.  I could never figure out a riddle, I doesn't matter how simple and I think that is part of my issue.

 

Initially I did use the Radio Buttons, then I replaced it with a List and a dropdown to select the choices, I think it is because with the Radio Buttons you can't overwrite the values...

 

The final Goal is to have everything within the Part Template file and go from there.

 

There is one thing that I noticed that is different between Excel and SW equations and that is; In the Excel formula everything is tight together, where in the SW equation line, there are spaces and if someone could give me specific direction there, I think I could get it, plus how do you call, like the SW-Bounding Box Thickness??

 

Dan Pihlaja - I had said "can" overwrite the values, should be "can't"....

By: John Stoltzfus  Tue, 21 Aug 2018 18:21:39 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

OK, look at the attached part.

 

From Property Tab Builder - 2 Properties defined.

 

User sees this:

 

 

Creates these Custom Properties inside the part:

 

Equations from the part:

 

So, save this part, as is, as a part template.

 

When you use this part as a part template, you can open the Custom Properties Tab from the Task Pane and type in your Finish board thickness....then select the Part ID.   If the Part ID is "PANEL", then .004" is added to the thickness of the board.  If it is "S4S RAZ", then 0.031" is added to the thickness of the board.   If it is anything else, then 0" is added to the thickness of the board.  (all after an update, of course).

 

This si all taken care of inside the part template and no links to outside files is created.

 

Maybe this will help you?

By: Dan Pihlaja  Tue, 21 Aug 2018 19:46:17 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Dan Pihlaja - That is what I'm looking for - something inside the part file that is an automatic calculation... Unfortunately I'm not at work right now, so tomorrow is going to be the test.  Within the cutlist there are other equations that I need to figure out as well. 

 

Thank you for your time on this

By: John Stoltzfus  Tue, 21 Aug 2018 20:22:18 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Dan Pihlaja wrote:

 

 

When you use this part as a part template, you can open the Custom Properties Tab from the Task Pane and type in your Finish board thickness...

 

My workflow is mainly to Extrude Up To either a point, line or Plane, there for in my part files there are no dimensions shown when you double click on a sketch, so I wouldn't have that option..

 

The process thought was to use the Bounding Box returns, such as length, width and thickness. 

 

By: John Stoltzfus  Wed, 22 Aug 2018 11:40:19 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

OK, so I am not sure how to grab the bounding box returns in excel.....BUT you wouldn't be able to modify them anyway, would you?  Those values are driven by other things....like sketches and whatnot.

 

So let me get this straight in my head:

Correct me if I am wrong

You want to do the following:

1) Have a part that is controlled by an external sketch (pursuant with the skeleton sketch method)

2) Insert a bounding box for that part

3) Use the bounding box values to drive criteria (formulas) inside a design table or the global variables, or wherever, in which a check is made on the PartID and the Thickness of the part.

4) Once the check is made, you want to add .004" (for example) to the thickness of the part, if that PartID is labeled as Panel

5) However, your dilemma is that, since the part size is driven from outside the part, and the bounding box is driven by the part size....you can't just edit a dimension to change thickness....you would either have to edit the external dimension (not impossible...but would require some serious finagling) OR you would have to add material to the part.

 

As far as using the Bounding box thickness in an equation or a custom property.....I don't think that it can be done.

I didn't test inside a design table.  Maybe you can.  Here are some Syntax's that I found...that may work (I tried both of these in a BOM, inside equations and inside Custom Properties with no luck):

SW-Thickness

$PRPWLD:"3D-Bounding Box Thickness"

 

To show you where I found those syntax's....

SW-Thickness, I found here:

 

And the other one.....apparently you can link the thickness inside a note:

By: Dan Pihlaja  Wed, 22 Aug 2018 12:30:20 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Can you open 2018?

 

The way it works right now is adding a design table into the part file, also insert the Bounding Box which will return these values -

 

 

 

This how I'm doing it at the moment, but would have liked to have everything in the Equations, because I think the rebuild issues that you create using Design Tables, would be gone..

By: John Stoltzfus  Wed, 22 Aug 2018 13:41:06 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

I cannot open 2018.  I am on 2017

By: Dan Pihlaja  Wed, 22 Aug 2018 14:10:43 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

My approach to capturing this kind of company-specific knowledge is to create an add-in.

 

That way you don't need an external Excel file, a custom property tab builder or a design table and you don't have to mess with these long and unclear Excel formulas.

By: Peter Brinkhuis  Wed, 22 Aug 2018 14:31:29 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Peter Brinkhuis - I would love to have something like that - but that's asking a lot from a Hick in the Sticks - I know there are a lot of better ways to do things, however this stuff is just best being figured out by people that really know how.  I enjoy doing simple Excel formulas, but today's 2nd graders are probably better then me.  Oh well, I got my needs covered temporarily, eventually someone will have a perfect solution.  Attached is a Screen Shot video that I took showing what I need to do with every part and every change, really, it's pretty lame.  I know this isn't a long term solution, therefore I don't have any issue using it for the time being..

By: John Stoltzfus  Wed, 22 Aug 2018 15:19:44 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

John Stoltzfus wrote:

 

Peter Brinkhuis - I would love to have something like that - but that's asking a lot from a Hick in the Sticks - I know there are a lot of better ways to do things, however this stuff is just best being figured out by people that really know how. I enjoy doing simple Excel formulas, but today's 2nd graders are probably better then me. Oh well, I got my needs covered temporarily, eventually someone will have a perfect solution. Attached is a Screen Shot video that I took showing what I need to do with every part and every change, really, it's pretty lame. I know this isn't a long term solution, therefore I don't have any issue using it for the time being..

For sure my kids know iPhone's features  better than me

By: Christian Chu  Wed, 22 Aug 2018 15:33:05 GMT
Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Here we use an Excel Spreadsheet to calculate our Cutlist and the information is inserted manually.  What I suggested we try to do is create a Part Template file and use the Custom Properties that are already in SolidWorks rather then having someone retype Descriptions, Part and Assembly information.  Plus use the Bounding Box information to update our Cutlist accordingly, so that when we choose certain custom properties that the length and thicknesses are calculated automatically.  All of our Panels are processed in our Rogh Mill, where they cut the boards to length and rip the boards to width, run them through a Glueing Machine and then they put them in a Clamp Carrier to dry.  To process the order they need the finish size of the panel and they add 1/2" to the width and 3/4" to the length, so the Cut List spreadsheet was supposed to take the inserted size and calculate the final cut size to our Mill Department, plus panels need to be a little thicker for final sanding.

 

We do have (9) Part ID designations and depending on the choice it could change the final thickness, width and length, plus we take the Bounding Box thickness and add 1/4" and then divide then total by 1/4" and round up one, this tells the Mill Dept which Quarter Size of Lumber they need to start with.

 

So my question is...

 

Can I setup a Part Template file with all those formulas in the Custom Tab in the Custom Properties - see the attached Sample Part, (check the Custom Properties) and the Excel Spreadsheet that the one gut was putting together here.  The spreadsheet needs additional work to fully function, but the formulas are there....

 

Attached is a sample part, the excel spreadsheet and the Custom Property Tab Builder file that I use.

 

Thank you in advance for taking a look..

By: John Stoltzfus  Tue, 14 Aug 2018 11:33:11 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

An update.....

 

I'm not where I want to be yet, however I have finally found a solution that work pretty good and what I did was to modify the Excel Macro that was written by Markku Lehtola, by adding our Cutlist formula to another sheet in the macro.

 

First I needed to have the bounding box in every part, the bounding box information returns in the Configurations Tab in the Custom Properties and this macro only reads the default custom properties (from the Customs Tab).  So I needed to add custom properties to the customs tab which reads the bounding box info, that way this macro reads the Customs Tab and inserts them into the Excel Document, then I read those returns in the cutlist page and return that information to the first page.

 

See the attached file...

 

The optimal way is to have this happen right at the part level without running the macro, I would like to have the cutlist information happen as soon as I extrude a part, then it would have to be in the Equations Manager..

By: John Stoltzfus  Tue, 23 Oct 2018 11:42:56 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

I finally got to figure out a work around and now I can consistently create accurate cutlists

 

 

This BOM gets saved out as an excel file.

 

The only thing with our process is that for every change I need to re-run our excel macro, which reads the Bounding Box information and then I have to make sure it processes in the spreadsheet by copy and dragging the formula again and then I need to write it back to the file..

 

We need this to happen

 

Enable Excel Formulas in Global Variables and The Equation Manager -  Voting Starts 12/13/2018

By: John Stoltzfus  Fri, 02 Nov 2018 12:14:59 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

Okay John,

Drop the attached template in your templates directory.  Create a box and extrude it using that template and add reference dimensions as stated in the note. If you create them in the order listed Width, Thick (can't use Thickness, it's reserved) and Length get automatically populated in your Custom Properties.

I did this by creating a box inside the template and then linking RD1@Annotations, RD2 and RD3 to the Custom Properties.  Then I deleted the box.  But, SW always assigns RD1 first then RD2 and so on.  So, I took advantage of that.

Does this help?

By: Matt Peneguy  Fri, 02 Nov 2018 12:45:57 GMT
Re: Inserting Formulas/Equations in Custom Property Tab Builder - Custom Tab

The only issue I can think of is - What is the direction of RD1 - RD2 - RD3 - is it XYZ or ZYX, which is important for us.  The bounding box is extremely good in the designations for the Thickness Width Length etc, and the way we have it set up that portion works really well. 

 

I have the Bounding Box in my template and as soon as I hit enter on the Boss Extrude the values are there and automatically update, which would be the same as what you have explained....

 

All that information is good, but I need to develop the cutlist depending on three inputs, these three custom property values get copied into Excel and run through a few different what if formulas, nothing that complicated, but I still need to throw it into Excel.  If we could put the formula in the Equation Manager, end of story for us, as soon as I pick out the 3 three inputs my cutlist for that part is automatically calculated..

 

Our spreadsheet formula adds material thicknesses and lengths, or does nothing, depending on the choices that pick... 

By: John Stoltzfus  Fri, 02 Nov 2018 13:39:50 GMT
You are not authorized to view this page No results found! Suggestions: Check spelling, try a different search, or browse topics below.