Dynamically filter documents inside a document library using their folders and sub-folders

Question

I am trying to find the best approach to follow, to implement this business need:-

1- We need to create a document library inside our sharepoint online site collections.

2- the ability to search and filter the documents based on their location (folder and sub-folders).

3- For example we will have the following folder structure:-

HR
  General Document
    External 
    Internal
  Policies
    External
    Internal
    Private

IT
  General Document
    Outlook 
    SharePoint
        Admin
        Development
    Office 365
  Policies

now i want to be able to filter the documents based on the folders. for example to get all the document which are "Internal" OR to get all the "IT" documents which are external, and so on...

in other words to have the document been tagged based on their folders. now i found this appraoch How to automatically tag files with metadata based on folders in SharePoint which mainly maps the folder/sub-folders to terms or choices within drop-down columns. but this appraoch has the following limitation in my case:-

• Require to manually map the folders/sub-folders. and users might create a folder and forget to map it.
• Will not apply to document already uploaded to the folders
• Most importantly, the build-in list view filtering will not allow us to filter for example all the "IT" documents which are external, unless we navigate to the IT folder first, because seems the list view filters will not filter documents inside the folders/sub-folders.So if we are inside the document library top level (root url for the document library), we can only filter documents directly added to the document library, and the filter will exclude documents added to the folders/sub-folders. and to fix this issue we need to modify the list view and chose to show all the documents without folders, which will cause the threshold issue in-case our document library exceed 5,000 items which can happen very soon in our case

so can anyone adivce on this please? we need to dynamically filter documents based on their folder? does sharepoint online provides any built-in features or if there are any 3rd party tools?

second point, also if we can find a way to do such a filtering from multiple document libraries?

Thanks

Answer 1

If there are a limited number of locations you can use search.

Who will be the user? A "power user" who could be shown how to write search queries? If not, A friendly UI will take some additional work.

Search:

(path:"https://yourDomain/HR/policies/external" OR path:"https://yourDomain/HR/general document/external") AND isdocument=1

Search can be used from Search web parts, custom web parts or custom code using REST queries.

From REST:

https://yourDomain/_api/search/query?querytext='(path:"https://yourDomain/HR/policies/external" OR path:"https://yourDomain/HR/general document/external") AND isdocument=1'

REST to search for a keyword in the path:

https://yourDomain/sites/yourSite/_api/web/lists/getbytitle('Documents')/items?$filter=substringof('external',FileDirRef)&$select=FileDirRef,FileRef,Title

second point, also if we can find a way to do such a filtering from multiple document libraries?

Search with Path: or other metadata can search across all sites.

Answer 2

That article outlines the right approach IMO, so let's deal with the objections :)

Require to manually map the folders/sub-folders. and users might create a folder and forget to map it.

Configure metadata the field as required. Will give you a nice "information required" audit view, and annoy anyone who has incorrectly configured their folder. To prevent frustration when users are just updating existing docs, you might want to delay this until you've fixed:

Will not apply to document already uploaded to the folders

There are migration/audit tools that will tag existing libraries. vendors that come to mind: AvePoint Metalogix Sharegate mover.io (full list: http://www.get-spsite.com/administration/#migration--backup-vendors). You may need one of these, or perhaps a migration to a new library using the free SharePoint Migration tool provided by Microsoft, will do the job? (worth testing?) A Powershell script can be used to add metadata without impacting other properties. (e.g. similar to this - https://collab365.community/move-documents-via-powershell-while-retaining-authorship-data/, but I would suggest reimplementing using this https://aka.ms/sppnp-powershell) This can also be used to remediate issues like new folders that haven't been configured correctly.

This might be a lot of work for you, but quick work for a local consultant who has a license for and experience with a migration tool. An experienced consultant could get this done in a few hours, and can offer other guidance and ideas that can save you a lot of time/effort. SP Consultants and devs have quite different skillsets :)

The build-in list view filtering will not allow us to filter for example all the "IT" documents which are external, unless we navigate to the IT folder first

Sorta sounds like you didn't configure the Custom View with 'show files flat' view option? This is Step 5 in the article, and it's critical. The 5000 item list view threshold is gone in SPO modern view. There are many ways around this that don't require folders.

--

Oh and as a general note, if you're powershelling or doing something simple, I'd recommend copying (some of?) this data set into a dev tenant or a test environment of some sort (e.g. a site collection is fine) which only you can access, and experiment with getting it right there. You should document your planned actions. Once that's done, replicate docs again into a test environment, test your action plan, then do it once correctly in production. For example, awareness of things like Document Sets can help :)

Answer 3

I had a similar situation that i needed to search for files in a specific folder but with dynamic level of folders (BUT the name of the folder is specified) and i have to admit it that the Microsoft doc for this Sharepoint API is a bit vague. But thanks to this guy i solved.

Request is done by jQuery Ajax and this is the final endpoint URL:

SITE_URL + /_api/Web/lists/GetByTitle('<TheFirstLevelFolder>')/Items?$select=FileLeafRef,FileRef,EncodedAbsUrl,Modified,Created,Author/Name,Editor/Title&$expand=Author,Editor&$filter=FSObjType eq 0 and substringof(%27/<FolderWhereIShouldSearch>/%27,FileRef) and substringof(%27pptx%27,FileLeafRef)

A brief explanation:
substringof(\"ValueToSearch\", PropertyName) -> is something like Contains, if the PropertyName value contains this ValueToSearch then returns true; In my situation i couldn't search using EncodedAbsUrl because it gives an error like in the blog post (in the link above)
And sure i had to specify the content type to search just for files:
&$filter=FSObjType eq 0

I hope this will be a solution for someone else in the future.