Search for documents over 255 full path length

SELECTED ANSWER

replied on May 19, 2022 • Show version history

It would be slow, but you can search for all documents in the repository and then download report with the path as a column. Then once you have it in Excel, add a new column for PathLength and put a formula in it to get the length of the path =len(Path). If you wanted, you could add another column with a formula =if(PathLength > 256,"True","False") . Now you can filter to display only those that are grater than 256.

1 0

View 1 previous reply

replied on May 19, 2022

This might work for a smaller repository, but this repository is well beyond the 9008 limitiation. If we search the entire repository we get a 9008 error, this applies to both cloud and on-prem and it means that you returned too many results.

If I can narrow the results down to only the documents over 255 that would allow me to stay below the 9008 search result limitations.

0 0

replied on May 20, 2022

Then break it down into smaller parts by searching a departmental folder or sub folder to get the search to work and just run the search multiple times doing different folders until you get the whole repository.

1 0

replied on May 25, 2022

Worked on this again today, the biggest drawback is another limitation I found in generating a report, it is even more picky than the 9008 restraint. It just means you really have to dig deep before you can export and sometimes it is out of control in how many documents are in one folder. This is a solution though and I can't yet think of another way to do it. I am passing on to the customer, hope it does not overwhelm them. Thanks for the help!

0 0

replied on May 25, 2022

What is the end goal here? Are you writing an export utility? If that's the case, is the list relevant? Wouldn't you have to handle it at export time anyway?

0 0

replied on May 19, 2022

I don't know about search syntax, but I do know how to do this from the database.

I know you tagged this as Laserfiche Cloud, and I don't know if you have database access in LFCloud or not, so my apologies if you don't have database access and this isn't actually helpful.

But if you do have database access, this is a query you can run on your repository database (just replace [LFRepository] with the name of your repository database - and tweak the TOP (1000) value at the top if you want to return more than 1000 rows of data) and it'll return items whose full path (including the entry's name) is more than 255 characters.

SELECT TOP (1000)
  subquery.[entry_id],
  subquery.[entry_name],
  subquery.[entry_path],
  LEN(subquery.[entry_path]) AS [path_length]
FROM (
  SELECT
    a.[tocid] AS [entry_id],
    a.[name] AS [entry_name],
    CONCAT(
      CASE WHEN z.[parentid] IS NULL THEN '' ELSE CONCAT('\', z.[name]) END,  
      CASE WHEN y.[parentid] IS NULL THEN '' ELSE CONCAT('\', y.[name]) END,
      CASE WHEN x.[parentid] IS NULL THEN '' ELSE CONCAT('\', x.[name]) END,
      CASE WHEN w.[parentid] IS NULL THEN '' ELSE CONCAT('\', w.[name]) END,
      CASE WHEN v.[parentid] IS NULL THEN '' ELSE CONCAT('\', v.[name]) END,
      CASE WHEN u.[parentid] IS NULL THEN '' ELSE CONCAT('\', u.[name]) END,
      CASE WHEN t.[parentid] IS NULL THEN '' ELSE CONCAT('\', t.[name]) END,
      CASE WHEN s.[parentid] IS NULL THEN '' ELSE CONCAT('\', s.[name]) END,
      CASE WHEN r.[parentid] IS NULL THEN '' ELSE CONCAT('\', r.[name]) END,
      CASE WHEN q.[parentid] IS NULL THEN '' ELSE CONCAT('\', q.[name]) END,
      CASE WHEN p.[parentid] IS NULL THEN '' ELSE CONCAT('\', p.[name]) END,
      CASE WHEN o.[parentid] IS NULL THEN '' ELSE CONCAT('\', o.[name]) END,
      CASE WHEN n.[parentid] IS NULL THEN '' ELSE CONCAT('\', n.[name]) END,
      CASE WHEN m.[parentid] IS NULL THEN '' ELSE CONCAT('\', m.[name]) END,
      CASE WHEN l.[parentid] IS NULL THEN '' ELSE CONCAT('\', l.[name]) END,
      CASE WHEN k.[parentid] IS NULL THEN '' ELSE CONCAT('\', k.[name]) END,
      CASE WHEN j.[parentid] IS NULL THEN '' ELSE CONCAT('\', j.[name]) END,
      CASE WHEN i.[parentid] IS NULL THEN '' ELSE CONCAT('\', i.[name]) END,
      CASE WHEN h.[parentid] IS NULL THEN '' ELSE CONCAT('\', h.[name]) END,
      CASE WHEN g.[parentid] IS NULL THEN '' ELSE CONCAT('\', g.[name]) END,
      CASE WHEN f.[parentid] IS NULL THEN '' ELSE CONCAT('\', f.[name]) END,
      CASE WHEN e.[parentid] IS NULL THEN '' ELSE CONCAT('\', e.[name]) END,
      CASE WHEN d.[parentid] IS NULL THEN '' ELSE CONCAT('\', d.[name]) END,
      CASE WHEN c.[parentid] IS NULL THEN '' ELSE CONCAT('\', c.[name]) END,
      CASE WHEN b.[parentid] IS NULL THEN '' ELSE CONCAT('\', b.[name]) END,
      CASE WHEN a.[parentid] IS NULL THEN '' ELSE CONCAT('\', a.[name]) END
    ) AS [entry_path]
  FROM [LFRepository].[dbo].[toc] AS a
  LEFT JOIN [LFRepository].[dbo].[toc] AS b ON b.[tocid] = a.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS c ON c.[tocid] = b.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS d ON d.[tocid] = c.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS e ON e.[tocid] = d.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS f ON f.[tocid] = e.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS g ON g.[tocid] = f.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS h ON h.[tocid] = g.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS i ON i.[tocid] = h.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS j ON j.[tocid] = i.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS k ON k.[tocid] = j.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS l ON l.[tocid] = k.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS m ON m.[tocid] = l.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS n ON n.[tocid] = m.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS o ON o.[tocid] = n.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS p ON p.[tocid] = o.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS q ON q.[tocid] = p.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS r ON r.[tocid] = q.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS s ON s.[tocid] = r.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS t ON t.[tocid] = s.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS u ON u.[tocid] = t.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS v ON v.[tocid] = u.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS w ON w.[tocid] = v.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS x ON x.[tocid] = w.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS y ON y.[tocid] = x.[parentid]
  LEFT JOIN [LFRepository].[dbo].[toc] AS z ON z.[tocid] = y.[parentid]
) AS subquery
WHERE LEN(subquery.[entry_path]) > 255

Also note that depending on the size of your repository (number of entries), this could be an extremely slow query - I'm sure there are ways to make it more efficient (like not going 26 levels deep if you don't believe any items are nested that far in the folder tree).

0 0

replied on May 19, 2022

I don't think we have any access to the database in Cloud. Thanks for the script though

0 0

replied on May 19, 2022

Bummer.

0 0

Question

Question

Search for documents over 255 full path length

Answer

Replies

Sign in to reply to this post.