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

Note

1. 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.

1. 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;

2. 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.

3. 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.

4. 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.

5. 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.

6. 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

   WHERE (PN.PNType<>"PS")

   ORDER BY PN.PNType, MFR.MFRMfrName, MFRPN.MFRPNPart;

7. 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

   FROM PN

     INNER JOIN (PL INNER JOIN PN AS PN_1 ON PL.PLPartID = PN_1.PNID)

     ON PN.PNID = PL.PLListID

   WHERE IsNumeric([PLQty])=False

     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).

8. 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;

9. 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.

   

10. When several home parts share the same approved OEM source, this may indicate an incorrect source assignment or redundant home part numbers. The Access query:

    SELECT DISTINCT MFR.MFRMfrName, MFRPN.MFRPNPart, PN_1.PNPartNumber, PN.PNTitle

    FROM MFR

      RIGHT JOIN (MFRPN

       INNER JOIN (PN AS PN_1

        INNER JOIN (LNK AS LNK_1

         INNER JOIN (PN

          RIGHT JOIN LNK ON PN.PNID = LNK.LNKPNID)

          ON LNK_1.LNKMFRPNID = LNK.LNKMFRPNID)

         ON PN_1.PNID = LNK_1.LNKPNID)

        ON MFRPN.MFRPNID = LNK.LNKMFRPNID)

       ON MFR.MFRID = MFRPN.MFRPNMFRID

    WHERE ((PN.PNPartNumber)<>[PN_1].[PNPartNumber])

    ORDER BY MFR.MFRMfrName, MFRPN.MFRPNPart, PN_1.PNPartNumber;

11. 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

     FROM HPREF

    WHERE HPREF.GPREFKey="UserFields";

    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

    FROM PN

    WHERE (PN.PNTab=False)

      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;

12. 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:

    SELECT

      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.

13. 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.

14. 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.

• Tools menu | Software License Key...: 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.

Final comments

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.