Excel ipart linking

I want to share a couple of excel tricks for retrieving data from other spreadsheets with vlookup match and error handling like when reporting #NA. We have a single excel database that contains all our part numbers, prices, description, vendor and some more info. What I wanted was to link each member in our ipart table (eventually a CC item) to our price parts list and ask excel to find our part number, place it in stock number and find the price and place it in cost center.

For simplicity I have copied the price list spreadsheet to a local path next o our library folder where we have all the components we author. This way I can work on it at home and in places where the mapped network drive is not accessible. I copy the new price list each year when it changes. I only used the price in the cost center of each item to provide a default value instead of leaving it blank.

What we intend to do is export the BOM and use the same vlookup formula to find the latest prices before doing a quote.  The price we put in CC is so we can have a value / any value, rather than leaving it blank bu we intend to update the BOM export with the vlookup formula at every export.

The tricky part was finding cells based on partial data. I have the manufacturer code, Georg Fisher part number, but in our price parts list we don’t have a cell with just GF Part number instead it holds something like this: “GF REF: 729101108” or “GF REF: 729101107  DRAIN CENTRE REF. P31127”. So all I have is 729101108 and I need to use a formula that can find a match inside a larger text and return the part number and price.

Can’t use left, right, mid, and len functions because Part Number appears randomly depending on who entered this info.

The solution is to use MATCH to find part of a text like *729101108* (* used as wildcard) and INDEX to get our Part Number value.

Once I found our part number for that item I can simply use VLOOKUP to get the price since the part number is a single unique cell value.

Here is the formula to get our Part Number (we store it in the parts Stock Number ipropertie).

=IF(ISNA(INDEX('[Price List.xls]Parts Price List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price List.xls]Parts Price List'!$E:$E,0))),"",INDEX('[Price List.xls]Parts Price List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price List.xls]Parts Price List'!$E:$E,0)))

Sounds complicated? Let’s brake it into simple terms.

What we’re saying is IF the value is not found (excel returns NA) then don’t put anything in the cell (“”)and if found then put the value.

=IF(ISNA(INDEX('[Price List.xls]Parts Price List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price List.xls]Parts Price List'!$E:$E,0))),"",INDEX('[Price List.xls]Parts Price List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price List.xls]Parts Price List'!$E:$E,0)))

MATCH(("*"&U5&"*") will actually try and find the value in cell U5 (our number 729101108 with anything before or after it in any part of the text of column E in our price list. Here is the microsoft help page.

http://office.microsoft.com/en-gb/excel-help/match-HP005209168.aspx

ISNA function on microsoft’s webpage

https://support.office.com/en-us/article/ISNA-function-a6e8078e-3cf7-4f08-a041-1461e2e55205?ui=en-US&rs=en-001&ad=US

Index is used to retrieve the value of our company’s Part Number (A row) inside our price list. Here is the microsoft help page.

http://office.microsoft.com/en-gb/excel-help/index-HP005209138.aspx

The price (cost center) is much simpler.

=IF(M2="","","£"&(VLOOKUP(M2,'[Price List.xls]Parts Price List'!$A:$F,3,FALSE)))

If there is no part number (stock number) in cell M then don’t put anything but if there is a part number then add £ in front and then the value of column 3 in our price list.

Here is the VLOOKUP on microsoft webpage.

http://office.microsoft.com/en-gb/excel-help/vlookup-function-HP010343011.aspx?CTT=1

                Some experts say that the IDEX MATCH is more powerfull and faster than VLOOKUP so if possible use it at all times.

                The best explanation on how to use it and comparasson with VLOOKUP I found here:

https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/

and a couple of reasons why VLOOKUP might not work

https://www.ablebits.com/office-addins-blog/2014/08/27/excel-vlookup-not-working/

I have attached my Georg Fischer iPart excel template for your review. I am sure it’s better to see all this at work rather than trying to understand my explanation.

Here is the excel file.

Later,

ADS

Vault Project

                Vault has not been part of my CAD up until 18 months ago and everywhere I’ve been a project file for each job has been in place. Now we only use on project for entire CAD area and while I like that I don’t need to play with changing and managing projects I do miss a couple of things. It seems all I do is browser for files and it takes longer now to do nested projects so that I can create local editable CC items. I know there is global setting for placing CC as custom but I am taking about that special times when we need to generate all the tube and pipe elements locally which otherwise would go straight to default CC folder without a local generation option.

                Having one vault project will not take you to your work in progress (just latest used location), like if you had a project for each job. I can’t edit the vaulted ipj file without affecting the rest of my colleagues so the only reasonable thing to do was to create shortcuts for the most common and current projects I work on. Nothing fancy here, just good old windows shortcuts to save me from navigating folders all day.

                So when I open or place something I click on Workspace (if I am not already there) in the left side window and then on to my shortcuts for a speedy navigation.

The fastest way for creating shortcuts is to drag with your left mouse button while holding ALT pressed the destination folder into your Workspace.

                I would only recommend you to be careful when deleting shortcuts, make sure it’s just them you’re deleting.

Later,
ADS.

Merry Christmas and Happy New Year

Oh wait.... is it all over?

Where has the time gone? I've missed 2 posts for the last weeks but I was in no way able to get close to a computer to hit publish.

Christmas week came loud and clear and Monday on the 22'nd of December right after midnight I was rushing at the hospital with my wife because my son decided to come to life. He was borne in the afternoon at 18:00 and the rest of the week we spend in hospital doing all sorts of tests and checks as they do on a new one.

Second week we came home but still had the 1 in 3 hours sleep routine (sometimes even less) so very little was done. We are slowly getting back to normal; I have returned to work as well so time is scarce but I will get back with those posts.

Thank you all for the kind words, mum and baby are doing fine, and I just wanted to wish you all a Happy New Year and all the best for the one ahead.

Later,
ADS

Tube and Ppipe style error

I haven’t finished with all the tube and pipe posts, just got sidetracked with authoring fittings and decided to share the problems and solutions found along the way.

Can’t remember if this has always been a problem but in 2015 even though you follow all the known steps and procedures you don’t always end up with usable tube and pipe fittings. I have been publishing a couple of fittings for a new route style and I found that you either end up crashing inventor or they can’t be found while browsing on the new T&P (tube and pipe) style dialogue.

First thing I recommend is to check the pipe and fittings end treatment. In my case the same pipe can be but welded or socket welded while the fittings (elbows, tees) can only be socket welded or but welded. So I used jointed on the pipe and jointed on the fittings no matter what type of connections they really are. On the fittings use different engagement distances to differentiate between socket and but welding. This is to avoid warning message every time you edit the style about non matching end treatment connections. Remember I don't keep any styles inside the T&P template but import the ones needed when starting a new T&P assembly.

We don’t use ISOGEN exports but I like to have it setup just in case we will ever need to. In my ipart table I have setup ITEM-CODE and ITEM_Description columns that I have used in the tube and pipe authoring dialog. The only column that needs to be on the ipart table is ND (nominal diameter) that I use as key when publishing to content center and that Inventor uses in the tube and pipe style setup. You can add this column while editing the ipart in the custom tab or you add it while editing the ipart table with excel. I like excel because it can contain any information you can think of without messing the ipart table. As long as you leave an empty column (actually first cell of a column) between the info you want in the ipart and the rest of your data, you can have any info you like. Formulas, graphs, images, even catalog pages from the manufacturer.

Setup the ISOGEN properties and engagement correctly but leave the end treatment to jointed. I don’t have a shoulder for pipe limit stop but I have created planes that will serve as my engagement limit in the author dialog.

You need to understand that socket and but weld “End Treatment” filter on the library browser while setting up you style, will not get you these parts; “jointed” will. So don’t filter by socket or but weld, as you will see later I use Standard and maybe Material as my filters.

After you finish publishing to content center you will find  that a “Designation” column is added automatically by Inventor and it’s linked to the part’s “Design Tracking Properties: Size Designation” which is of type string and can be edited but not in the iproperties window (code or else).

 It seems this is used only for content center stored parts and represents a “succinct user-friendly universal way of providing a name for the component represented by a row in the table”. This is copied from an old pdf I found on iproperties and VBA by Sean Dotson at AU2014. Most of the times you will have a description of the item like “d10 x 1 – 250 Lg” and it doesn’t need to be unique. I think this has been left over and obsolete so I would just ignore it except when it gives you problems with tube and pipe style as it did to me.

                It seems that it can be accessed as “Content Library Component Properties” as well:

Content Library Component Properties {B9600981-DEE8-4547-8D7C-E525B3A1727A}

Common Name: Size Designation

ENUM Name: kSizeDesignationContentLibrary

Value: 12

Comments: Size Designation: String (VT)BSTR), Editable, No UI, Succinct, user-friendly and universal way of providing a name for the component represented by a row in the table.

Why have I gone to such length and dig out old posts and manuals to find the info on Designation? Because I found it to be the source to my problems, and there’s no logic to what my problem is as well.

I've been having this weird problem with tube and pipe styles. After I author and publish a tube and pipe fitting it won't show up for selection in tube and pipe styles until I edit the family table and delete the designation values. I don’t edit the column formula just select all values in this cell and delete them. Because the column is an expression the cells update automatically to same values but now the family appears in tube and pipe style editor.

As soon as I delete the designation values, the family appears in the styles editor as you can see in the animation bellow, where I have 2 inventor sessions running at the same time.

In the style component browser if you expand your part you will see individual members as long as you have a unique individual stock number. The members are shown by stock number and AU explanation was that part number can have custom lengths so not suitable to use here. I don’t pick individual components just the family when setting up the styles.

Hope you never run into these problems but if you do try deleting the designation values and see if it fixes it.
Next week I will share a couple of excel tricks for retrieving data from other spreadsheets with vlookup match and error handling like when value not found returning #NA so check back. Hopefully I will finish the rest of Tube and Pipe tutorials. I will also share this elbow and excel as separate file (it's embedded anyway in the ipart).

Later,

ADS

                

Inventor BOM Structure

I’ve answered a question today on the inventor forum about the structure and sequence of the BOM. The question was why the structure set at the top level assembly doesn’t persist in the subassemblies and how to link them if possible.

The answer is I don’t know how to link BOM item structure in between different levels of a project” but I have a quick trick on re-structuring. I could think of doing it with illogic, to get the item number from the top level BOM and assign it the BOM of all subassemblies but what if you have reused components in different places? What item number would we give it? What if in one assembly you decide to change a part (or an occurrence) to phantom or reference?

So for now I leave the question open, maybe some of you have the answer and you’re willing to share it with us.

However I do have a workaround and I haven’t thought of posting it before because I’ve been doing it so often it became second nature and it didn’t occurred that others are unaware of it.

Unfortunately because the BOM item number is controlled at the assembly level you need to do this for each assembly that you want re-structured. Luckily, if your project files have unique filenames, than the structure becomes identical in all BOM’s where you do this routine.

It has always been a pain to do these but it takes seconds to fix. Normally in the top level assembly right at the end when the project is done I follow these steps:

1. Go to BOM in the assembly and enable the view, change it to all levels in the View Properties

2. Right clicking on the columns and selecting Runtime Column Customization, add BOM Structure, Component Type and File Path.

3. Use Sort Items and sort by:

3.1 BOM Structure - Ascending (this way you will have normal parts at the top and purchased, phantom reference towards the end.)

3.2 Component type - Ascending (this puts the assemblies and subassemblies at the top and parts at the end)

3.3 File Path - Ascending (if you have a logical part number structure and filename then it sorts by that)

4 Click ok and go to Renumber Items, make sure it set to renumber all rows. Specify the start (I have it on 1) and increment (1 again)

When you click ok they are all sorted out.

Unfortunately because each level can have suppressed, hidden components, this so you can generate a different structure, the component number is not linked between different levels. But it takes seconds to do this on subassemblies and sort them out too, and the item number should correspond because you use same sort order.

Let me know how that goes for you. You don't need to use expand all but I did it to check results.

And of course the video:

 

ADS.

               

               

Tube and Pipe 3

On first and second part we have discussed, naming and constraining your tube and pipe assembly, projecting the run planes for route constraining and importing styles so we don’t mess about with the default template.

The next thing I like to do while inside my route is Import Parameters. You don’t have to follow all this steps and I understand it might seem too much for doing a small route but it’s better to install these habits one at a time and stick with the ones you really need (find useful). I can’t help but do them all no matter how small the assembly. When you have all the time in the world to design a spool you don’t need these but when the day comes hot and heavy you will find them useful.

 What do you mean by saying Import Parameters from xml? I have a set of parameters with same name but depending on the pipe size they have different values and different description.  These are just measurements I’ve done on my fittings (most commonly used ones) in order to see how much space they take. This will help in tight confined spaces, as well as keeping the routes visual appealing.

These are the parameters that I import for a DN32 PVC-U route.

I have a set of parameters for each size-style. For DN32 PVC-U one xml file, DN32 PVC-U DN25 another xml file, DN32 ABS a different one again and so on. I use these parameters to dimension the distance between the nodes, based on what fittings I will have on the node. 

Ex: Ball valve after an elbow will have the distance between the nodes as “=e+vb+gp” (elbow parameter + ball valve parameter + gap parameter) or because it’s so common I have another parameter “ebg” that holds this sum as a formula “ebg = e+vb+gp”, so in this case I will type just “ebg”.

Unfortunately, when dynamically entering dimensions by typing the value (while holding your mouse over a reference), you can’t enter any text value so you won’t be able to type “ebg”. You can start typing a number and then in the dimension value dialog type “ebg”. Inventor will calculate the value but will not put the equation in, just the end result.  So when you edit the dimension (if you used auto-dimension) you will see the end result as 95mm instead of “ebg” what we wanted.

That’s why I just place nodes by clicking a couple of times in the desired direction and dimension right at the end using my parameters as values. If you don’t care about your dimensions, don’t bother doing this, you’re better off skipping and dimension by the “eye”.

When you start, after you select your first point (circular edge, or point) an arrow will show up indicating the direction of the route. You can change the direction either by pressing space bar or by typing a negative value while holding the mouse over the arrow vector. Because Inventor projects the plane of the circular edge and dimensions to a plane can have a negative or positive value, you can switch direction with negative sign. 

        You can enter dimensions by using right click on the reference, triad, start vector, or any face or plane your mouse can select. I don’t use the right click menu, I use + and – to increase the triad or starting vector and while holding your mouse over the reference I start typing my dimension.

If you need to use planes or faces as references, right click and activate point snap, and while holding your mouse over the face or plane start entering the value. 

If you type 100 it will create the segment 100 mm over the reference (more than), while if you type -100 it will create the segment 100 mm to the reference (less than). In my example I am holding the mouse over the wall face and type -100 to keep the nodes and segments inside the room. Inventor will not create a dimension from your node to the reference, but instead will dimension the length of the segment. I don’t want to auto-include geometry, references, and things that might change. If I want to dimension my node from the wall I will first use include geometry on the wall face.

Till next time,

Adrian.

Identify drawing view type

I was looking into identifying the drawing view types so I can add it to the view label and decided to share the code with you guys. I am taking about checking if it's a main, projection, section,detail, etc. type of view.

Lots of places to use this code and one that comes to mind is adding a description to all secondary views (details, sections, projections) of what the main view is called, labelled and on what sheet it is.

Hope it’s self-explanatory (let me know if you need more help with it).

'------------------START CODE--------------- 

' Set a reference to the drawing document.
' This assumes a drawing document is active.
Dim oDrawDoc As DrawingDocument
oDrawDoc = ThisApplication.ActiveDocument

Dim oSheets As Sheets
Dim oSheet As Sheet
Dim oViews As DrawingViews
Dim oView As DrawingView

Dim i As Long
i = 1
For Each oSheet In oDrawDoc.Sheets
'For Each oSheet In oSheets
    oViews = oSheet.DrawingViews
    For Each oView In oViews
        MessageBox.Show("View No.: " & i & _
        vbLf & "View Name: " & oView.Name, "View ID")

        Select Case oView.ViewType
            Case 10506 ' could use kAssociativeDraftDrawingViewType
                MessageBox.Show("View is associative draft one", "View Type")
            Case 10499 ' could use kAuxiliaryDrawingViewType
                MessageBox.Show("View is associative draft one", "View Type")
            Case 10498 ' could use kCustomDrawingViewType
                MessageBox.Show("View is auxilliary one", "View Type")
            Case 10497 ' could use kDefaultDrawingViewType
                MessageBox.Show("View with customized camera settings", "View Type")
            Case 10502 ' could use kDetailDrawingViewType
                MessageBox.Show("View is a detail of a portion of the document", "View Type")
            Case 10505 ' could use kDraftDrawingViewType
                MessageBox.Show("View is a draft one", "View Type")
            Case 10500 ' could use kOLEAttachmentDrawingViewType
                MessageBox.Show("View is an OLE attachment", "View Type")
            Case 10507 ' could use kOverlayDrawingViewType
                MessageBox.Show("View is an overlay one", "View Type")
            Case 10504 ' could use kProjectedDrawingViewType
                MessageBox.Show("View is a projected one", "View Type")
            Case 10503 ' could use kSectionDrawingViewType
                MessageBox.Show("View is a section", "View Type")
            Case 10501 'could use kStandardDrawingViewType
                MessageBox.Show("View with the camera set to one of the " _
                & vbLf & "standard orthogonal views (Top, Iso, etc.)", "View Type")
            i = i + 1
              End Select
      Next
Next

'------------------END CODE--------------- 

Try it out, it will show the view name and type.

ADS.