Question

Access 'Creation Date' of a document from DB

Laserfiche Administration Workflow Search and Retrieval

Updated July 28, 2016

asked on July 26, 2016

I would like to read the Creation Date value from the LF database.

The reason behind is:

- I have a WF and when it triggers, it has to search for documents with Creation Date less than the speicified inthe starting entry

- I udnerstand I can use Search Repository and provide an Advanced Search criteria to search for documents, but if documents are in huge volume, then a load will be put on the LF Server.

- To avoid the load as per point above, I want to search for the list of TocIds for all the documents on the basis of Creation date (r.g Creation Date < 01/01/2012) and then using FInd ENtry in WF to get each entry using the tocId retrieved.

- I have looked into [propdef] table but can't find a field named 'Creation Date'

Any suggestions guys?

0 0

Answer

SELECTED ANSWER

replied on July 26, 2016

The TOCID table should have what you need. There is a created column in that table and the tocid is the same as the entry id (I believe this is the case would need to test).

You would need to write a Custom Query something like SELECT tocid WHERE created < 01/01/2012. Not a real SQL person so the syntax may not be correct. After you write your query have it return all results and then do a For Each Row loop and do a find entry based on the tocid.

Again I could be wrong about the tocid being the entryid but I believe that is the case (using LF 10).

1 0

replied on July 26, 2016 • Show version history

Hi Chris,

That's what I had just found and was going to update my post here :)

SELECT tocid
FROM [LF_RepoDB].[dbo].[toc] WHERE created < '2013-09-11' AND etype != 0

Thanks for replying.

1 0

replied on July 26, 2016

Note that all timestamps in the database are in UTC

1 0

replied on July 26, 2016

Thanks for the headsup Robert.

0 0

Replies

replied on July 26, 2016

Can you describe the process after you get the documents?

I would not recommend going through SQL directly as it bypasses Laserfiche security. If you're planning on processing these documents, you'd still have to call into Laserfiche separately with Find Entry. So you're actually increasing the number of calls to SQL.

The propdef table refers to fields. Creation Date is not a field, so it wouldn't be in there.

"etype<>0" will return shortcuts and entries in the recycle bin as well.

0 0

replied on July 28, 2016 • Show version history

Miruna,

When I get results, in a for each row, I use the Find Entry to get the Document using DocID (tocid) and then move it into an Archive Repository.

From your email above, does it mean that I am increasing the burden on SQL? I had taken this long path; instead of doing advance search in Repository, because I thought that this kind of search would put huge burden on the LF Server and SQL Server, while if I can get the tocid's from DB, I can do a direct search for the Entry using DocID and this would be quicker and less stress on the repository?

Is my above understanding correct?

And that returing of recyle bin items is concering...is there a way of avoiding this?

Or do you suggest to search inthe repository directly using the 'Search Repository'?

0 0

replied on July 28, 2016

It really depends on what you're trying to accomplish. Is the document that triggers the workflow just an easy way to get the date? Or is this some ongoing process that will run possibly for each new document? I'm not really understanding the use case of running a search for all documents older than some date with no other criteria.

But, yes, I'd probably still recommend the search since creation date is not an expensive property to search on.

0 0

You are not allowed to follow up in this post.

Question

Question

Access 'Creation Date' of a document from DB

Answer

Replies

Sign in to reply to this post.