Need a report, which shows me, in which collections a document is

I am trying now for a few hours to write a report, which gives me the collections, which contain a certain document. Sounds simple, but the reality is not that simple...Input to report: Object ID of a documentDesired output: Object ID(s) and Name(s) of the collections, which contain the documentWe are running Livelink 9.7.1 here....Thanks in advance for help on this topic...!!!

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

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

Hi Torsten,

Looks pretty straightforward to me.

The COLLECTIONS table is a many-to-many relationship table.

CollectionID and DataID are each foreign keys to DTree.

CollectionID points to the collection object (subtype=298) in DTree.

DataID points to the item(s) in DTree that are in the collection.

So I would start with the following query

select d.DataID, Name from DTree d where d.DataID in (select distinct(CollectionID) from collections c where c.DataID = %1)

%1 should be defined as User Input 1.

If input 1 is defined as Integer you put in the DataID of the object you are looking for.

Otherwise, if you define it as NodeID, you can query in the document in a pop-up window.

I have explicitly qualified the common column names with the table aliases to avoid confusion, but the RDBMS will not require that.

This will work for the autolivereport output format.

If you want a proper LiveReport output format the select %2 from DTree (where %2 is defined as a Parameter "Report Fields") will work.

You might want to embellish the report with the details of the user who created the collection...

select d.DataID, d.Name CollectionName, k.name Login, FirstName,LastName from DTree d, KUAF k
where d.DataID in (select distinct(CollectionID) from collections c where c.DataID = %1)

and d.UserID = k.ID

Notice that I had to make the column names unique to get the correct columns to appear on the report - otherwise both d.Name and k.Name came out as just Name and both contained the k.Name data.

Hope this helps.

Regards

Lindsay Davies

EU Escalations Team

Open Text

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2010 September 23, Thu 09:50
To: eLink Recipient
Subject: Need a report, which shows me, in which collections a document is

Need a report, which shows me, in which collections a document is

Posted by torsten.bietz@lurgi.com (Bietz, Torsten M) on 2010/09/23 04:46

I am trying now for a few hours to write a report, which gives me the collections, which contain a certain document. Sounds simple, but the reality is not that simple...

Input to report: Object ID of a document
Desired output: Object ID(s) and Name(s) of the collections, which contain the document

We are running Livelink 9.7.1 here....

Thanks in advance for help on this topic...!!!

Torsten_Bietz

Dear Lindsay,which addess can I give to Fleurop, to send you some flowers...??? :-)Thank you so much for your help, the report works exactly the way I wanted...RegardsTorsten M. Bietz