PDXpert® on PDXpert on Google+

Exporting a PDXpert PLM bill of materials to Fishbowl Inventory

Last update 2011-07-03

Fishbowl Inventory® for QuickBooks® requires two separate identifiers to transform a collection of components into an assembled product. This is different from PDXpert PLM software, which directly associates the parent assembly number with its child parts. This note describes one method for transforming a PDXpert PLM assembly to Fishbowl Inventory using Microsoft® Excel®.

Topic contents

Fishbowl Inventory for QuickBooks: the Bill of Materials record

Fishbowl Inventory collects and displays pre- and post-production information about an assembly in two sections under a single BOM record.

  • The first section contains the output of the manufacturing process, the Finished Good, which is assigned a physical part number that's distinct from the BOM record number.
  • The second section contains the set of individual items (Raw Good) that are required to construct the Finished Good.

The example below shows that shippable product 1234 (a Finished Good) is produced from two components (Raw Good numbers 2345 and 4567) and an assembly (Raw Good 3456). These are shown on the Fishbowl bill of materials record 5678, which is of type Manufacture.

Fishbowl bill of materials record

Fishbowl Inventory's import file formats

There are two Fishbowl import files of interest, each containing a set of properties in standard comma-separated value (.csv) format.

Item Import CSV file

The item file contains all items that will be consumed or produced. It doesn't include the BOM Manufacture record, which appears only in the BOM Import file.

PartNumber PartDescription UOM PartType
Text: max 70 Text: max 252 Text: max 10 Inventory

BOM Import CSV file

Each BOM file has one BOM row, which contains the BOM Manufacture record.

Flag Number Description Type Revision AutoCreateType
BOM Text: max 70 Text: max 252 Manufacture Integer Never

The BOM file also includes Item rows that contain any number of Raw Good components as well as the Finished Good. Each BOM Import file's item Description and Part must match, respectively, the Part Import file's item PartDescription and PartNumber text.

Flag Description Type Part Quantity UOM
Item Text: max 252 Raw Good Text: max 70 Real number Text: max 70
Item Text: max 252 Finished Good Text: max 70 1 each

Combining these, the data headers are in the first two rows and all data follows. For example:

Flag Number Description Type Revision AutoCreateType
Flag Description Type Part Quantity UOM
BOM 5678 BOM: Model 55, Shippable Manufacture 1 Never
Item User Guide, 55 Series Raw Good 2345 1 each
Item Shipping Carton Raw Good 4567 1 each
Item Model 55, Final Assembly Raw Good 3456 1 each
Item Model 55, Shippable Finished Good 1234 1 each

Modeling the Fishbowl BOM within PDXpert

The PDXpert export file must contain two distinct records for each Fishbowl assembly, the Finished Good item and the Manufacture record.

The PDXpert assembly shares its part number with the Fishbowl Finished Good (1234 in our example). Along with the individual components and any lower-level Finished Good subassemblies, the PDXpert structure must include exactly one Fishbowl Manufacture record, 5678.

The practical consequence is that the physical assembly located in a warehouse location is the same as the PDXpert assembly number. It seems intuitive that the same PDXpert and Fishbowl items should have identical physical attributes like unit of measure, cost, mass and materials.

In practice, you can search for either record to find its complement: the Manufacture record appears on the Finished Good item's Structure, while the Finished Good is displayed on the Manufacture record, on the Appears On tab's Structure list.

There are other schemes for relating the single PDXpert assembly to the pair of Fishbowl records. For example, a separate number could be entered into the assembly's Global number or a custom attribute could be created. These have the benefit of being more tightly coupled to the assembly, and not requiring a separate structure record. The disadvantages are that the virtual component can't have a distinct Description, and the record identifier can't be auto-generated (although a simple rule could be, say, to replace manually the first character in the assembly part number with "9" or append a suffix).

How one PDXpert export creates two Fishbowl import files

The Excel macro PDXpertToFishbowl processes the PDXpert export file, which is created using the Data Transformation collection's Export Structure (Current) member.

  • To create the Fishbowl item import CSV file from the PDXpert file, each item is copied unless it's a document or is of the type Manufacture.
  • Creating the Fishbowl BOM import CSV file is a bit more complex. With the PDXpert parent assembly as the Fishbowl Finished Good:

    1. The PDXpert items are scanned for the Manufacture record that's copied to the BOM row.

      The Excel macro assigns the PDXpert assembly's revision, not the PDXpert Manufacture document revision, to the Fishbowl Manufacture record. It's the assembly's list of components that is revised, and there's no particular benefit to keeping the Manufacture record's revision in lockstep with the assembly's revision.

    2. All items on the list that are neither documents nor the Manufacture record are copied to the BOM import file.

    3. The PDXpert parent assembly is copied to the final row as the Fishbowl Finished Good.

Setting up the PDXpert PLM collections (once)

  • Add a new Document Type named Manufacture and use BOM as the Abbreviation.

    On the General tab, the Markup will accept parts checkbox should remain cleared. To avoid confusing this with the physical assembly record, don't accept documents or parts on the Markup, or any file attachments.

    On the Attributes tab, assign new document numbers from (a) the same Identifier sequence collection member as the parts use (e.g., Part number), or (b) a special sequence for BOM records. It may be useful to assign something like BOM: [assembly name] to the Document title template.

  • Open the existing Part Type collection's Assembly member, and edit the Name to Finished Good, and update the Abbreviation to FG. The Markup will accept parts checkbox should remain marked.

    This step is entirely optional. The Excel macro will convert any assembly part type into a Fishbowl Finished Good type. You can edit the Assembly record if you prefer PDXpert to display Finished Good to match Fishbowl's record.

  • Ensure that the PDXpert's Units of Measure members match the Fishbowl units of measure. The primary UOM is each or ea but also review other Count members, as well as the Length, Area, Volume and Mass categories. Fishbowl's UOM matching is case-sensitive.

Setting up the export/import workspace (once)

  1. Using Windows Explorer, create a new folder on one of your computer's local drives. Let's call this the \Working folder. Download the PDXpert-PLM-Tools.zip file, extract the Excel PDXpert-PLM-Tools.xls file, and save it in your \Working folder.

    The following steps 2 & 3 will be performed automatically if you run the Excel ThisWorkbook.PDXpertToFishbowl macro immediately after this step.

  2. Create a folder immediately under your \Working folder called \PDXpertOut, so you now have a folder \Working\PDXpertOut. This is where the Excel macro will look for the PDXpert assembly export file(s).

  3. Create another folder under your \Working folder called \FishbowlIn. The Excel macro will save all Fishbowl import files in the \Working\FishbowlIn folder.

Creating your assembly in PDXpert (each assembly)

  1. Create your parent assembly as a Finished Good item.

  2. Add child documents and parts (which may include other Finished Good items) to the assembly Structure tab's Markup list.

  3. Add one Manufacture record to the Markup list.

    A source file will not be converted if it doesn't have any, or has more than one, Manufacture record.

    This Manufacture record can be at any Find location on the assembly's Structure list.

  4. Release the assembly.

Processing your assembly files (each export/import)

Before proceeding, you may want to delete any existing files in the \Working\PDXpertOut and \Working\FishbowlIn folders.

  1. From within the PDXpert client:

    1. On the Tools menu, select Report/Export Wizard...

    2. Click on the Export table-formatted data button.

    3. Select the Export Structure (Current) report. Click the Next button.

      A source file will not be converted if it has any pending or canceled items.

    4. From the PDXpert Item Explorer, drag a released assembly (which contains the components and virtual item record) onto Report/Export Wizard window. Click the Finish button.

    5. Navigate to your export subfolder \Working\PDXpertOut and save the file.

  2. Open the PDXpert-PLM-Tools.xls file in Excel. Run the macro ThisWorkbook.PDXpertToFishbowl (for example, in Excel 2010, click the Developer tab and then the Macros button). This macro processes one or more files in the \Working\PDXpertOut folder and creates the related Fishbowl files in the \Working\FishbowlIn folder.

    You may need to enable macros; for instructions, search for "enable macro" in the Excel Help file.

    The Fishbowl filenames use the BOM number, not finished good number.

  3. From within the Fishbowl Inventory application, first import the item import file \Working\FishbowlIn\Item-number.csv and then the BOM import file \Working\FishbowlIn\BOM-number.csv. If you've created multiple item and BOM files, import all item files before importing the BOM files. Refer to the Fishbowl Inventory user documentation Steps to Import a CSV File for detailed instructions.

Final comments

The PDXpert-PLM-Tools.xls file offers several options on the FishbowlRules tab. You can identify alternative working folders, set file processing limits, and choose whether to auto-delete a source file in the \PDXpertOut folder after it's been converted.

In the BOM import file, the Finished Good item is always assigned a quantity of 1 and unit of measure of each.

Before each run, ensure that your working folders contain only those files that you want processed. During the Fishbowl import, you can mark the Delete file from the file system after successful import checkbox which can be used to automatically clean up the \FishbowlIn folder after the import is finished.

Because the Inventory items must be imported before the BOMs can be constructed, import of a multi-level product structure requires items to be imported "bottom up".

Excel® and Microsoft® are registered trademarks of Microsoft Corp. Fishbowl® and Fishbowl Inventory® are registered trademarks of Fishbowl. Intuit® and QuickBooks® are registered trademarks of Intuit, Inc.

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

PDXpert & other software