If you have ever tried to migrate a large volume of data into SharePoint libraries or lists you will have likely encountered an issue with trying (and failing) to break permissions inheritance on lists/ libraries following the migration.
I was recently dealing with this limitation myself as part of a migration project. We had migrated terabytes of data from on-premise file servers into SharePoint Online libraries and as we were beginning to break permissions inheritance on the library saw this error:
Faced with the problem of needing to secure lots of data that, based on the error above didn’t seem possible – I decided to refresh my memory on just what the limits of lists/ libraries are in SharePoint Online:
A list can have up to 30 million items and a library can have up to 30 million files and folders. When a list, library, or folder contains more than 100,000 items, you can’t break permissions inheritance on the list, library, or folder.
With the above being true, the error mentions the list view threshold – which is the way SharePoint throttles and limits resources that govern the amount of data and throughput that can be managed. The list view threshold is set to approximately 5000 items by Microsoft and cannot be changed.
Now the issue with not being able to change the list view threshold, although unique to SharePoint Online isn’t a new thing – in fact it seems to have been a problem for lots of people for a long time. So if we are unable to change the list view threshold, what options do we have?
Well as it turns out the list view threshold error appears to be a bit of a red herring. I had taken a look at Microsoft guidance for managing large lists and libraries – creating indices within the library, but this had no affect as the total number of items in my library exceeded 100,000.
The answer for me came from an inspired post on the SharePoint Stack Exchange! I give full credit for the simple, yet brilliant way to get this to work to Kasper Bo Larsen, who suggested that you should just delete enough stuff to get under 100,000 > break permissions inheritance > then restore the deleted items.
This works a treat!
Now it’s not perfect, nor would I call this a fix but if you have already migrated your data then this certainly will work for you.
Things to note
#1 Remember to break inheritance before any migration tasks begin!
I know this is a super obvious one and hindsight is always 20/20, but I’ve learnt from this mistake and built it into any future migration runbooks to break permissions inheritance before starting any migration tasks. It will save you a lot of hassle I promise…
#2 Trying to break permissions inheritance via PowerShell will yield same results
I had the thought during my investigations of this that maybe trying to break permissions inheritance via PowerShell might supersede some of the restrictions deployed to us mere GUI administrators – this was not the case and the PowerShell route fails just the same.
#3 There are no service limits or boundaries for the SharePoint recycle bin
I tried to find out if there were any limits to how much data you can restore from the SharePoint recycle bin in one go – I couldn’t find anything. With that said, I was able to restore over 60,000 files/ folders in one sitting back to it’s original location after breaking permissions inheritance so I don’t believe that will be a blocker.
Add the following field reference in between the opening <ViewFields> and closing </ViewFields>
Add the field reference in the display order you would like it to appear in the view
Press the Save icon to save your changes
Press the Preview button to see your view in action in the browser
Now you will notice there is a new column being displayed “Path”, that is showing us the full location of the file or folder in the libary. You’ll also notice that this path will display data when at the library root, or in any folders or sub-folders in the library.
Taking this one step further, what if we wanted to show files of a certain type, then create a view that groups these files by their folder location? Guess what, that’s exactly what I did!
Navigate to your library > create a new view as before, this time base your new view off the one you just created
If you wish to only show files of a particular type, use the filter by settings (for example below is filtered to only shows Word documents)
Make sure “show all items without folders” is selected
Back in SharePoint Designer > Open up the view you just created
Scroll down until you see the opening <Query> tag and add the following beneath it:
With Microsoft retiring SharePoint 2010 designer workflows, plus the movement away from SharePoint Designer in general, a few readers have asked for a solution that works with modern SharePoint.
When researching this I considered whether suggesting to use SharePoint Designer 2013, as the above solution would still work in SharePoint Online using SPD 2013. But, as Microsoft say themselves although SPD 2013 remains supported, it’s depreciated – so I decided to go in a different direction.
The below example walks you through how you can create a flow in Power Automate to update a file after it’s been created to have the folder path shown in the document library view:
For this solution you will need to have access to create Flows in Power Automate, as well as an existing Document Library created in SharePoint Online:
Navigate to the document library you wish to show the folder path for
Add a single line of text column to the document library > give it a name (I called mine FolderPath)
Under the ellipsis, press Automate > Power Automate > Create a flow
In Power Automate, either use an existing, relevant template or start from blank
The trigger action should be When a file is created (properties only)
Set the Site Address and Library Name where you want to add the folder path
Insert a new step > select Update file properties.
Set the following values for the update file properties step:
Site Address: same as previous step
Library Name: same as previous step
FolderPath: Folder path
NOTE: The FolderPath within the Update file properties step is the custom column we created earlier. The Folder path (highlighted in red) is dynamic content available within the step in the flow. The folder path dynamic content is the path to the folder the item is in, relative to the site address.
Here’s the flow in it’s entirety:
At this point test and save your flow to make sure it is working as expected 🙂
Bonus #1 – turn your folder path column into a hyperlink column
So if like me you want to take this one step further, wouldn’t it be good if we could easily make our newly showing folder paths, actual hyperlinks to the folders? Well the good news is you can!
Navigate back to your document library > click on the FolderPath column > Format this column
Under Apply formatting to make sure FolderPath is selected
Paste the following JSON into the custom formatting box:
NOTE: for more information on turning field values into hyperlinks, check out this awesome sample from sp-dev-list-formatting.
Your FolderPath column values should now be legitimate hyperlinks that click through to the relevant folders
Bonus #2 – update existing files in the document library
This was another suggestion from a reader with regards to how to update files that existed in the document library before the flow was created.
Running a flow manually for individual files
When I began to consider how to do this I started by looking at ways to manually start the flow.
It appears the only real way to do this is to create a new column that adds a button next to each file, that allows you to run the flow. I’m not really enamoured by this approach as it doesn’t seem ideal to have an extra column to run a flow showing on every file in your library. If this is something you would like to pursue then I would recommend this great article by WonderLaura who has the process of creating a button to trigger a flow covered!
Update our flow to update all files if folder path is empty
My solution to this problem was to update the flow we created earlier to get the properties for all files in the library, then add a condition that checks if the FolderPath column is empty, then if yes runs our flow as before.
First, I added a Get files (properties only) action which gets all the files from the source library
Then I added a new Condition action, which simply checks if the FolderPath column we created is equal to null. You will also notice a new Apply to each action will be created
I then moved the previous Apply to each action into the “If yes” condition
Data retention and deletion…I’m sure this is a something that anyone involved in Office 365, SharePoint on information management in general gets fed up of saying since the recent GDPR legislation!
Recently we have been rationalising and cleaning up our data in preparation for moving to Office 365. We are starting with SharePoint as the first target repository or silo of content.
The general consensus is to delete files and folders over 7 years old unless there is a pre-existing data retention policy to adhere to. So the next task is to identify those files that fall within our threshold, and ultimately delete.
Luckily, we have Tree Size Pro and ShareGate so I was able to relatively easily identify the files in question (there were a lot!).
As our SharePoint environment is a) rather full; and b) rather old, I made the decision to incrementally delete files rather than en-masse to mitigate risk, targeting the lists/libraries containing the most out of date content. I started by creating a view in the first library – library A with the following parameters:
Standard library view
Filtered by Created Date if less than or equal to 01/01/2011
Folders or Flat: Show items inside folders Show this view: In all folders
(all other settings are left default)
Results this returned looked good, I could see folders and files in this view that matched the criteria – brilliant! Based on my previous statement I decided to delete in batches out of working hours, again to mitigate risk. I deleted first from library A, then from the first stage and finally from the second stage recycle bin all in this fashion.
I had permanently deleted around 50% of the total volume of content to be deleted from library A when we started to receive reports of current files being ‘missing’ from library A…not a good day.
After these reports were investigated they were indeed true. It turns out that when folders are included within a library view, folders that match the filter will be shown in the view, regardless of whether the files inside match.
We tested the view exluding folders and all the files returned matched the filter criteria. The same results were demonstrated from a SharGate report of the same nature. The report of all files over 7 years old brought back folders over 7 years old, but they also contained files that were newer.
At present, we are not entirely sure as to why these filters are not able to drill down past a top-level folder. It appears to be difficult to specify via view settings to only show files within folders, including the folder itself that matches the criteria.
We have decided to omitt folders from our reports and views going forward and to solely focus on files as this is the most reliable way we can delete files.
Bonus: for those of you with ShareGate, heres an example of my report we created to bring back all files over 7 years old, excluding folders. I ran this report across the entire intranet application over a weekend and it worked a treat 🙂
Have you ever been asked to hide a list or library from a SharePoint site? If so, you go straight for selecting ‘no’ to displaying the list or library on the Quick Launch or removing it from the navigation. However, your eagle eyed users notice the handy view all site contents option and see that it is still listed there – they want it gone!
Luckily, all you need is SharePoint Designer and it is as simple as a click of a button…
(These steps were created using SharePoint Server 2010)
Open the site that where list or library resides in SharePoint Designer
Under Lists and Libraries – Select the list or library you wish to hide
On the main list settings page – find the Settings section
Check the Hide from browser option
Thats it! when you option the view all site content page now, that list or library will no longer be showing. Also, if you want to re-instate it at a later date, just un-check the box and it will re-appear.
This also works for SharePoint 2013, 2016 and SharePoint Online, under the site contents page.
If you’ve ever been in the situation where you try to edit a page in SharePoint and you see this seemingly unchangeable message appear at the top of the page stating:
“Page has been declared a record or placed on hold and is read-only”
Fear not! I’ve seen this message appear and it usually occurs when a user (or service account) is operating as a system account. This could have been set manually by said user if they have access to the web application via central admin:
If this is the case, you can overwrite the system account check in via SharePoint Designer. You’ll obviously need the correct permissions to access SPD beforehand! In the following example I’m using a SharePoint 2010 environment. To do this:
Connect to your web application in SharePoint Designer
Navigate to All Files – Pages
Right-click on the page that is currently locked
NOTE: You won’t be able to view the individual pages within each Page Library if you navigate through Lists and Libraries. This space is used to view and manage the settings for each list type you have. All Files takes you to the ‘root’ of your web application, where you can see everything that sits under the web application.
I know what you’re thinking, what date format problem? Well this is something that’s cropped up for me time and time again so let me explain. Most requests I get for new SharePoint lists will usually contain a variety of custom date columns and said list will also require that the default display form shows a unique view of this data.
On the default display form, you will notice that the custom date columns will be showing data that looks like this:
Well this just won’t do, will it? The good news is we can fix it!
To be able to customise the display form, you will need to be able to connect to your SharePoint environment using SharePoint Designer, if you don’t have it you can download it from Microsoft here:
For this example, we are only focusing on one custom date column, at the end of the post I will summarise how to fix this issue for multiple custom date columns.
Open SharePoint Designer and connect to your environment
Navigate to the lists and libraries and open the list you want to change
Within the Forms section – New Form
(I’d recommend creating a new display form for no other reason than to avoid potentially breaking the active one).
Give the form a logical name so you can identify it at a glance as a display form (I usually go for DispForm2)
The code behind the display form will open, don’t worry too much about this we are only interested in a small portion of the code.
In the Ribbon – under the Home Tab – Press Advanced Mode
If it’s not already, at the bottom of the form – change the view to split view
Scroll through the design view of your form until you find the custom date field – select it
In the code view, make sure the following line is highlighted: <xsl:value-of select="@Start_x0020_Date"/> (NOTE: the bits after the @ will be the name of your date field)
If you amend this line of code to look like this: <xsl:value-of select="ddwrt:FormatDate(string(@Start_x0020_Date), 2057, 3)"/>
you should see something like this: 11 June 2014
Save the display form
Press Preview in browser to double check the format date function is working as expected
Close preview browser, close the display form
In the forms section of the list – select your form
In the Ribbon – Set as Default
That’s all there is too it! It might seem like quite a few steps but it isn’t too bad once you get comfortable with the forms code view.
If you have more than one custom date column that you need to format, just amend the code of each custom date column using the example above as a guide (just make sure if you copy/paste the code that each @ name is different.
FormatDate function and locales explained
What our updated code is doing is inserting a FormatDate function, which allows us to add the locale parameters to the end of our line code (the 2057, 3). The locale parameters control what the output of the FormatDate function will be, here are some examples of the outputs and locale parameters:
3/23/2009 12:00 AM
Monday, March 23 2009
Monday, March 23, 2009 12:00 AM
3/23/2009 12:00:00 AM
Monday, March 23, 2009 12:00:00 AM
3/23/2009 12:00 AM
23 March 2009
23 March 2009 00:00
23 March 2009 00:00:00
If you are looking for a list of all the available locales you can find them here:
This is my first ever blog post…scary! I’ll start with a really easy solution to a problem I was tasked with solving by one of our departments…
Ever wanted to completely remove any reference to the pesky NEW! icon from freshly uploaded documents or list items? Yeah me either…well if in case you ever felt so inclined to do so here are some very simple steps to remove the icons from any new documents or items on a page.
This example is a SharePoint 2010 web application with a standard publishing site collection active:
Navigate to the page you want to remove the NEW! icons from and begin editing
Add a content editor webpart to the page; I added it to the bottom of the page but you can add it whenever suites
Click on the down arrow to open the webpart menu – Select Edit Web Part
You’ll now notice the content editor webpart has changed, it will now say ‘Click here to add new content’, click here!
In the ribbon – Editing Tools menu – Format Text tab – Press HTML – Edit HTML Source
In the HTML editor, copy and paste this little bit of CSS:
Press OK on the HTML editor
Voila! The NEW! icons have vanished and we can move on with our lives…the next little bit is totally optional, but I think for completeness it makes sense to do. We’re just going to rename and hide the content editor webpart so that it’s not visible and any editors know not to touch it:
Click on the down arrow to open the webpart menu – Select Edit Web Part
In the content editor webpart menu – expand Appearance
Change the Title to ‘Do not delete’
Change the Chrome Type to None
Press Apply and OK
That is all there is to it, we have successfully removed the NEW! icons so they are no longer visible and also hidden the webpart which contains the tiny snippet of CSS that makes the change.