URL Report

Our users have a need for a report which will give them the URL of a 'Fetch' for all docs beneath a particular project. I've had success with the SQL for Word and Excel files, but not for PDFs, due to difference in the syntax of URL strings. The URL for a PDF contains the DataIDs of all the Parent/Owner IDs, as shown below:URL FOR A PDF:http://docdev1.genzyme.com/dev91m/livelink.exe/fetch/1416510/2367762/1740319/1755085/PDF_WITH_EXTENSION.PDF?nodeid=1755088&vernum=0(where 1755085 = ParentID, and 1416510 = - OwnerID )URL FOR A WORD FILE:http://docdev1.genzyme.com/dev91m/livelink.exe/1755090/WORD_NO_EXTENSION.doc?func=doc.Fetch&nodeid=1755090Below is the SQL I'm using to generate the URL for Word/Excel:SELECT d.DataID "Data ID", d.Name "Object Title", v.DataSize "Bytes", 'http://mdocdev1.genzyme.com/dev91m/livelink.exe/'||d.DataID||'/'||REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(d.Name),' ', '_'), '#', '_'), '.XLS', ''), '.DOC', ''), '.RTF', '')||CASE WHEN UPPER(d.Name) LIKE '%%.RTF' THEN '.rtf' WHEN v.MimeType IN ('application/vnd.ms-word', 'application/msword', 'application/rtf') THEN '.doc' WHEN v.MimeType LIKE 'application/vnd.ms-excel' AND UPPER(d.Name) LIKE '%%.XLS' THEN '.XLS' ELSE '' END||'?func=doc.Fetch&nodeId='||d.DataID URL FROM DTree d, DVersData v WHERE d.DataID IN ( SELECT DataID FROM DTree START WITH DataID=412754 CONNECT BY PRIOR DataID=ABS(ParentID) ) AND d.DataID=v.DocID AND d.VersionNum=v.Version AND v.MimeType IN ('application/vnd.ms-word', 'application/msword', 'application/rtf', 'application/vnd.ms-excel')Does anyone have any advice on how modify the SQL above to build the URL string for PDF files?Thank you,Su

Find more posts tagged with

Comments

Marie_Lindsay_(MLindsay_(Delete)_15608)

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

What happens if you just use a URL like http://docdev1.genzyme.com/dev91m/livelink.exe?func=doc.Fetch&nodeid=1755088 for PDF files? I often use links like these when adding links to Word files or mail messages.

I also wonder whether using the "LiveReport" report format could save you some effort here -- ?

Marie

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, August 06, 2003 10:33 AM
To: eLink Recipient
Subject: URL Report

URL Report Posted by GenzymeUser5 (Roberts, S) on 08/06/2003 11:29 AM

Our users have a need for a report which will give them the URL of a 'Fetch' for all docs beneath a particular project. I've had success with the SQL for Word and Excel files, but not for PDFs, due to difference in the syntax of URL strings. The URL for a PDF contains the DataIDs of all the Parent/Owner IDs, as shown below:

URL FOR A PDF:
http://docdev1.genzyme.com/dev91m/livelink.exe/fetch/1416510/2367762/1740319/1755085/PDF_WITH_EXTENSION.PDF?nodeid=1755088&vernum=0

(where 1755085 = ParentID, and 1416510 = - OwnerID )

URL FOR A WORD FILE:
http://docdev1.genzyme.com/dev91m/livelink.exe/1755090/WORD_NO_EXTENSION.doc?func=doc.Fetch&nodeid=1755090

Below is the SQL I'm using to generate the URL for Word/Excel:
SELECT d.DataID "Data ID", d.Name "Object Title", v.DataSize "Bytes", 'http://mdocdev1.genzyme.com/dev91m/livelink.exe/'||d.DataID||'/'||REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(d.Name),' ', '_'), '#', '_'), '.XLS', ''), '.DOC', ''), '.RTF', '')||CASE WHEN UPPER(d.Name) LIKE '%%.RTF' THEN '.rtf' WHEN v.MimeType IN ('application/vnd.ms-word', 'application/msword', 'application/rtf') THEN '.doc' WHEN v.MimeType LIKE 'application/vnd.ms-excel' AND UPPER(d.Name) LIKE '%%.XLS' THEN '.XLS' ELSE '' END||'?func=doc.Fetch&nodeId='||d.DataID URL FROM DTree d, DVersData v WHERE d.DataID IN ( SELECT DataID FROM DTree START WITH DataID=412754 CONNECT BY PRIOR DataID=ABS(ParentID) ) AND d.DataID=v.DocID AND d.VersionNum=v.Version AND v.MimeType IN ('application/vnd.ms-word', 'application/msword', 'application/rtf', 'application/vnd.ms-excel')

Does anyone have any advice on how modify the SQL above to build the URL string for PDF files?

Thank you,

Su

Marie_Lindsay_(MLindsay_(Delete)_15608)

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Sorry - to clarify: "I often use links like these when adding links to Word files or mail messages." means "I often use links like these when adding links to PDF files within Word files or mail messages."

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, August 06, 2003 11:06 AM
To: eLink Recipient
Subject: RE URL Report

RE URL Report Posted by MLindsay (Lindsay, Marie) on 08/06/2003 12:05 PM
In reply to: URL Report Posted by GenzymeUser5 (Roberts, S) on 08/06/2003 11:29 AM

Message from Marie Lindsay <marie@opentext.com> via eLink

What happens if you just use a URL like http://docdev1.genzyme.com/dev91m/livelink.exe?func=doc.Fetch&nodeid=1755088 for PDF files? I often use links like these when adding links to Word files or mail messages. I also wonder whether using the "LiveReport" report format could save you some effort here -- ? Marie -----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, August 06, 2003 10:33 AM
To: eLink Recipient
Subject: URL Report

URL Report

Posted by GenzymeUser5 (Roberts, S) on 08/06/2003 11:29 AM

Our users have a need for a report which will give them the URL of a 'Fetch' for all docs beneath a particular project. I've had success with the SQL for Word and Excel files, but not for PDFs, due to difference in the syntax of URL strings. The URL for a PDF contains the DataIDs of all the Parent/Owner IDs, as shown below:

URL FOR A PDF:
http://docdev1.genzyme.com/dev91m/livelink.exe/fetch/1416510/2367762/1740319/1755085/PDF_WITH_EXTENSION.PDF?nodeid=1755088&vernum=0

(where 1755085 = ParentID, and 1416510 = - OwnerID )

URL FOR A WORD FILE:
http://docdev1.genzyme.com/dev91m/livelink.exe/1755090/WORD_NO_EXTENSION.doc?func=doc.Fetch&nodeid=1755090

Below is the SQL I'm using to generate the URL for Word/Excel:
SELECT d.DataID "Data ID", d.Name "Object Title", v.DataSize "Bytes", 'http://mdocdev1.genzyme.com/dev91m/livelink.exe/'||d.DataID||'/'||REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(d.Name),' ', '_'), '#', '_'), '.XLS', ''), '.DOC', ''), '.RTF', '')||CASE WHEN UPPER(d.Name) LIKE '%%.RTF' THEN '.rtf' WHEN v.MimeType IN ('application/vnd.ms-word', 'application/msword', 'application/rtf') THEN '.doc' WHEN v.MimeType LIKE 'application/vnd.ms-excel' AND UPPER(d.Name) LIKE '%%.XLS' THEN '.XLS' ELSE '' END||'?func=doc.Fetch&nodeId='||d.DataID URL FROM DTree d, DVersData v WHERE d.DataID IN ( SELECT DataID FROM DTree START WITH DataID=412754 CONNECT BY PRIOR DataID=ABS(ParentID) ) AND d.DataID=v.DocID AND d.VersionNum=v.Version AND v.MimeType IN ('application/vnd.ms-word', 'application/msword', 'application/rtf', 'application/vnd.ms-excel')

Does anyone have any advice on how modify the SQL above to build the URL string for PDF files?

Thank you,

Su

Susan_Roberts

Marie-Thank you for your reply. Oh, how I wish it were that easy! Unfortunately our users have an Access database full of Livelink URLs which were gathered by using the Function/Fetch method. They need to match these URLs with the ones that I build as part of a LiveReport (long story, don't ask why!).I'm trying them to convince them to do a Right-click/Copy Shortcut ( which would look like this: http://docdev1.genzyme.com/dev91m/livelink.exe?func=ll&objId=413581&objAction=Open) but I don't think I'm going to win because that would involve updating ~11,000 entries in their Access database.

Marie_Lindsay_(MLindsay_(Delete)_15608)

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Ugh, that sounds rough. It'd take me a loooonnng time to figure out the SQL for this -- I'd probably go back to my users and convince them to alter their requirements a bit, since that would take less time. But maybe someone who is more expert at SQL can give you some clues here.

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, August 06, 2003 11:51 AM
To: eLink Recipient
Subject: Marie-

Marie- Posted by GenzymeUser5 (Roberts, S) on 08/06/2003 12:43 PM
In reply to: RE RE URL Report Posted by MLindsay (Lindsay, Marie) on 08/06/2003 12:20 PM

Marie-

Thank you for your reply. Oh, how I wish it were that easy! Unfortunately our users have an Access database full of Livelink URLs which were gathered by using the Function/Fetch method. They need to match these URLs with the ones that I build as part of a LiveReport (long story, don't ask why!).

I'm trying them to convince them to do a Right-click/Copy Shortcut ( which would look like this: http://docdev1.genzyme.com/dev91m/livelink.exe?func=ll&objId=413581&objAction=Open) but I don't think I'm going to win because that would involve updating ~11,000 entries in their Access database.

Harald_Lange_(harald.lange@nsn.com_(Delete)_231178

Message from Lange Harald via eLinkI don't know what you mean by "They need to match these URLs...", but I can say that ANY Livelink URL points to the same object as soon as it points to the same ObjectID - no matter how the rest of the link is formed. So to perform sth. like a consistency check ("...match these URLS..."), I would concentrate on the ObjectIDs ONLY.Best Regards,Harald Lange--------------------------------------------------------Harald Lange IMS Content Management ICMICM IT eA 2 !New! Tel: +49 89 636 53812 (Mon-Wed.)Siemens AG !New! Mailto:Harald.Lange@siemens.com--------------------------------------------------------> -----Ursprüngliche Nachricht-----> Von: eLink Discussion: Livelink LiveReports Discussion > [mailto:livereportsdiscussion@elinkkc.opentext.com] > Gesendet: Mittwoch, 6. August 2003 19:40> An: eLink Recipient> Betreff: RE URL Report - Access URLs> > > >