Count the documents in a folder and it's subfolders.

I have almost seen this same question asked but with a different spin to it. I just need a LiveReport that will list:Folder Name | # of documents Folder Name | # of documents Folder Name | # of documents Folder Name | # of documents Folder Name | # of documentsFolder Name | # of documentsetc......................I would like the dataid starting point to be user input. I am using an Oracle DB and using Livelink v9.1.0 sp4Any help would be greatly appreciated!Regards,Ann

Find more posts tagged with

Comments

Martin_Gäckler

Hi Ann,here is an example as a starting point:select( select count(*) from dtree c where c.dataid in ( select dt.DOCID from DVersData dt, dtree d where ( ( dt.MIMETYPE like '%application%' and dt.mimetype not like 'application/x-shockwave-flash' ) or dt.mimetype like 'text/html' or dt.mimetype like 'text/richtext' or dt.mimetype like 'text/plain' ) and d.versionnum = dt.version and d.dataid = dt.docid ) and c.name not like chr(37)||'customview.html' and c.name not like chr(37)||'index.html' and c.name not like chr(37)||'.js' and c.name not like chr(37)||'.swf' connect by prior c.dataid=c.parentid start with c.dataid = dtree.dataid) as DocCount,name,dataidfrom dtreewhere parentid = %1order by 1 Desc

adarsh_lobo_(adarsh.lobo@shell.com_(Delete)_112626

Message from <Adarsh.Lobo@shell.com> via eLink

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

Ann,

Not sure if the below code helps. You can give a try.

select d.dataid "Folder ID",d.name "Folder Name",(select count(1) from dtree where parentid=d.dataid and subtype=144) "No. of Docs"from dtree d where d.subtype = 0start with d.dataid = XXXXconnect by prior d.dataid = abs(d.parentid)

Thanks,

Adarsh

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, June 17, 2009 3:16 AM
To: eLink Recipient
Subject: Count the documents in a folder and it's subfolders.

Count the documents in a folder and it's subfolders. Posted by unilev02user2 (Song, Ann) on 2009/06/16 17:45

I have almost seen this same question asked but with a different spin to it. I just need a LiveReport that will list:

Folder Name | # of documents
Folder Name | # of documents
Folder Name | # of documents
Folder Name | # of documents
Folder Name | # of documents
Folder Name | # of documents
etc......................

I would like the dataid starting point to be user input. I am using an Oracle DB and using Livelink v9.1.0 sp4

Any help would be greatly appreciated!

Regards,
Ann

Ann_Song

Wow, I have to admit this is a very sharp SQL.....but we're at LL version 9.1.0 sp4 on an Oracle database and it can't handle the quoted strings.Is there a simpler version with just folders (subfolders) and number of documents per folder without mimetype test?Thanks very much,Ann

Ann_Song

Yes, this is exactly what I was looking for !!!Many thanks !!!Ann

Colin_Schmidt

Now, what I would like is a query that will go through all of the folders and produce a count to determine which ones have the most child objects. Going through one folder at a time would not be possible by LiveReport. And limit it to those over a specified threshold.

Kyle_Swidrovich

Could you explain why listing each folder would not be possible with a LiveReport?Are you looking for a cumulative child count? That is, given a folder, you want a count of all child objects, including objects in subfolders?If not, a very simple query can query DTree for SubType=0, returning the ChildCount column, which could then be ordered by ChildCount (descending). This would show you the folders who have large numbers of immediate children.