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.

    Parts and Vendors custom supplier attributes on Source type
  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.

Application Notes
Working within PDXpert
Working with other software applications