Sharing improved workflow for boosting AML/AVL completeness

[avillanueva]

I hope to share a method which I think might make filling out AML/AVL entries faster, at least in my company. See, most of my OEM parts are the same as the MFG part so it literally a copy task to fill out. The one by one method is too damn slow and with version 13's expose org preference, a total PITA. I've done most of the big sweeps via excel imports so what's left is small blocks of parts lacking AMLs. Tough to find and fill out. 

I have a report (QML) above that export the full AML/AVL I care about. I have observed that often similar part numbers are in order from the same MFG but just not entered in. I just need to add in MFG name and as a base, they would be approved to start. What I need is a way to make that notation and generate the import sheets for those notations. 

I have settled on a VBA macro (.xlsm) that will pull in the QML reports of AML/AVL with gaps and all Suppliers. From there, I can just note the MFG or Vendors as drop downs to make notations. Then a macro can spit out formatted part import and AML sheets I can load back into Windchill. I am using VBA and Excel since the input and output is all excel sheets and maybe I am not smart enought to know better. Use what you got. 

I will debug this and post as a reply to this thread that will include my QML reports, Excel sheets and VBA code as text file. You can reassemble on your end as you see fit. 

[avillanueva]

Attached it two QML files (as zips) that you can use as reference. These are my source data I am extracting from Windchill. You can modify any of my unique constraints I added that do not apply for you. 

Library-Parts-AML-BOM Usage2: This report pulls all buy parts on lastest BOMs (Mfg View which my id is 1447), released since 2022 (recent), where components are not obsolete or superseded. It displays if its classified in PartsLink node, shows any linked MFG parts and and Vendor parts associated with it. 

All Suppliers: Pretty straight forward where is outputs all MFG and Vendor information along with their IDs for loading to a single table. I used this report in my spreadsheet to create a data validated column drop down list. 

In both cases I use the Excel legacy web data connection to pull down reports to a worksheet for processing. I would love to improve this part so let me know your input here. Running URL below will output report as an HTML table that Excel can feed into a sheet. Always looking for better ways to do this part. 

https://<myhostname>/Windchill/servlet/WindchillAuthGW/wt.enterprise.URLProcessor/URLTemplateAction?format=formatDelegate&delegateName=HTML&xsl1=&xsl2=&oid=OR%3Awt.query.template.ReportTemplate%3A<REPORTIDHERE>&action=ExecuteReport

Next post will be on the Excel sheet itself. 

[avillanueva · Accepted solution]

Attached 

Attached is my template sheet and the VBA code that you can assembly and modify. I deleted the connections but I had two linking to the QMLs I posted this morning. Again, if there are better ways or improvements, please send along.

Some notes on the code. I have two libraries for my buy parts that I coded in. General Parts Library and Electrical Parts Library. SUMA is picky about things being in the same context so update as needed for yours. You can change the defaults for the folder, Lifecycle, state etc in code blocks like below. I did add in a rule that if you provided a diffenent part number for a MFG or Vendor part, it will take it but pull name from OEM part. 

            target.Cells(y, 3) = ActiveCell.Offset(0, 1) 'Copy same Name
            target.Cells(y, 4) = "false" 'End Item
            target.Cells(y, 7) = "component" 'Assembly Mode
            target.Cells(y, 8) = "/Default/SUMA" 'Location
            target.Cells(y, 9) = Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, 6), ThisWorkbook.Worksheets("ManufacturerIDs").Range("B:C"), 2, False) ' False for exact match 'Organization ID
            target.Cells(y, 11) = "Design" 'View
            target.Cells(y, 12) = "RELEASED" 'State
            target.Cells(y, 13) = "EOS Basic" 'Lifecycle
            target.Cells(y, 14) = "buy" 'Source
            target.Cells(y, 15) = "ea" 'default unit

I tested this just before. I was able to spot 120 parts that in a sweep that were missing a supplier, generate sheets and import in less than 20 mins. An improvement for sure.