What's new today (+location)

Hi allA standard LiveReport (What's new today?) gives list of files that are added/new today.What I would like to do is edit this report and add a field that shows the folder location of the files.Is this something that is easily added if so how?ThanksDavid

Find more posts tagged with

Comments

EDMS_-_functional_NAM

Hi David, This is easy to do, the Dtree table have columns; parentid and dataid, for each record, dataid is unique key of the record, the parentid = is also a record in the dtreee.g. try thisselect p.name folder, d.* from DTree d, dtree p where p.dataid=d.parentid and (d.CreateDate>%1) and %2 order by d.CreateDate desc

David_Turpie

Thanks for this.It worked well.I was wondering if it was also possible to include the whole location rather than just the folder name for example if a document was created in folder1/folder2/folder3 then the location would display as this and not just folder 3?Thanks again though.David.

volvostephen

Message from via eLinkIf you are running SQL Server, an interesting approach to this could be to -create a SQL function that would execute SQL to retrieve this information.The SQL Function could be called GetFullPath and would accept a DataID asthe input. Then the SQL in the LiveReport would be structured as...Select Name, GetFullPath(DataID) from Dtree where .....In our environment, I would like to store a nodepath as well as full path soI could use that as a filter for queries. For example, I would want toretrieve the most viewed documents (DAUDIT) in all folders below this one.If I had a view or table that contained the nodepath, I could use astatement like where nodepath like '%' or whereLeft(nodepath, Len() = So - back to the function.... Rather then write about it - here it is...Called like...select Name, livelink.GetFullPath(DataID) from dtree with (nolock) where<<<Disclaimer>>>> Use this sparingly - this will be a performance hit andshould not be used on long running queries...-----------------------------------------CREATE FUNCTION [livelink].[GetFullPath] (@DataID INT) RETURNS varChar(8000) AS BEGIN Declare @TmpParentID intDeclare @FullPath varChar(8000)Declare @Name varChar(255)Declare @TmpName varChar(255)if @dataID <> 0Begin --build up a list of parentID's for the given dataID while @DataID <> -1 Begin Select @TmpParentID = ParentID from dtree with (nolock)where dataID = @rowcount = 0 BREAK --this logic is for parentID's that are negative if @dataID < -1 and @TmpParentID = -1 begin --we got -1 as the parentID but the dataID isnegative so try again with the (+) of the dataID Set @DataID = ABS(@DataID) end else begin Set @DataID = @TmpParentID end Set @TmpName = @Name Select @Name=Name from dtree with (nolock) where dataID =@DataID if @FullPath is NULL Begin Set @FullPath = @Name end else begin if @TMPName <> @Name begin Set @FullPath = @Name + ':' + @FullPath end end endendRETURN @FullPathEND------------------------------------------------------------------------