Join back to DOcMaster table from Projects

Ok, so this lists the email folders in a workspace who have no value in the EMAIL field...right? I assume where PRJ_Name = 'Email'...means those folders...

Select * From MHGROUP.Projects Where PRJ_Name = 'Email' And EMAIL Is Null

Assuming that's true...what would join back to the DocMaster table? PRJ_PID? TREE_ID? DocNum?

Thanks...

Find more posts tagged with

Comments

sschro

Pretty confident I figured this out.

Select P.* From MHGROUP.Projects P
Inner Join MHGROUP.Projects P2 On P2.Tree_ID = P.Tree_ID And P2.SubType = 'work'
Inner Join MHGROUP.DocMaster D On D.DocNum = P2.DocNum
Where P.PRJ_Name = 'Email' And P.EMAIL Is Null

Because there is both a workspace record, and a workspace folder record (Email) in Projects, in order to get back to DocMaster, you have to join in Projects a second time, using an alias, joining the two on Tree_ID and you can then get back to DocMaster via DocNum on the second Projects table, P2 in this case.

So the Above query would list those Email folders with no email value, where you could then use to get C1ALIAS, C2ALIAS.

Hence:

Update MHGROUP.Projects Set EMAIL = D.C1ALIAS + '.' + D.C2ALIAS
From MHGROUP.Projects
Inner Join MHGROUP.Projects P1 On P1.Tree_ID = MHGROUP.Projects.Tree_ID And P1.SubType = 'work'
Inner Join MHGROUP.DocMaster D On D.DocNum = P1.DocNum
Where MHGROUP.Projects.PRJ_Name = 'Email' And MHGROUP.Projects.EMAIL Is Null

jny

Pretty confident I figured this out.

Select P.* From MHGROUP.Projects P
Inner Join MHGROUP.Projects P2 On P2.Tree_ID = P.Tree_ID And P2.SubType = 'work'
Inner Join MHGROUP.DocMaster D On D.DocNum = P2.DocNum
Where P.PRJ_Name = 'Email' And P.EMAIL Is Null

Because there is both a workspace record, and a workspace folder record (Email) in Projects, in order to get back to DocMaster, you have to join in Projects a second time, using an alias, joining the two on Tree_ID and you can then get back to DocMaster via DocNum on the second Projects table, P2 in this case.

So the Above query would list those Email folders with no email value, where you could then use to get C1ALIAS, C2ALIAS.

Hence:

Update MHGROUP.Projects Set EMAIL = D.C1ALIAS + '.' + D.C2ALIAS
From MHGROUP.Projects
Inner Join MHGROUP.Projects P1 On P1.Tree_ID = MHGROUP.Projects.Tree_ID And P1.SubType = 'work'
Inner Join MHGROUP.DocMaster D On D.DocNum = P1.DocNum
Where MHGROUP.Projects.PRJ_Name = 'Email' And MHGROUP.Projects.EMAIL Is Null

Would you explain what you're trying to accomplish? I don't recommend going through SQL. I might be able to help with using the API's instead but I need to better understand what you're trying to find - not how you are trying to find it.

sschro

Well the use of DocAuto's Workspace Manager does in fact involve some usage of SQL.

Our issue was the EMAIL value of the Project folder 'EMail' was not properly set. My goal was to identify those records in the project table, joining in the DocMaster table so that I could retrieve the C1ALIAS.C2ALIAS values to properly populate those records.

Arwin

In the query above, don't you need to use the PROJECT_ITEMS table to get from projects to documents?

E.g. for a project in the projects table, you need to join in the project_items table on the project id, and then join the docmaster table on the item_id and version of the project_items table vs the docnum and version of the docmaster table.

This in addition to getting all the folders that belong to a specific workspace first, of course.

Through the API, I think you can do a query on all documents of a given workspace easily enough, and then specify what type you want. You can't, however, currently use the API to query for empty values, so you'd have to test those one by one.

sschro

In the query above, don't you need to use the PROJECT_ITEMS table to get from projects to documents?

E.g. for a project in the projects table, you need to join in the project_items table on the project id, and then join the docmaster table on the item_id and version of the project_items table vs the docnum and version of the docmaster table.

This in addition to getting all the folders that belong to a specific workspace first, of course.

Through the API, I think you can do a query on all documents of a given workspace easily enough, and then specify what type you want. You can't, however, currently use the API to query for empty values, so you'd have to test those one by one.

Not sure if what you're saying applies to what I was trying to accomplish or not.

My goal was to return those folders named 'Email' where the Projects.EMAIL field was null, and to include the C1ALIAS, C2ALIAS value from DocMaster, for in fact the value of Projects.EMAIL should have been 'C1ALIAS.C2ALIAS'

If there is more than one way to get those values, great, I but I know this did work.

Am I making sense?

What specifically do you mean by the API? I mean, I know what an API is, but in the context of Worksite...?

Arwin

My mistake! I had just run several queries for documents in folders and I thought you needed documents, not just folders.

Worksite also has a COM interface (iManage.dll) which you can use. It contains among others functions to search for folders, documents and workspaces, and they are returned to you as objects with properties and such.

You can get a workspace object, and then go through its collection of documentfolders to check if their EmailPrefix property has the correct value, and adjust it if necessary.

This is a cleaner method, but not always the most practical of course if you're well versed in SQL and not in the API of a certain application, because unlike APIs, SQL and relational databases have at least the same grammar. Smiley Wink

However, programming against the API is a lot safer, and if I remember correctly, directly editing SQL tables invalidates WorkSite's warranty. It's not always possible to avoid it, but if we can avoid it for our customers, we do so.

sschro

My mistake! I had just run several queries for documents in folders and I thought you needed documents, not just folders.

Worksite also has a COM interface (iManage.dll) which you can use. It contains among others functions to search for folders, documents and workspaces, and they are returned to you as objects with properties and such.

You can get a workspace object, and then go through its collection of documentfolders to check if their EmailPrefix property has the correct value, and adjust it if necessary.

This is a cleaner method, but not always the most practical of course if you're well versed in SQL and not in the API of a certain application, because unlike APIs, SQL and relational databases have at least the same grammar.

However, programming against the API is a lot safer, and if I remember correctly, directly editing SQL tables invalidates WorkSite's warranty. It's not always possible to avoid it, but if we can avoid it for our customers, we do so.

Yes, I used that API to create a Worksite add-in to rename document descriptions. A user can select 2 or more documents and either replace or append text to a document description. See here:

Is there an official document that describes this particular point, where you describe how using T-SQL on the back-end invalidates the warranty or support contract? Thanks.

I don't know how it would be avoidable, especially within the first few months of implementation where not all the business processes are completed ironed out.

Migrateduser

Hi

I am haviing a similar issue.

I would like to know where the document resides in an Imanage Workspace using SQL (similar to "where used" in Desksite). I understand that the PROJECTS table as PRJ_PID field which references the top-level folder.

I don't know how to query the tables which will give me the full workspace path and returns the path

I have a number of documents that are linked to a number of folder workspaces - similar to below.

Folder A (document A resides here)
Folder Sub A
Folder Sub B (document A resides here)
Folder Sub C

Is it possible using SQL that I can find where the document resides (using the full path)

So it would tell me that document A is located af Folder A

AND

Document A is located At Folder A\Folder Sub A\Folder Sub B

jny

Hi

I am haviing a similar issue.

I would like to know where the document resides in an Imanage Workspace using SQL (similar to "where used" in Desksite). I understand that the PROJECTS table as PRJ_PID field which references the top-level folder.

I don't know how to query the tables which will give me the full workspace path and returns the path

I have a number of documents that are linked to a number of folder workspaces - similar to below.

Folder A (document A resides here)
Folder Sub A
Folder Sub B (document A resides here)
Folder Sub C

Is it possible using SQL that I can find where the document resides (using the full path)

So it would tell me that document A is located af Folder A

AND

Document A is located At Folder A\Folder Sub A\Folder Sub B

You can accomplish this type of reverse lookup by iterating through the folders collection belonging to the document, like so:

[VB] 'Referenced imanage.dll 8.2

'A Windows Form App
==========================

Option Explicit

Private Sub Form_Load()
Dim dms As New ManDMS
Dim sess As IManSession
Dim db As IManDatabase
Dim doc As IManDocument
Dim fldr As IManFolder
Dim fldrs As IManFolders

Dim fullPath As String

Set sess = dms.Sessions.Add("DSS_WIN2003SERV")
sess.TrustedLogin

Set db = sess.PreferredDatabase
Set doc = db.GetDocument(1830, 1)

' Get all folders containing the references of the document object
For Each fldr In doc.Folders
fullPath = GetFullpath(fldr)
Debug.Print fullPath
Next fldr

sess.Logout
dms.Close
Set doc = Nothing
Set fldr = Nothing
Set fldrs = Nothing
Set db = Nothing
Set sess = Nothing
Set dms = Nothing
End
End Sub

Private Function GetFullpath(fldr As IManFolder) As String
Dim fldrs As IManFolders
Dim sPath As String
Dim i As Long

sPath = "\\" & fldr.Database.Name
Set fldrs = fldr.Path
For i = 1 To fldrs.Count
sPath = sPath & "\\" & fldrs.ItemByIndex(i).Name
Next i
GetFullpath = sPath
Set fldrs = Nothing
End Function

==========================

jny

Yes, I used that API to create a Worksite add-in to rename document descriptions. A user can select 2 or more documents and either replace or append text to a document description. See here:

Is there an official document that describes this particular point, where you describe how using T-SQL on the back-end invalidates the warranty or support contract? Thanks.

I don't know how it would be avoidable, especially within the first few months of implementation where not all the business processes are completed ironed out.

Do you mean that you would like to search for all document folders that have the name, "EMail". Then go through each folder by the name "EMail" to see update the EmailPrefix field value when it's empty?