Team,
Can someone tell me if it is possible to find all the fasteners in out vault by writing a select statement using T-SQL? I want to make sure users are not copying these files out of the designated location and putting them in other folders. Maybe by the File Type of something?
SELECT D.Filename ,P.Path FROM [Documents] D JOIN DocumentsInProjects DIP ON DIP.DocumentID = D.DocumentID JOIN Projects P ON P.ProjectID = DIP.ProjectID WHERE D.DocTypeID = 3
DocTypeID = 3 is a toolbox part.
You could filter it a little more by adding something like -> AND P.Path NOT LIKE '\Toolbox%'.
Thanks Lee for the response. Although I think this is pretty close, not exactly what I was looking for. Maybe this is not do-able. I think if I am understanding this correctly, the DocTypeID is what EPDM call the Catagory, correct? If so, what I am trying to do, that wont work because a toolbox part can have the same catagory as any solidworks file.
Sorry, I am sure I am confusing you now.
Ah, yes, sorry about that. The DocType does map to categories.
Try this:
SELECT D.Filename ,P.Path FROM [Documents] D JOIN DocumentsInProjects DIP ON DIP.DocumentID = D.DocumentID JOIN Projects P ON P.ProjectID = DIP.ProjectID WHERE D.Flags = 2 OR D.Flags = 4
Lee,
THIS IS IT!!!!! Thanks so much for your help!
Lee,
Can you help me understand what exactly these two lines are doing?
JOIN DocumentsInProjects DIP ON DIP.DocumentID = D.DocumentID
JOIN Projects P ON P.ProjectID = DIP.ProjectID
I understand you are Joining the two tables, declaring DIP as DocumentsInProjects, but why are you having to join DIP.DocumentID and make it equal to Document.DocumentID?
Secondly, again, I understand the second Join, I just dont know why you have to make Project.ProjectID equal to DIP.ProjectID?
I hope I made sence there.
Again, this is what I wanted, but I just want to understand so I can start doing this stuff myself.
Thanks Again Man!
Rodney
It has to do with the structure of the database. The Documents table lists the documents and the Projects table lists the projects (EPDMese for folders), but the interconnecting table is DocumentsInProjects, which provides the links between each document and its project.
The query asks for a field from the Documents folder (D.Filename) and a field from the Projects folder (P.Path) for all records in which D.Flags = 2 or 4. In order to look up the Path field from the Projects table which corresponds to the selected document in Documents, joins are used to follow the connection from Documents through DocumentsInProjects to Projects.
The link between Documents and DocumentsInProjects is the shared key field 'DocumentID'
The link between DocumentsInProjects and Projects is the shared key field 'ProjectID'
So in order for a document to 'see' a value relating to the project (folder) that it is associated with, it must follow the shared key field links through the interconnecting table.
Clear as mud?
I believe what you explained was well put. I think what I understood was, unless I join those two table together, I cant see the document and its path at the same time, correct?
Correct!
Now, one final comment. Is there a table that explains to me what values the ".Flags" are?
Meaning...
D.Flags = 2 OR D.Flags = 4
Hmmm... good question. I couldn't find anything relevant in the API guide... Perhaps a call to your VAR would be helpful.
Funny you say that. I did ask them about my initial question, and I still haven't heard back from them.
Thanks Brian and Lee for all your help!
Ah, yes, sorry about that. The DocType does map to categories.
Try this: