SQL query on LF Server tables for specific metadata

replied on August 12, 2022 • Show version history

Obligatory Warning/Disclaimer: Direct queries of the LF database are not generally recommended and are not officially supported. Doing so requires extreme caution to ensure the data and system performance are not impacted.

If you do go this route, retrieve the minimal amount of data, make your query as efficient as possible, and avoid an excessive number of joins wherever possible. Also be sure to test in a test environment, and even in production try only running your process after hours if you can.

There's also no guarantee something won't change down the road that stops this from working.

That being said, I do understand the scenario and sometimes SQL really is the best/only option.

First, you'll need to look at the [propdef] table to get the identifiers for your target fields.

SELECT *
  FROM [YourDatabase].[dbo].[propdef]
  ORDER BY [prop_name]

For example, in our test environment we have an "Employee Number" field with a [prop_id] of 166, and one of our date fields has a [prop_id] of 55

Next, we need to look at our [propval] table for the values associated with those fields.

For example,

SELECT * 
FROM [YourDatabase].[dbo].[propval]
WHERE [prop_id] = 166

This is where things get more complicated because the column you want will depend on the data type assigned to the field. Text is stored in [str_val], numbers in [num_val], date in [date_val].

If your employee id is stored as a number, you might have to do a cast because num is designed to hold all numeric types and would return unwanted decimal precision if you're expecting an integer.

For example,

SELECT [str_val],[tocid]
FROM [YourDatabase].[dbo].[propval]
WHERE [prop_id] = 166 --Employee Number

SELECT [date_val],[tocid]
FROM [YourDatabase].[dbo].[propval]
WHERE [prop_id] = 55  --Date

Now, to get the corresponding values we would join on the [tocid] which references the specific document associated with the field values.

SELECT e.[tocid] AS [EntryId],
	e.[str_val] AS [EmployeeNumber],
	d.[date_val] AS [Date]
FROM [YourDatabase].[dbo].[propval] e
INNER JOIN [YourDatabase].[dbo].[propval] d
ON (e.[tocid] = d.[tocid] AND d.[prop_id] = 55)
WHERE e.[prop_id] = 166

This isn't the only way to structure the query but it should be pretty efficient.

4 0

replied on August 12, 2022

Great stuff. Thanks for the break-down too, that was very useful.

0 0

replied on August 15, 2022

We do this enough that I created a view for it.

CREATE VIEW [dbo].[vw_Repositorylookup_byTemplate]
/*
  This view lists all folders that are associated with a template.  Each folder is
 shown associated with all field values in the template.  By selecting for specific
 field names and field values you can select specific folders in the repository.
*/
AS
    SELECT Templates.pset_id      AS TemplateID,
           Templates.pset_name    AS TemplateName,
           TemplateFields.prop_id AS FieldID,
           FieldDef.prop_name     AS FieldName, 
           FieldValues.str_val    AS FieldStrValue,
           FieldValues.num_val    AS FieldNumValue,
           FieldValues.date_val   AS FieldDateValue,
           Folders.[name]         AS FolderName,
           Folders.tocid          AS FolderID,
		   Folders.parentid       AS FolderParentID
      FROM GenericRepository.dbo.propval    AS FieldValues
      JOIN GenericRepository.dbo.propdef    AS FieldDef
        ON FieldValues.prop_id = FieldDef.prop_id
      JOIN GenericRepository.dbo.pset_props AS TemplateFields
        ON FieldValues.prop_id = TemplateFields.prop_id
      JOIN GenericRepository.dbo.toc        AS Folders
        ON FieldValues.tocid   = Folders.tocid
      JOIN GenericRepository.dbo.propset    AS Templates
        ON TemplateFields.pset_id = Templates.pset_id
     WHERE Folders.etype = 0     -- doc type = folder
       AND Folders.parentid <> 2 -- Not in the recycle bin
GO

2 0

Discussion

Discussion

SQL query on LF Server tables for specific metadata

Comments 3

Follow-Ups 0

Sign in to reply to this post.