Which SQL table will show the file location (in actual Volume not in Laserfiche) of an electronic document? We already have the tocid, parentid and vol_id.
Question
Question
SQL Table for finding electronic file location
Answer
When you locate the entry in the toc table, you can use the vol_id and edoc_storeid values to locate the file in the volume. Query the vol table by the vol_id to get the path (either fixpath or rempath columns) and convert the edoc_storeid value to a zero padded eight character hex value to get the filename. The folder path in the volume is just the segmented pair values of the first three pairs of the hex name. Note that the first pair value will have an "e" in front.
Here is an SQL query that you can run to get this information if you have the given tocid (credit goes to Cliff as this is just a modified version of the one he posted here):
DECLARE @entryID INT = 98785 SELECT dbo.toc.name AS DocumentName, dbo.vol.fixpath + '\e' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.toc.edoc_storeid),2),1,2) + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.toc.edoc_storeid),2),3,2) + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.toc.edoc_storeid),2),5,2) + '\' + CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.toc.edoc_storeid),2) + '.' + dbo.toc.edoc_ext AS FullPathAndFilename FROM dbo.toc LEFT JOIN dbo.vol ON dbo.toc.vol_id = dbo.vol.vol_id WHERE dbo.toc.tocid = @entryID
With the result looking like
One additional thing to note is that for electronic documents in the volume, you may actually see the files without an extension and that's normal.