Vendor tracking with EPDM

A big part of engineering products is sourcing vendors, some of which may be tracked in another system possibility governed by the purchasing group.  This tends to be common practice in large corporations, however many smaller scale companies source thier vendors from within the engineering department and maintaining a list of vendors for BOM management can be a disconnected task.

In SolidWorks, many users will enter the part/assembly vendor information into file properties, which can make this easy to access downstream in drawings and bills of materials.  The issue is keeping a running list of vendors we use, tracking contact infomation and even sharing this list for easy selection in file properties.  When implementing EPDM, this question comes up frequently and knowing EPDM comes with MS SQL, a database, ideas start swarming around build a vendor database.  Although this is quite possible, there are ways to integrate this into EPDM without building new interfaces to talk to SQL or maintaining another system for vendors.

Here is how we can do it using the out-of-the-box functionality in EPDM and make this process simple to add, maintain and share your selected vendor list with the engineering group.

Step 1: Create a virtual document template:

Virtual documents can be a great way to use the SQL database to store metadata but not have to maintain a physical file.  You can right click, new -> Virtual Document in the vault view which will ask for a name and extension.  For this example I used "ven" as the extension which yeilds: FILENAME.ven.cvd.

Step 2: Build a file and template card:

Since we want to have an entry form as well as a display form, we will need to have two cards built in the EPDM system.  In my case I wanted to have a slightly different exterience for data entry than for display so I built the two cards to look a little different.  The file card will be associated to the "ven" extension so it only displays for my vendor cards.

 Step 3: Create "Add new vendor Template:

The last piece of the vendor creation will be to tie the first two steps together in order to add the right click menu and add our vendor files to one spot inside the vault.  In my case I have a folder called "Vendors" that I want all these cards to be created in.

Using Templates in EPDM, here is the setup to use the virutal document (*.ven.cvd) as the vender file created in the vault, using the vendor template card for entry and saving it to the "Vendor" folder in my "Standard Libraries" top level folder.

 Now that the template is created, the creation of a vendor named virutal document containing all the important information is stored in the "Vendors" folder.  If we pull up EPDM search, we can use the vendor file card to search for vendors to get details.

Step 4: Create a list from SQL:

Now EPDM contains a virutal roll-a-dex of vendor inside the "Vendors" folder, which is nice but the goal is to capture this list for selection in the part file card. Time for SQL to be our friend...  Create a new list in EPDM called "Vendors" using the data type "From SQL Database" using the following query: 

SELECT     VariableValue.ValueText

FROM        VariableValue INNER JOIN

                 Variable ON VariableValue.VariableID = Variable.VariableID

WHERE     (Variable.VariableName = N'Vendor - Name')

Notice the 'Vendor - Name' in the last line of the query.  This will be the name of the field you use for the vendor name.  Make sure to also set the refresh options to happen peoridically to a time interval that makes sense for the rate of new vendor entry.

Step 5: Add list selection to Part Card:

Edit any file cards that need to specify a vendor from this list, replacing the text field box with a Combobox and attach it to the vendor list created in step 4 under special value.  Now there is a single place where the vendor list grows and is now accessable from any file card in the vault using this new vendor list.  As vendors are added, the list grows and any file in the vault can access the new vendors.

This seems to be a easy solution to add and maintain vendors if indeed it needs to be done in engineering.  Granted this is not going to replace a sophiscited supply chain management system but is better than having the rogue Excel file floating around with hundreds of copies on the network.  ~Lou