if you are trying to get data from enterprise, why not use the report generator?
i say this because the tables have connections EVERYWHERE.
The query is a huge join where PartNumber is a variableID and the value lies in VariableValues
but the connection to it is Documents
a better method might be to write an excel addin, super easy to do.
what you can do then is use the EDMLib library to run searches for files that = "12345%" which means starts with 12345
with the IEDMSearch it returns the Search Object which you can use to Get the file.
i am trying to steer you away from the sql search because you will have probably 10 joins in your query
all this to get where the workflow state of a part number that will apply to possibly many files.
So your query will be something like this
you will need to look in Variables to find the part number variable id
you can query this but it wont ever change so hard code it, mine is 98
Part number lies in DBO.VariableValue where VariableID = your part number variable ID
ConfigurationID also is retrieved from DBO.Variablevalue.ConfigurationID
however its likely you won't need to use this
You need to get documentID from DBO.VariableValue which is retrieved from DBO.VariableValue.DocumentID
you then can get data from the DBO.Document
CurrentStatusID is in DBO.Documents. Use this to join to DBO.Status where DBO.Documents.Status = DBO.Status.Statusid
you now have access to DBO.Status.Name which is what you are looking for.
Clear as mud?
also. be very very careful. you could destroy your vault if you dont make sure its a read only connection. if you do a bound connection, you will destroy your vault and solidworks will NOT help you fix it as it is not supported to do direct database modifications
Definitely good advice on "read only". I am careful to pick this option when I connect to the tables. But, I do agree... it does seem rather fragile. One slip-up and its off the the daily backup.
We are currently using standard. Thus, no reports. What I am trying to accomplish is a dynamic excel table that can be used to show BOMS and their current state that non-engineers can use in development projects. Real-time view of the bom will help purchasing know when its okay to submit for quotes, etc. Having all of that info ready at a glance and always fresh data is really nice.
In the Excel Query Builder, you can select the required columns and load all data records. Then you can search for the "PartNumber" with Excel filters. In my opinion, this is the simplest way. However, if you need to join multiple tables, you probably won't get by without SQL.
Makes sense. There are a lot of tables out there. I was hoping for some help in knowing which ones to join.
i told you which ones to join above.
Ah yes, seems I didn't pick "show earlier"... ugh...
Just a small piece of advice.
Always try to view replies from the main thread itself rather than just your inbox otherwise you could possibly miss attached pictures, links or attached zipped files that people may have sent you.
If you are familiar with Excel macros, this might help you. The Excel file containing the macro must contain two sheets with the names from the constants tab00 and tab01. Adjust the constants connString, tab00, tab01 and insert the code into a new module. Type the filename filter in tab00 cell(A1). For example, %.sldasm for all assemblies. When the macro is executed, you receive the current status of the files. Note that this is only an example and no error handling is implemented. For security reasons, you should store the SQL query in a Stored Procedure, since this cannot be modified without authorizations. I would also create an user who only has authorization to read the tables or columns that are really needed.
Option Explicit On
Const connString = "Provider=SQLOLEDB.1;User ID=userName;Password=userPassword;Data Source=serverName;Initial Catalog=dbName"
Const tab00 = "Tab00"
Const tab01 = "Tab01"
' Get file name
Dim fName As String
fName = Worksheets(tab00).Cells(1, 1)
If fName <> "" Then
' Clear sheet
Dim adoCn As Object
Set adoCn = CreateObject("ADODB.Connection")
Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")
adoCn.ConnectionString = connString
Dim sqlQuery As String
sqlQuery = "SELECT T0.Filename, T1.Name FROM Documents T0 INNER JOIN Status T1 " _
& "ON T0.CurrentStatusID = T1.StatusID WHERE T0.Filename Like N'" & fName & "'"
If adoCn.State = 1 Then
adoRs.Open sqlQuery, adoCn, 0, 1
If Not (adoRs Is Nothing) Then
If Not (adoRs Is Nothing) Then
If adoRs.State = 1 Then
Set adoRs = Nothing
If Not (adoCn Is Nothing) Then
If adoCn.State = 1 Then
Set adoCn = Nothing
It is a nice idea, but the result would not be "live". Its would be a one-time dump. I need to have a list that someone can work in, and have data update as the components in the design are released, checked out/pending, etc.
i say get rid of excel. write a stand alone application. Avoid Interop code at all cost.
Rodney, are you familar with sql queries and joins?
i figured you were so i left it at what i posted earlier. if you need the joins let me know.
I have not written an SQL query. I have just used the query builder in Access & Excel to build them with the GUI.
I only want excel because you can build a simple spreadsheet, and do other functions, but then have a live link to the part data. I have used this in an SAP environment in the past, and the time savings to just normal individuals is tremendous.
I eventually want to turn this into functions in excel... such as::: =SWPartName(PartNo) Returns the part name/description of a part based on the input part number. I can have many such functions for a lot of the data. Anyone can then get this info using simple excel functions.
Another way I could foresee doing this is if I can have SW dump a report every so often that my excel can connect to and use no queries. Is it possible to run a report (I don't have PDM Pro at the moment) and have it update every hour or so? Then, its just a vlookup out of the resulting table. Having data update every hour isn't perfect, but its close enough.
i understand using excel. but for less time and suffering, you could write a simple .net form that self publishes that only allows end users to do what you want them to be able to do.
alternatively writing an excel addin is extremely easy also.
The new excel addins also self publish when you deploy them.
which is Awesome.
Now if only solidworks was capable of doing anything cool like that. But they aren't. they are too busy making the program fat and slow and unstable.
Yes, I think the add-in would be the thing to try first. But, since I havent dont those before... can you send me where I need to go to do it? Also, I presume a data dump via a report is the way to get a file updated on a regular frequency. But, that is a PDM Pro function... Yes? I am simply not familiar enough with .net. I'm savvy with many computer languages... perhaps I can pick it up if I am sent to where I can start seeing how that is done. Any references?
if you have visual studio
start a new project
they make it so easy to do now.
if you don't have visual studio you will need to get it to use this template. it is the only way i have done it so beyond that i cant help.
with an add-in like this, you can basically make it take over specific documents.
so if it is for a report, when the C2 field changes and its part number
you can have it pre fill out everything else.
add links to the file in enterprise to open , like right click Part number cell and have a menu item say open file, location, ect
i do this a lot to automate things internally here, using a .net add in is the only way to do it in my opinion.
one really cool thing is if you have office 365 at your company, these can be deployed through the website to whomever you chose should get it.