ID numbers, reference numbers, ticket numbers…this is something that regularly gets asked to be a part of any SharePoint solution or request based system. My first thought when this is required is “easy, we can just use the SharePoint item ID column and use that”. However, creating a simple calculated column that leverages the in-built ID column is not as easy as it seems.
My first attempt at creating a custom ID column involved creating a new calculated column, and appending some text before the ID and then inserting the ID column into the formula, like this:
The problem with this approach is that when new items are added, the ID appears to “slip” resulting in the custom ID column having no ID number being pulled from the SharePoint ID.
Custom ID column – modern SharePoint
Before you begin you will naturally need to create either a list or library in SharePoint, and the relevant apps checked as part of your O365 license.
2. The setup
Create a new column, with the type Number – I called this ‘solIncrementNum‘
Create a new column, with the type Calculated – I called this ‘solReqNum‘, later renamed ‘Request Number’
In the formula field, add the following: ="SOL-00"&[SolIncrementNum]
For the Data Type, select Single line of text
NOTE: for the Request Number formula if you want to prefix your custom ID with something else just replace what’s between the ” “ in the formula field above.
3. Build the Flow
Flow action: when a new item is created
Create a new flow from the template “when a new item is created, complete a custom action”
Give your Flow a name, I called mine “Populate Solution Request Number”
In the “when a new item is added” step, make sure the site address and list name are the same as the list you built the custom ID column for earlier
Flow: update item
Press + New step, start typing “update item”, select the update item action from the selection
Select the site in question, then copy and paste the List Name from the previous action
Make sure this action has the following fields set:
NOTE: make sure that when you set these fields, that the values you use are coming from the “when a new item is created” action.
Now when new items are created within the list or library, the flow will fire and create a new request number.
(this post was written using a SharePoint 2010 environment)
If you are using lookup columns in your SharePoint environment, one thing you may want to do is set the column that appears first, or is the default value in the lookup list when a document is uploaded into a library.
Now this solution is not a no code solution, but it requires very little code and is really straight forward to read and understand…
Step one – prerequisites
My setup to achieve this contained the following:
A custom list with each lookup value as a seperate list item (name stored in Title column)
A document library with a lookup column looking at the title in the custom list
Step two – configure the edit form
Open the list which is using the lookup column, make a note of the display name of the column
NOTE This caught me out while configuring, I naturally took the field name of the column from the URL string as opposed to the column name displayed in the edit form, make sure you use the latter as the field name won’t work.
Navigate to the lookup list where the lookup column is pointed to
Open the item in this list that you wish to be the default value
Open an item in a new window, in the address bar look for ID= and make a note of the number value as this is used for later
Navigate to the libraries edit form (just add /Forms/EditForm.aspx after the name of your list or library)
Press Site Actions > Edit Page
Add a web part > add a content editor web part to the page
Click inside the content editor web part > Under Editing Tools > Format Text press the HTML, Edit HTML Source button
Step three – add the code
In the HTML Source window, paste in the following:
(this post was written using a SharePoint 2010 environment)
So here’s the scenario, we have a document library that contains agendas, minutes, reports and other files related to meetings. The document library has a custom column called ‘Meeting Date’, a date column that is mandatory for all files that are uploaded.
We are required to create a view that is grouped by meeting date, sorted chronologically. Let’s give it a go!
Step one – prerequisites
My setup to achieve this was as follows:
A document library
An additional date column ‘Meeting Date’
Step two – configure the library
Navigate to the document library
Create a new calculated column called ‘Month of Meeting’
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 🙂
(This post was written using a SharePoint 2010 environment)
So you’ve got a SharePoint site, it all looks good (well, as good as it can!) but you notice that the Publish tab isn’t available in the ribbon.
First things first you check the site settings to see if SharePoint Server Publishing is turned on.
If you get to this point and your still no further forward it’s likely that your site wasn’t set up as a publishing site, but if you follow the steps below and your pages will be able to be published in no time.
Open the site in question, then go to Site Actions > View All Site Content
Open the SitePages library
Under Library Tools > Library, select Library Settings
Under General Settings > Versioning Settings, turn on Create major and minor (draft) versions > press OK
Go back to the original page, you will now see the publishing tab has appeared!
Drag and drop to re-order the left-hand site navigation
You can now re-arrange the order of your site navigation elements with a nice, user friendly drag and drop gesture. This removes the multiple clicks involved in he ellipsis, move up or down options of old. This new gesture also works for dragging/dropping into a sub-navigation location.
Bulk check in/out
This one I’ve been waiting for awhile for! You will soon be able to check in/out multiple files at the same time form the modern ribbon in a document library.
File signals or status icons are being added into SharePoint to add visual clues regarding the status of a file, such as check-out status, sharing, DLP blocks or missing metadata. This applies to modern lists and libraries.
A revamp of an old favorite, column totals and subtotals are coming to modern lists and libraries. These totals and subtotals are displayed in the footer. This is a welcome change from the old total count in classic views!
Add columns between columns and drag and drop
You can now insert new columns in place between existing in and modern list or library view, plus move a column around by simply dragging the header to a new location in the view.
Smarter file hover cards
The file hover card gives you a sneak peek into your most important files. Now, as soon as your hover over a file you will be able to see important stats like number of views, who’s viewed and who’s modified. This functionality is now being extended to almost all file types.
This new experience will also appear for files listed in your Shared by Me view.
Microsoft Authenticator allows you to receive security notifications for important events on your personal Microsoft Account. When you receive a notification, you can quickly view your account activity to take action if needed.
Microsoft Authenticator can be used to add two-step verification for added security if needed. It supports fingerprint, face ID or PIN authentication.
I was recently updating a view in a SharePoint List, the view was set up to use metadata fields to sort and group the content…lovely stuff. What I was required to do was to implement a choice field with a numerical order within it (i.e. 1. First step, 2. Second step, 3. Third step).
With sort order in List/Library views, it works with either alphabetical or numerical options ascending or descending. What I found was with choice fields operating as the number order, once you hit 10 the numbering system went out the window!
What you end up with is something like this:
1) First choice
11) Eleventh choice
12) Twelfth choice
2) Second choice
3) Third choice
and so on…
By default, SharePoint interperates the choice field as alphabetical so the way I got around this was to just use:
This gets around any issues with numerical values over 10 or having to create lookup lists or anything else 🙂
Explorer view in classic SharePoint sites has been a widely used bypass for users actually interacting with SharePoint libraries for a number of years.
Now in some cases that’s for good reason, from being able to upload multiple files easily in older versions of SharePoint to a familiar navigation of nested folder structure.
With modern SharePoint libraries, the old school ribbon has gone the way of the dodo…and so it seemed had open with explorer.
But fear not! If you still use IE you can still use the trusty open with explorer
How to open with explorer
Go to the library that you wish to open with explorer
On the right-hand side, press the drop-down icon next to all documents
Press View in File Explorer
Some weird stuff will then happen, where a classic 2013 version of your SharePoint site/library will open in a new tab and for me I got a message at the bottom of the browser window to allow popups from Microsoft then got another, more serious popup like this (multiple times):
I pressed allow to all of these then voila! we have file explorer!
File explorer only works for Internet Explorer, I tested in IE11 and it categorically doesn’t work in Firefox, Chrome, Edge or Edge Dev (beta).