5 Replies Latest reply on Jan 15, 2019 9:45 AM by Bill Toft

    How to assign correct vendor part number to a variable length part?

    Bill Toft

      Background:

      At the simplest level, I have an assembly with 2 vertical Post parts (3" wide) and 12' apart (at center).

      I then add a horizontal Header part between them. It is a purchased part and comes in 1' increments, with a unique Vendor Part Number (VPN) for each Stock Length.

      I set up the Header part file to have a Configuration for each Stock Length (12', 11', 10', 9', 8', etc.). I added a configuration-specific custom property (VPN) and entered the corresponding Vendor Part Number for each length.

      The Header's Cut-Length was set to be a global variable = Stock-Length - 3"

       

      The original design intent was to sell this assembly in a fixed set of widths (12, 11,10, 9 and 8), so my assembly had a separate configuration for those 5 sizes.

      The new design intent is to allow ANY width to be ordered (1' to 12'), so I went with the Skeleton Sketch Part (SSP) design. This part has 1 sketch on front plane that is a rectangle with Project Height and Project Width driven by global variables.

       

      The new design assembly uses the SSP to position the 2 Posts. I then mate the Header part to the right face of the left Post.

      Finally I use an Assembly Cut feature to use a non-dimensioned sketch to cut the right side of the Header to match the left face of the right Post.

      To avoid errors in the assembly cut feature, I am using the longest (12') Header configuration as the default.

      I have set up a global variable (using measurement) for the Cut-Length (say 8'-2"). I then calculate Order-Length by rounding up (to 9').

      I have set up a SW Part-Number = Profile-Name + Order-Length ("HEADER-09")

      Finally I set up an Excel spreadsheet with 2 columns (SW-Part-Number and VPN) with one row for each Stock Length.

       

      Question:

      What is the best way to show the correct Vendor Part Number in the BOM?

      1. The manual method is for me to select the correct Header Stock Length Configuration in the assembly. This brings up the part's matching VPN in the BOM..

      2. The automated method would be to always use 12' as the Header Stock Length Configuration, then "somehow" use the calculated SW-Part-Number to do a lookup in the Excel file to get the correct VPN. Can this be done in the drawing BOM table?

      Note that the assembly also includes other parts (like brackets & fasteners) that only come in one size, so only have one VPN.

       

      All ideas & suggestions welcomed!

        • Re: How to assign correct vendor part number to a variable length part?
          Josh Brady

          I assume that the VPN is not an "intelligent" part number that contains the length, but a pseudo-meaningless number like McMaster-Carr uses?

          • Re: How to assign correct vendor part number to a variable length part?
            Scott Stuart

            Use a design table, and in the table make a formula that converts the custom lenth to the stock length with a roundup function or something like that, and then do a vlookup to convert the stock length to the VPN.

              • Re: How to assign correct vendor part number to a variable length part?
                Bill Toft

                Hi Scott & Josh,

                Thanks for suggesting Design Tables. I decided to try Scott's idea first (part level) and it worked out great for me.

                 

                Here is a summary of what I did, in case it can help others.

                1. Set up Header part with 2 configurations (L1 & L2). This allows me to use the same part twice in an assembly and let each part have a separate Cut Length (separate Cut-ExtrudeN -> features that are configuration specific).

                2. In the Header part, set up a Global Variable ("MaxLength") and set it to the longest size available for this extrusion.

                3. Do a Boss-Extrude of the profile sketch (from Right Plane to a distance of MaxLength).

                4. Insert the Header part into the assembly and Mate from Right Plane to Left Post (right face).

                5. In the assembly, do an Extruded Cut on the Header part to cut right edge to line up with Right Post (left face). Propagate this feature to the part.

                6. In the Header part, add a Global Variable "CutLength" and set it to me the measured length of the cut part ("RD1@Annotations").

                7. Add a Global Variable with a formula ("OrderLength" = int ( "CutLength" / 12 ) + 1 ). This gets us the length in feet (rounded up).

                8. I have a Custom Property for "SWPartNumber" with format NNNN-00LL where NNNN is the unique Profile and LL is the OrderLength. For this part the value is "2200-00LL" because we do not yet know LL.

                So I set up a Custom Property "SWBasePartNumber" with format NNNN-00 because LL will be changing.

                9. Because my actual assembly has many extruded parts that will be cut to length, I set up a separate spreadsheet ("VPN") with the first column "SWPartNumber" and the second column "VendorPartNumber". I manually entered all possible SWPartNumbers and their corresponding VPN. Column A is sorted to get the SWPartNumber's in numeric order.

                10. FINALLY, insert that Design Table into the Header part (automatic creation). One column is $PRP@OrderLength.

                11. In the column $PRP@SWPartNumber use the equation =CONCATENATE($PRP@SWBasePartNumber,$PRP@OrderLength). Result is "2200-0012"

                12. In the column $PRP@VendorPartNumber, use the equation =VLOOKUP(PRP@SWPartNumber,[VPN.xlsx]VPN!$A$2:$B:$100,2).

                This allows me to use that one spreadsheet for ALL extruded parts. Result is "6928103" which is the correct VPN for that profile and length.

                13. Set up a drawing of the assembly with a BOM table. Add SWPartNumber and VendorPartNumber as columns.

                Save & close all files.

                 

                Open the Skeleton Sketch Part and change the width dimension. Save and close.

                Open the Assembly. It will automatically resize. Open the Drawing. The BOM will show the new OrderLength as part of the SWPartNumber and its matching VendorPartNumber.

                MAGIC!