File Location and Status Query in SOLIDWORKS PDM Professional

Two common technical support cases that come through our support queues for SOLIDWORKS PDM are for assistance in understanding why a file cannot be checked in with a “duplicate file name” message, and for help finding a file that “magically disappeared”. Most of these support cases simply need a little explanation as to what is happening. To facilitate understanding and locating such files, I have developed a Transact-SQL query and saved as a Conisio Report file (.crp) that can be safely ran through the Report Generator feature of SOLIDWORKS PDM Professional. PDM Administrators and colleages to whom I have shared this query have found it very useful. This document will provide the query to the public as-is, elaborate on the functionality of this query, provide how to use the query, and provide a few use case scenarios/examples. Note that this feature is not included in SOLIDWORKS PDM STANDARD.

Logistics

How do I install and run a .crp file query?

We have created a document to help with the process of Installing and Using a Report Generator File

Where can I get your File Location and Status.crp file?

You can download the latest version of this .crp file here: File_Location_&_Status.crp

 

The File Location and Status Query

The File Location and Status query works like a PDM search. However, it is not bound by the visibility restrictions of set permissions nor the limitations of the PDM search cards which do not show deleted files in the results. It does not return files themselves like a PDM search card does, it is just a table of metadata from the vault database. It is a very inexpensive query, meaning that it will not use much database server CPU to complete a large search. The query is particularly useful in situations where a file cannot be found through regular use of PDM search cards. See use case scenarios below.

 

How to use the File Location and Status Query

The search interface of the query is simply an input text box where you can search for a specific file by providing its precise file name with extension, or you can use percent signs “%” as wildcards in place of variant text.

In the screenshot above, %.sld% is used as search criteria. This text means that you want to output information about all files in the entire vault that has the text .sld in the first three characters of the extension. The output results would include but not be limited to .sldasm, .sldprt, and .slddrw files. If you are interested in only Microsoft Excel files, for instance, you could search for %.xlsx or %.xls% if you also need Excel files with macros (.xlsm).

Clicking OK on the search interface of the query will output the flowing information:

• File Name (Useful column when using wildcards)
• File Location – Column called “Located In”
• Deleted (Yes or No)
• Version Count
• Checked Out By (User name is provided, or “checked in” if not checked out by anyone)
• Current State (Name of the Workflow State that the latest version is in)
• Workflow Name (Name of the workflow that the latest version is in)
• Document ID (for advanced purposes)
• Hexadecimal Document ID (for advanced purposes)

 

Query Results Example

Note: The file locations are relative to the root folder of the vault. File cyl.sldprt is located at the root of the vault. Path of “\” means C:\MyVaultname\.

Use case scenarios

For a few of these scenarios, we will use the example output table above to illustrate. A key thing to note is that the situation happened either because of visibility permissions, which may be intentionally placed for a user, or the fact that PDM searches do not return deleted items as search results.

My file disappeared!

Typically, a file can no longer be found because of these possible things:

1. File has been deleted or destroyed
2. File has been moved
3. No longer have permissions to see the file
4. File has been renamed

Currently, the query can be used to determine if the file cannot be found because of 1, 2, or 3. In a future version of this query, additional functionality will be added to determine if the file being searched has been renamed (4) – stay tuned.

For this first scenario, you may know the name or part of the file name that you are looking for. Suppose you were looking for “Mold Top” and “Mold Bottom” files which are SOLIDWORKS part files. Using the PDM search did not yield results. However, running the query through the Report Generator and searching for Mold%.sldprt will show you that the files are still in the vault, but have been deleted. (see example table) You may recover them by navigating to that path > right clicking on the folder indicated > properties > deleted items tab.  If there are no query results, then the files may have been destroyed, or renamed.

In a second scenario, suppose you are the designer searching for cyl.sldprt using PDM search, you get no results. You run the query to search for cyl.sldprt and find that it is in a “waiting for approval” state in which only approvers can see files.

Suppose you are the approver and can’t find the file cyl.sldprt through PDM search. You log out as your user profile and log in as the Admin super-user to run a PDM search again, but still no results.  You then run the query and find that it has been misplaced to a location which you do not have folder permissions to see files. You are now aware of what is happening and can approach the issue accordingly.  Keep in mind that even the Admin super-user can have file visibility permissions revoked for X reason. The query may be your only way to find information about a file you are looking for.

I cannot check in file…“Duplicate file name” message

Having a message saying that your file cannot be checked in because of duplicate file name restriction, implies that there is already a file in the system that has the same name. Doing a search with search cards may not return the file location of the file that is already in the system. Running the query and searching for the name of the file you are trying to check in, you find the file that is already in the vault. The file already in the vault may have been deleted and may need to be destroyed. Another scenario may be that it is in a workflow state or a folder in which you do not have visibility permissions. Whichever the case, you now know what the matter is and can now approach your PDM Administrator with the appropriate request that would allow you to check in your file.