Livereport to return new subfolders

[brettsanders]

I'm looking for some assistance to build a live report to return all new folders created one level below the location specified. We want record coordinators to be able to see any new folders that are created outside of the defined folder structure. I would like to return the folder name, docid, created date, creator--if possible.

 

[appnair]

if you are in oracle this should sufice with some minor tweaks.

If Folder A is in your enterprise and has an objid=1234 your typical oracle query would look something like this.

select name,dataid,parentid,level,createdate,createdby from dtree
where subtype=0 start with dataid=1234 connect by prior dataid=parentid

In this case what we are saying is travesre all the childeren under that ID and give m only folders subtype=0.The userid is a number that is coming from kuaf.if you would like the pretty name we just haveto link it to kuaf and bob's your uncle.

Note SQLserver does not have an inbuilt tree walk like oracles connect by hence you may have to write a function in thoery which does the oracle connect by

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

Certified OT Developer and probably certfiable,Livelink ECM Champion 2008

 

[appnair]

I posted too soon so here's some more info .Livelink newer versions has a table called dtreeancestors which is shown to be faster than connect by or sql temporary table.However the dtreeanacestors is quite new and will require you to be on the latest and greatest version and the most latest patch.KB has dicsuiions regarding dtreeancestors lately.I belive querying that also will give you what you are looking for.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

Certified OT Developer and probably certfiable,Livelink ECM Champion 2008

 

[ggriffit]

As you are only looking for one level down from the current container something like the following should work :

select dtree.name,dtree.dataid,dtree.createdate,dtree.createdby,kuaf.name // select these columns
from dtree,kuaf // from these tables
where dtree.subtype=0 // where the subtype is 0
and dtree.parentid=1234 // and the parent is node 1234
and dtree.createdby=kuaf.id // and link the created user to the users table

If you are doing a LiveReport you can replace the 1234 with a UserInput value such as %1. The subtype values can be found here

http://www.greggriffiths.org/livelink/reference/subtypes/.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[ggriffit]

You can also get a copy of the Livelink Schema if you contact your Local OpenText support desk and they will provide a Non Disclosure Agreement - NDA - that you will need to sign, this will provide a significant aide to you in similar endeavours.

Also I would recommend the "209 Livelink ECM - Enterprise Server Advanced Schema and LiveReports" course which will take you through the Livelink database, more info can be found here -

http://www.opentext.com/training/coursedetails.html?id=1529.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[brettsanders]

Thanks everyone for your input. I ended coming up with the following sql:

SELECT OwnerID, ParentID, DataID, Name, UserID, CreatedBy=(SELECT Name FROM KUAF WHERE (ID = DTree.CreatedBy)), CreateDate, ModifyDate FROM DTree WHERE DataID in (select DataID from DTreeAncestors where AncestorID = %2 or AncestorID = %2) AND (CreateDate > %1) and SubType=0

However, i want to improve the output to show the entire folder path and am running into syntax errors when trying to create the following user defined function:

CREATE OR REPLACE FUNCTION Get_Folder_Tree (
IN_dataid IN DTREE.Dataid%TYPE)
RETURN VARCHAR2 IS
/*************************************************************************************
Module Name: Get_Folder_Tree
Purpose: Returns the complete path for a given item based on the item's dataid
Passed In: DTREE.Dataid
Returns: VARCHAR2
Assumptions:
**************************************************************************************
CHANGE LOG
Date Developer SR# Description
--------------------------------------------------------------------------------
DD MON 2005 Name ####
**************************************************************************************/

v_Name DTREE.Name%TYPE;
n_Parentid DTREE.Parentid%TYPE;
n_Dataid DTREE.Dataid%TYPE := IN_dataid;

CURSOR get_folder_cur IS
SELECT name||'\',
parentid
FROM lluser.DTree
WHERE dataid = n_Dataid;

v_Path VARCHAR2(1000) := NULL;
BEGIN
WHILE n_Dataid != -1 LOOP -- the ROOT folder
OPEN get_folder_cur;
FETCH get_folder_cur INTO v_Name, n_Dataid;
CLOSE get_folder_cur;
v_Path := v_Name||v_Path;
END LOOP;

RETURN '\'||v_Path;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END Get_Folder_Tree;
/