miniPDM Excel Add-in is primarily designed to read SolidWorks Bill Of Materials exported as Excel files but can potentially work with any other software capable of exporting an indexed BOM. It is capable of computing the information from the BOM’s columns but also combine it with the extra information stored in the associated database.
Rows / Columns Requirements of the Source
The top row has to contain the column titles. Three mandatory columns must be present in the source for the program to work:
- Index
- Unique Identifier
- Quantity
The Index is used to display a tree-layout and correctly compute the quantities by identifying parents and children components. The Index has to be the first column. The values in the column have to follow the same pattern with the ranks separated by the character “.”.
The Unique Identifier is used to discriminate a component from another in order to group identical components across several sub-assemblies together.
Finally the Quantity is used to get the total quantity of the same component and properly compute related information.
Additional columns can be added to the source BOM to display extra information on the components and allow advanced filtering in Excel and display anything you want like a normal spreadsheet.
If a component is missing any of these values, the add-in will throw an error and won’t be able to compute the BOM. The errors are highlighted to help quick resolution. They can be corrected directly into the spreadsheet but next time a new BOM is exported it will contain the same errors. It is strongly advised to correct the Component Properties and not the exported BOM itself.
The tab containing the source is renamed to miniPDM – Source whether the program is stopped by an error or not.
Once the source is clear of errors and the program can run, the miniPDM Excel Add-in will do the following in the workbook’s tabs:
- Rename miniPDM – Source tab to miniPDM – Tree
- Create miniPDM – Computed tab
Note that these predefined tab names and mandatory columns names can be changed in the settings. Please see the article about the Excel Add-in Settings.
miniPDM – Tree
The miniPDM – Tree tab presents the Bill Of Materials structured like the assemblies and sub-assemblies. This tree-like structure helps the user identify to which parent(s) a child belongs to.
The order is the same as the source, the component’s Index is just indented according to its rank. Each node represents an assembly, or more precisely a component that contains one or more components. See the article about the Definition of the Word Component.
Note that the Index values don’t have to be contiguous. If you delete a row you don’t have to update all the following indexes.
The components are grouped up to 8 ranks deep and can be expanded “+” and collapsed “-” independently by clicking on the node icon or all at the same time by clicking the rank numbers to adapt the view and reduce the size of the table.
A gradation of color that can be customized, see the article about the Excel Add-in Settings, helps to visually know how high / low a component is ranked in the assembly structure.
Components of the same rank have the same shade.
The quantity displayed on each row is the quantity to make one (sub-)assembly. The quantity in comments is the total number of component required to make the total number of (sub-)assemblies contained inside an higher level sub-assembly.
In this example to make one unit of 0000002268, you need four units of 0000002076. In result, you need four 0000002098 to make one 0000002076 and sixteen to make one 0000002268.
miniPDM – Computed
The miniPDM – Computed tab presents the information flattened unlike the miniPDM – Tree tab: each instance of a component is grouped under one unique row. This tab gives a different perspective to help track, purchase, etc… identical components as a batch.
Note that the Index column does not exist in this tab as there is no ranking of the components.
Every other row has an alternating color that can be customized, see the article about the Excel Add-in Settings, to facilitate reading.
The quantity displayed is the sum of the quantity of each instance of a component across the whole source BOM, see the article about the Component Quantity.
The add-in automatically adds filters on each column to facilitate the sorting and to only show relevant information and speed up the search.
The rows are automatically sorted by Distributor then by Class and finally by Unique Identifier if the column names have been entered in the settings, see the article about the Excel Add-in Settings. This will help copy / paste part of the table to do a Request For Quote, issue a Purchase Order, etc…
Some distributor websites support direct copy / paste of Part Number – Quantity pairs like McMaster-Carr, Grainger, Misumi, AutomationDirect, etc… See the article about the Components Identification.
To help the project management, the rows’ background color and the font color can be changed to reflect the state of a component. Updating or importing the BOM – Source does not reset these colors which can be useful to color code some information relevant to your workflow
For example the components being quoted can be purple, the ones in order can be yellow and the ones received can be green, etc…
Removing the rows’ background color (setting it back to “No Color” / “No Fill” in the Format Cells window in Excel) will let the add-in to set the
Even / Odd Rows Color pattern next time the BOM is refreshed.
After the first time the miniPDM – Computed tab is created, it is possible to rearrange the columns. Refreshing the BOM won’t revert it back to the default but the only requirement is to keep the Index column in the first position. This is typically something than can be done automatically by using a Post – Processing function. See the section in this article about the Pre / Post Processing.
It is possible to add additional columns that will be kept during a refresh as long as their name is not a setting variable value. See the article about the Excel Add-in Settings. This is useful to display additional information or do some standard spreadsheet calculation.
Additional Features
Database
If miniPDM is associated with a database, see the article about miniPDM Database, additional features will be available and more information will be computed.
Unique Identifier Generator
miniPDM Excel Add-in can generate unique identifiers with the help of a database to keep track of which number has been already assigned. The importance of having a way to distinguish two components from each other is discussed in the article about Files Naming.
The number generator can generate 10-digits sequential numbers. In the future, it will be possible to call an external generator to customize this unique identifier to your needs.
Auto-Updates
Thanks to Microsoft ClickOnce technology, each time a new version is published, the client will self-update.
Printing
The two sheets are pre-scaled to be ready to print in a landscape arrangement with the headers displayed on each page.
Pre / Post Processing
In a future version it will be possible to execute your own code before and after miniPDM computes the BOM to allow you to do further customization. See the article about Pre / Post Processing.
Component Extended Properties
miniPDM can store component specific properties into a database so it can be used by both the Excel and the SolidWorks add-in allowing more complex processing.
Spare
It is possible to add extra quantity of a component that is not called in the BOM. This is convenient if you want to order more that what is actually needed in the assembly.
Utilization
There are two ways to compute a BOM with miniPDM depending if you want to add it or update it into an existing Excel file or if you want to directly compute the BOM into the source file.
Compute in the Source File
Open the source file in Excel, and in the miniPDM ribbon, click on Run / Update.
It will take a variable amount of time depending of the size of the raw Bill Of Materials, the processor speed and the connection do the database if applicable. During that time, you won’t be able to perform any action on Excel.
If you manually changed a cell value, you can click on Run / Update again to update the miniPDM – Tree and miniPDM – Computed tab content
Note that the Computed tab is driven by the Tree tab so it it advised to make the changed in the Tree tab before a refresh. As pointed before, it is actually better to make the change in the Component Properties itself so this change is permanent and independent of this source BOM.
Compute in an Existing File
If you have an Excel file open that contains or not a miniPDM – Tree tab, you can import a raw source BOM or file containing a miniPDM – Tree tab by clicking on Import on the miniPDM ribbon.
If there was already one, the new imported BOM will entirely replace the existing miniPDM – Tree tab.
Synchronization
When updating or importing the BOM source, the following behavior is to be expected:
- If there was already a miniPDM – Computed tab, updated or added values will be bold, suppressed values will be
struckthroughbut other formatting, like cell color or font for example, will be kept as is. - Columns present in the existing miniPDM – Computed tab but not in the miniPDM – Source will be kept.
- No other tab than miniPDM – Tree and miniPDM – Computed will be affected.
Most of the cells are locked after running the program to prevent unwanted modifications but some specific columns values can be edited:
- Description
- Ref Manufacturer (Ref Distributor)
- Notes
- Spare
Changing these cells will automatically update the database and also update the other tab with the new value. If you are modifying a cell in miniPDM – Tree, the changes will spread to miniPDM – Computed tab and vice versa.
Keep in mind that this synchronization is one way, it will update the database with the Excel values but not the other way unless the database had a value but the Excel one is blank.
Restoration
If you want to rollback to the raw BOM, you can click on Restore on the miniPDM ribbon. It will turn back the miniPDM – Tree tab into the miniPDM – Source tab.
Note that you can only restore a BOM if the miniPDM – Tree tab still exists in the workbook but you can delete the miniPDM – Computed tab. Be careful as you will loose the information that is not present in the
miniPDM – Tree or miniPDM – Source tab like the Spare quantity, color coding or any additional rows and columns.
Component Extended Properties
To open the Extended Properties, you can click on the cell containing the Unique Identifier of the component or on the cell containing the Price or the Extended Price of the component. Each method will open a window with several tabs where the information stored in the database can be displayed and edited. For more information see the article about the Component Extended Properties.
Spare
When entering a numeric value in the Spare column, the whole row becomes Italic to indicate it has to be refresh.
The Spare quantity affects the total Quantity which can have an impact on the Procurement depending of the packing size and the Price if this added quantity helps to reach a different step of bulk discount.
To refresh, you can press the Run / Update button in the miniPDM ribbon.
After the refresh, the whole row will be changed to bold to indicate it has been modified and the Quantity, Procurement and Extended Price will reflect the new total quantity.
