PDXpert PLM Software
Importing Trilogy Design Parts&Vendors to PDXpert
Last update 2019-02-27
Applies to Trilogy Design™ Parts&Vendors™ SE Edition, Version 6. With adjustments, may be applied to Parts&Vendors EX Edition and Parts&Vendors ECO Edition, as well as earlier versions.
Trilogy Design Parts&Vendors ("P&V") was a Microsoft Access-based personal parts list manager, retired in 2014.
This tutorial reviews how to prepare your P&V design-related data for importing into PDXpert PLM software (or any other Parts&Vendors replacement). We've created Microsoft Access queries (below) that can identify potential issues with your Parts&Vendors database.
Technical differences between Parts&Vendors and PDXpert PLM
PDXpert PLM software is designed for larger, more advanced engineering environments that require product definition, detailed part/document/file management, and formal change control. When considering PDXpert as a Parts&Vendors alternative, there are important differences in how each system represents your product data, which can influence the quality of your data conversion.
|Technical feature||Parts&Vendors||PDXpert PLM|
|Database technology||Microsoft Access 2000||Microsoft SQL Server|
|Multi-user support||Peered data file; from 1 to ~10||Client-server; from 1 to 1000|
|Item class||Parts (CAT, DWG, PL, PS) and
non-inventory parts (AW, DOC)
|Distinct part and document objects|
|Item types||Fixed set; fixed behaviors||Expandable set; modifiable behaviors|
|Custom attributes on type||Ten 100-character text fields;
common to all item types
|Virtually unlimited; unique per type; may be text, date, checkbox, number with optional units, dropdown selections|
|Item numbering||User-entered||User-entered, or system-generated based on item class and/or type|
|Design document relationship||Shown as pseudo-parts within the parts list (BOM)||Shown separately in the part's References list (pseudo parts can be defined and added to a BOM, but this isn't considered best practice)|
|BOM quantity data type||Quantity is text, and allows non-numeric values like A/R||Quantity is a numeric value; uses convertible unit of measure; may be classified as Per Assembly, Per Setup, As Needed|
|File management||Link to unmanaged external files||Import and manage revision and item files, or link to unmanaged external files|
|Revision management & change control||Optional feature (ECO edition); single change type with hard-coded workflow||Standard feature; user-defined change types with configurable workflow; revision histories for BOMs, sources, references & files; email notifications|
|Purchasing & production||Customer jobs, RFQ, Purchase Orders, inventory balances||Part-level attributes (packaging/order quantities, unit cost, handling/storage and end-of-life tags) for export to manufacturing1|
|Part cost and roll-up report||Multiple part costs with discount levels||Single budgetary part cost (custom attributes may define alternate costs)|
|Tasks||Related to a customer job||Prepares an item revision for release|
|Organizations||Up to four tables (CU, MFR, ORG, SU) for different purposes||Unified list for all purposes|
|Units of measure||Default, purchase and OEM parts may have different units (each and feet); may be inconsistently applied (e.g. a box may contain 5, 24 or any other quantity, depending on the part)||A unit of measure (m) is convertible to other units (cm, ft) within a category (Length); BOM units must be the same as the part default unit, or may optionally remain within its category|
|Currency||May use non-standard currency name, e.g.
Canadian $; exchange rate is
[ default / selected ] e.g., USD/EUR
|Uses standard ISO currency code, e.g. CAD;
exchange rate is
[ selected / default ] e.g., EUR/USD
|Text search||Structured Microsoft Access literals and wildcards||Google™-like free-form with wildcards, filters, proximity, ranges and other extensions|
PDXpert software doesn't support purchase orders, inventory balances, barcoding and customer jobs; this Parts&Vendors data cannot be imported.
Preparing your Parts&Vendors data before importing
You should be comfortable running, and perhaps modifying, Microsoft Access queries.
If this tutorial seems a bit too complex or time-consuming, we can prepare your Parts&Vendors conversion for you.
Depending on how you've used Parts&Vendors, some of these steps may not be needed for your data.
Check that the part units of measure in P&V are matched exactly in PDXpert. For example, P&V may use feet while PDXpert's default is ft. Edit one system so that both systems use the same name for each unit. A useful Access SQL query to run against the P&V database:
SELECT DISTINCT UN.UNUseUnits
FROM UN INNER JOIN PN ON UN.UNID = PN.PNUNID
ORDER BY UN.UNUseUnits;
Parts&Vendors has four tables that contain organizations: manufacturers (MFR table), suppliers (SU), customers (CU) and your own (ORG). In PDXpert, these are merged into a single Organizations collection. Clean the P&V data of spelling errors, and resolve conflicting names (e.g., TI / Texas Instruments / Texas Instruments Inc.) into one preferred name.
List all organizations using this Access query:
(SELECT MFR.MFRMfrName AS Organization, 'MFR' as TableName FROM MFR)
UNION (SELECT SU.SUSupplier AS Organization, 'SU' as TableName FROM SU)
UNION (SELECT CU.CUCustomer AS Organization, 'CU' as TableName FROM CU)
UNION (SELECT ORG.ORGName AS Organization, 'ORG' as TableName FROM ORG)
ORDER BY Organization;
Table names in the query results, e.g. 'MFR' as TableName, lets you identify which table contains the preferred organization.
To focus exclusively on manufacturers and suppliers:
(SELECT MFR.MFRMfrName AS Organization, 'MFR' as TableName FROM MFR)
UNION (SELECT SU.SUSupplier AS Organization, 'SU' as TableName FROM SU)
ORDER BY Organization;
Of course, you can also ignore the differences, and after import into PDXpert either (1) manually reassign items to the preferred owner and delete the unwanted organizations, or (2) set the non-preferred organization record to inactive, and edit it to match the preferred Name and Display name.
In Trilogy P&V, the part cost PN.PNCurrentCost uses the default currency. Therefore, currencies can usually be ignored if PDXpert's Currencies collection's default member matches P&V. However, if your part costs do not use the default currency, or you want to use a cost in the LNK or COST tables, costs and currencies may need special attention. In this case, edit the P&V currency name to use the ISO 3-character code (USD, CAD, JPY, etc.) and/or set the correct default in the Currencies collection.
Since PDXpert calculates assembly costs on demand, import CAT and PL parts at 0 (zero) cost. Otherwise, the assembly cost will be added to the total cost of components.
In both systems, item types are used to define item purpose and attributes. P&V's six fixed item classifications can be mapped to PDXpert's part and document types.
P&V type Purpose of type Similar PDXpert class: type AW Artwork: Data in physical form (camera-ready art, film, or other media) Document: Drawing CAT Catalog Number: marketing part number (just a filter, operates the same as PL) Part: Assembly
(or create new part type)
DOC Document: A drawing or text document
used to specify, fabricate or process a part
Document: Specification DWG Drawing: a component defined by your
company drawings or specifications
Part: Design, or a combined Component PL Parts List: assembly / subassembly Part: Assembly PS Purchase Spec.: a component defined by another company, and adopted for your use Part: Purchase, or a combined Component
The P&V user guide says AW and DOC items can have part attributes (e.g., sources and costs). If these are required, add the desired custom attributes to each PDXpert document type.
You can create any number of types in PDXpert to support, say, special numbering rules or custom part attributes for E-CAD. You'd then need to define the rules, based on part number and/or description, for assigning P&V's parts to the larger set of PDXpert types.
If any item record is missing a revision value, it'll be assigned a blank value during import. Where appropriate, you should assign a non-blank revision value in Parts&Vendors, or note how the Item Master import file will be updated to include revision values.
These relationships between parent types and child types may indicate a problem:
Parent Implied invalid child Explanation AW, DOC PL* with CAT, PL, PS, DWG; MFR Documents should not have sources or a parts list, but may have reference documents CAT MFR Proprietary end products don't usually have off-the-shelf sources; however, some catalog items may be purchased and re-sold, and they may have qualified contract manufacturers PL* MFR Proprietary assemblies don't usually have off-the-shelf sources, although they may have qualified contract manufacturers PS PL with CAT, PL, PS, DWG Parts purchased to a specification don't usually have a parts list, but may have reference documents DWG PL with CAT, PL, PS, DWG; MFR Proprietary components don't usually have sources or a parts list, but may have reference documents; however, some proprietary parts may indicate qualified contract manufacturers
* In the P&V database, PL is both (1) a table name and (2) an item type. Non-PL types can have PL table entries.
Decide whether (a) the Parts&Vendors record needs editing or (b) PDXpert's rules should be modified by, say, enabling the Sources list on the Assembly part type. A P&V AW and DOC record won't appear on a PDXpert assembly's BOM list, but on its References list. Any part or document appearing on P&V AW and DOC document's "parts list" is imported onto the PDXpert document's References list.
The Access query for checking parts list relationships:
SELECT PN.PNType, PN.PNPartNumber, PN.PNTitle,
PL.PLItem, PN_1.PNType, PN_1.PNPartNumber, PN_1.PNTitle
FROM (PN INNER JOIN PL ON PN.PNID = PL.PLListID)
INNER JOIN PN AS PN_1 ON PL.PLPartID = PN_1.PNID
WHERE (PN.PNType="AW" OR PN.PNType="DOC" OR PN.PNType="DWG" OR PN.PNType="PS")
AND (PN_1.PNType="CAT" OR PN_1.PNType="DWG"
OR PN_1.PNType="PL"OR PN_1.PNType="PS")
ORDER BY PN.PNType, PL.PLItem, PN_1.PNPartNumber;
The Access query for checking manufacturer parts relationships:
SELECT PN.PNType, PN.PNPartNumber, PN.PNTitle, MFR.MFRMfrName,
MFRPN.MFRPNPart, LNK.LNKVendorPN, LNK.LNKVendorDesc
FROM (PN INNER JOIN LNK ON PN.PNID = LNK.LNKPNID)
INNER JOIN (MFR
RIGHT JOIN MFRPN ON MFR.MFRID = MFRPN.MFRPNMFRID)
ON LNK.LNKMFRPNID = MFRPN.MFRPNID
ORDER BY PN.PNType, MFR.MFRMfrName, MFRPN.MFRPNPart;
Parts lists in Trilogy Design Parts&Vendors allow non-numeric quantities, such as A/R ("as required"). PDXpert and many manufacturing systems require a numeric value for the BOM component quantity. This is typically assigned quantity of 1. The row uses the part's default unit of measure with a BOM quantity category As Needed. (If a downstream system expects text like A/R, this can be restored in the export query.)
The Access query to identify parts (CAT, DWG, PL, PS) that have non-numeric quantities:
SELECT PN.PNType, PN.PNPartNumber, PN.PNTitle, PL.PLItem,
PL.PLQty, PN_1.PNType, PN_1.PNPartNumber, PN_1.PNTitle
INNER JOIN (PL INNER JOIN PN AS PN_1 ON PL.PLPartID = PN_1.PNID)
ON PN.PNID = PL.PLListID
AND (PN_1.PNType="CAT" OR PN_1.PNType="DWG"
OR PN_1.PNType="PL" OR PN_1.PNType="PS")
ORDER BY PN.PNType, PN.PNPartNumber, PL.PLItem;
The quantity specified for item types AW and DOC is ignored, as these items are imported as documents onto the parent item's References list (which has no quantity).
The "manufactured from" concept in P&V makes sense in the context of production, but not in the engineering definition managed by PDXpert.
In most cases, the engineering BOM provides the actual quantity required. The manufacturing staff determines—and can change without engineering approval—the best way to obtain that quantity. For example, if the product requires 10 centimeters of wire, this is simply specified on the BOM; there's no point to specifying that it's made from a length of 10m or 100m, or purchased in pre-cut 10cm segments.
In P&V, identify the actual part quantity required, and update the parts list accordingly. The Access query:
SELECT PN.PNPartNumber, PN.PNType, PN.PNTitle, MF.MFQty,
PN_1.PNPartNumber, PN_1.PNType, PN_1.PNTitle
FROM PN AS PN_1
RIGHT JOIN (PN INNER JOIN MF ON PN.PNID = MF.MFPNIDParent)
ON PN_1.PNID = MF.MFPNIDSub
ORDER BY PN.PNPartNumber, PN_1.PNPartNumber;
On a home purchased part in PDXpert, the Sources list identifies approved partner parts. By convention, the engineering-approved list contains only original manufacturer, not distributor, parts. This is because distributor parts aren't independently qualified by engineering.
For convenience, one distributor part from Trilogy P&V (SU table) can be imported onto the partner part. (This limit is driven by Access query constraints.) The manufacturer is always considered its own supplier, and is automatically excluded from the suppliers list. In PDXpert, custom attributes may be created on the partner part type to accept the distributor organization and its ordering number.
All part records in P&V share 10 common custom text attributes. These are evenly split between item-level and revision-level. PDXpert can support a very large set of item-level custom attributes, specific to each part or document type. These attributes can have more varied data types (text, numbers with optional units of measure, currency values, Booleans, dates, and lists of collection members). Analyze how the P&V types should appear on imported parts in PDXpert, and define these attributes on the appropriate type collection members. The Access query for user-defined attribute names:
SELECT HPREF.GPREFKey, HPREF.GPREFText1, HPREF.GPREFText2,
HPREF.GPREFText3, HPREF.GPREFText4, HPREF.GPREFText5, HPREF.GPREFText6,
HPREF.GPREFText7, HPREF.GPREFText8, HPREF.GPREFText9, HPREF.GPREFText10
The Access query showing all items with non-empty user-defined attribute data:
SELECT PN.PNPartNumber, PN.PNTitle, PN.PNDetail, PN.PNRevision,
PN.PNUser1, PN.PNUser2, PN.PNUser3, PN.PNUser4, PN.PNUser5,
PN.PNUser6, PN.PNUser7, PN.PNUser8, PN.PNUser9, PN.PNUser10
AND ((PN.PNUser1 Is Not Null) OR (PN.PNUser2 Is Not Null)
OR (PN.PNUser3 Is Not Null) OR (PN.PNUser4 Is Not Null)
OR (PN.PNUser5 Is Not Null) OR (PN.PNUser6 Is Not Null)
OR (PN.PNUser7 Is Not Null) OR (PN.PNUser8 Is Not Null)
OR (PN.PNUser9 Is Not Null) OR (PN.PNUser10 Is Not Null))
ORDER BY PN.PNPartNumber;
If there are files to be imported, confirm that all files are available and correctly identify their parent part. Create and save a ListFiles.csv file (with headers) using this Access query:
IIf([PNType]="DOC","Document",IIf([PNType]="AW","Document","Part")) AS Class,
PN.PNPartNumber AS Number,
PN.PNType AS Type,
FIL.FILFileName AS RevisionFile
FROM PN INNER JOIN FIL ON PN.PNID = FIL.FILPNID;
To attach the files to PDXpert's Item Files list, replace AS RevisionFile with AS ItemFile. With further editing, you can select which files are attached to each list.
If your company is identified as a P&V organization in the ORG, MFR, SU or CU tables, use that exact name in PDXpert's Software License Key window.
PDXpert provides free-form text search, similar to web search engines. Individual search terms are separated by spaces, the same as normal language. Therefore, ensure searchable text includes spaces. A part name Resistor,270ohm,1% is treated as a single seach element, and should be updated to Resistor, 270ohm, 1%. (A simple test is whether Windows Notepad treats the description as a single block of text, or breaks it naturally, when Word Wrap is enabled.) To ensure your text can be indexed for searching, update fields like PNTitle, PNDetail, PNNotes and similar (see PNUser??) in Access. For example, insert a space after every comma, and then remove any doubled spaces:
Replace(Replace([PNTitle],",",", ")," "," ")
Parts&Vendors EX Edition and Parts&Vendors ECO Edition
Trilogy Design Parts&Vendors EX Edition and ECO Edition users and permissions (tables DEPT, GRP, USR) aren't directly transferrable from the Microsoft Access workgroup to the PDXpert user permissions model. However, you can use PDXpert's Batch Importer CollectionPersons.csv file format to create persons, and to assign roles and user accounts.
Due to significant design differences, Parts&Vendors ECO Edition data tables ACD, ECO, MFE, PLE, PNE and RH require case-by-case analysis to determine whether useful change data can be transferred.
Configuring PDXpert and importing your data
After you finish your Parts&Vendors review, and before beginning the import process, the PDXpert system must be configured to reflect your analysis.
- menu | : Licensed organization name
- Persons collection: EX Edition persons list; default member becomes imported items' trustee
- Sequences: Identifier collection
- Units of measure collection
- Document Types collection: new or modified collection members, identifier sequences, revision sequences, custom attributes
- Part Types collection: new or modified collection members, identifier sequences, revision sequences, custom attributes on source part type(s) for suppliers, custom attributes generally
After configuration, the Parts&Vendors data is extracted and then imported using P&V-to-PDXpert code.
Review your data in PDXpert after importing
Verify that the import is successful by comparing parts, documents, file attachments and other data to your Parts&Vendors data. With sufficient pre-import preparation, you can expect reasonably good fidelity. However, the significant technical differences between PDXpert and Parts&Vendors may require changes to your imported data or PDXpert's configuration.
Confirm that the part and document identifier sequence(s) have the correct Next number value. This ensures that new item numbers won't conflict with imported part numbers.
If it hasn't already been done, use the Batch Importer tool with the ListFiles.csv that you created earlier.
For insight into what data can be imported into PDXpert, see the PDXpert application help topics How to report, import & export > Import & update items >
- Use the Batch Importer (on line)
- Item Master import format (on line)
- Bill of Materials import format (on line)
- Sources import format (on line)
- References import format (on line)
- Item files & links import format (on line)
- Revision files import format (on line)
Trilogy Design™ and Parts&Vendors™ are trademarks of Trilogy Design, Grass Valley, CA, USA.
This application note was relevant to the PDXpert software release that was current at time of publication. Product changes since that time may affect its utility. We'd be happy to assist you in assessing the applicability of this note to your situation.
Working within PDXpert
- Designing an engineering change process and workflow
- Server: Fast file content searches using IFilters
- Server: Scheduled backup for the database and library
- Server: Running several PDXpert Application Servers on the same network
- Batch Import: Preparing file attachments for import
- Batch Import: Converting a PDX package for PDXpert
- Batch Import: Keep settings, remove test item records
- Batch Import: Creating release sets for imported data
- Batch Import: Bulk replacing item attributes
- PDX package: Compare all product data, not just the BOM
- PDX package: IPC-2570 package implementation
- PDX package: IPC-2570 security signing and password extensions
- Adding a custom SQL Server report to PDXpert
- Adding a custom SQL Server report to PDXpert 8.0 to 11.2
- Designing email notice templates
- Including labor time and cost in assemblies
- Materials management for regulatory compliance
- Switching an existing item to a different type
- What's the difference between Item Revision State and Item Lifecycle Phase?
- Copying an assembly BOM to another assembly
- PDXpert permissions: User access design
Working with other software applications
- Using Altium Designer EDA software with the PDXpert client
- Using Altium Designer EDA software with the PDXpert server
- Using Cadence OrCAD Capture CIS with the PDXpert client
- Using Cadence OrCAD Capture CIS with the PDXpert server
- Using Pulsonix EDA software with the PDXpert server
- Importing a CAD bill of materials into PDXpert PLM software
- MRP/ERP data transfer options with PDXpert PLM software
- Exporting PDXpert PLM parts, BOM & sources to MRP/ERP
- Importing Trilogy Design Parts&Vendors data into PDXpert
- Exporting a bill of materials from PDXpert to Visio