How to stop all Office files in SharePoint 2010 opening read-only

(This post was written using a SharePoint 2010 environment)

In this post we look at how differing versions of Microsoft Office ProPlus affects SharePoint 2010 and the ability to open and edit Office files, plus how to fix them.

The situation

I began to get reports from users that were trying to open Office files from SharePoint that the files would only open read-only. These users have historically always had permissions to edit the files and have previously been able to open the files in edit mode from libraries within the SharePoint environment.

In this scenario we were running Microsoft Office 365 ProPlus version 16 and updates were on the monthly channel.

Investigation

There were several suggestions already within the Microsoft community on how to fix this issue, which I initially tested myself to see if they would work. I tried:

  • Clearing the Windows credentials of the MicrosoftOffice16 credentials
  • Turning off the “require documents to be checked out before they are edited” option in the a libraries settings

However, neither of these things worked completely. I didn’t notice any difference clearing credentials, and with the checking out files feature – this wasn’t turned on for the majority of the libraries I tested. Plus I didn’t really like the idea of having to turn off the checking out functionality in every library within our SharePoint environment!

So I continued to investigate and found the following:

The read-only issue appeared differently depending on the Office file

For Word and PowerPoint files, I saw that files would show this window when opening:

Word & PowerPoint files would open read-only after clicking open from this prompt.

Then the files would be read-only, if you tried to save the file it would ask you to save a copy. If you tried to overwrite the file saved in SharePoint it would tell you it is read-only and cannot be saved.

For Excel files, again you get the above prompt before the file opened, however when you opened the file a yellow banner would appear, allowing you to edit the workbook:

Excel files performed differently to other Office files when opening form SharePoint.

If you press “Edit Workbook” you could edit the file and save back to SharePoint no problem.

Some users could edit files from Open with explorer, but not all

So in this example some users could work around the issue by opening the file in explorer, which would allow them to edit the file and save back to SharePoint.

This wasn’t consistent for everyone, in some cases when users opened a file from explorer view, they would be prompted to check out the file from a yellow message in the ribbon, which would then error to tell them they can’t as the file is read-only.

Older versions of Office updates did not experience this issue

Not all of the users in this scenario had the same Office updates. One user who was running version 1611 on their machine was able to open and edit the same files from SharePoint that users running 2002 and above Office updates could not.

Solution

Im calling this a solution, but really it feels more like a sticking plaster as I’ve personally noticed this issue has been presenting itself in varying ways since at least the 1908 Office update, but this latest issue presented itself with the 2002 and 2003 Office updates. Anyway, here’s what we did to get files opening again:

  • Uninstall any Access Database Engine downloads you may have installed (2007, 2010 is what I noted)
  • Run an Office repair on the machine(s) with the issue
  • Install, or re-install Infopath 2013

Once Infopath was installed, when opening the same Office files (Word, PowerPoint or Excel) the above prompt changed to look like this:

Once Infopath 2013 was installed, the above prompt would appear when opening files from SharePoint.

I continued to test, going so far as to switch on the “require documents to be checked out before they are edited” setting just to see if that had any bearing on things, which it didn’t as the prompt just changed slightly:

With the check out option configured, the prompt to open and edit files in SharePoint slightly changes.

Like I mentioned earlier, this is by no means a solution to this problem, but it’s got us out of a jam for the past few Office updates, so hopefully it will help someone else too!

Resources

How to create a custom ID for lists and libraries

In this example I demonstrate how to create a simple solution that allows users to submit a request to a list, then a workflow fires that updates the request ID field with our custom ID number.

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:

="REQ-00"&[ID]

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

1. Pre-requisites

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
Setting the formula for the solReqNum column in list settings

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
Step 1 of the Populate Solution Request Flow

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:
    • Id: ID
    • Title: Title
    • solIncrementNum: ID

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.

Step 2 of the Populate Solution Request Flow

Now when new items are created within the list or library, the flow will fire and create a new request number.

Modern SharePoint list with Flow that populates Request Number

How to set the default value of a lookup field

(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:
<script src="https://code.jquery.com/jquery-1.11.0.min.js"> </script>

<script type="text/javascript">

$(document).ready(function() {

$("select[Title='lookup']").val('1');

});

</script>
  • You will need to change the [Title=’lookup’] to be the name of the lookup column as described earlier
  • You will also need to edit the number after .val to be the ID number of the lookup list item we made a note of earlier
  • Under the Page Tab > Press Stop Editing, make sure to save your changes

Test your changes!

Now when you upload a new document, your lookup column will default to the value we have specified!



How to group by date chronologically for library views

(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’
  • In the Formula section, add the following:
=IF([Meeting Date]="","Empty",("0"&MONTH([Meeting Date])&"/"&RIGHT(YEAR([Meeting Date]),4)))

NOTE
This formula basically states that if Meeting Date is blank, show Empty, if the meeting date has a value format it as MONTH (0 in front of the month number) / YEAR (yyyy).

  • Make sure the data type returned is ‘single line of text’
  • Press OK

Test your changes!

Now we have created a calculated column, we can test our changes by adding the column to a view and checking the Month of Meeting column has values.

Step two – create the view

Now we have verified our formula is working, we can start building out our view based on the requirements.

  • Navigate to the document library
  • Under Library Tools > Library > Create View
  • Scroll down to Group By, then group by Month of Meeting in ascending order
  • Press OK

NOTE
In the Group By section, you can set whether the groups are open or closed by default by choosing either collapsed (closed), or expanded (open).

You can also change the default number of groups to display from 30, but beware of slower page loading if you set it too high.

Now our view is configured we can take a look at it in all its chronological glory!

If you want to find out more about using group views, take a look at another post I wrote on ways around the 10 item limit in choice columns.

How to fix SharePoint sites that don’t appear in top navigation

(this post applies to SharePoint 2010, 2013, 2016 classic sites)

So in this scenario, a user was unable to get new SharePoint 2010 sites to show in the top navigation, even though some where hidden. If this happens to you try the following:

  • Navigate to Site Actions > Site Settings
  • Under Look and Feel > Navigation
  • In Global Navigation > update the maximum number of dynamic items
Update the maximum number of dynamic items to show all your sites

That’s it! now all your sites that haven’t been hidden will be displayed in the global navigation.

Tales of a SharePoint migration – part one

The path to a trial migration

I’ve been suitably inspired by Andrew Warland’s fantastic two-part series documenting his approach and migration to SharePoint Online, so much so that I thought it would be a fun series to write about my own experiences.

Take a look at Andrew’s blog series here.

It is’nt my intention to necessarily document Microsoft best practice in this series, rather just to explore some of the challenges, sucesses and experiences I notice along the way.

The current situation

My organisation has recently made the decision to move to to the cloud, with O365 being the naturally preferred destination. SharePoint has been well embedded, and heavily used within the business for several years, with on-premises SharePoint 2010 currently in production.

Finally, in terms of the SharePoint architecture and data volume, there are only three web applications to merge together as part of the migration effort. However, there are several site collections within our main intranet web app, plus many sub-sites nested within them, meaning the huge database sizes behind these site collections could prove difficult come migration time.

A note on the new, flat structure

Our current environment has a well established top-down structure in place that is generally consitent across the environment.

Having already made the investment in ShareGate, this will be the tool of choice for the migration. In the version 11.0 release of ShareGate, a new restructure option now allows you to promote sub-sites to top-level sites post inital migration from the source SharePoint environment.

The new restructure option in ShareGate 11.0

Considerations for a successful migration plan

One of the biggest issues to be resolved before we can start any sort of migration activity, is the fact that we have several content databases well over the 200GB recommended general use size limit.

Microsoft best practice suggests that any environment that has site collections, sites, content databases, libraries or lists that exceed the software boundaries and limits should be remediated prior to any migration activity. In this case, the main idea is to split each content database that exceeds 200GB into seperate content db’s, and where neccessary, move or promote sub-sites to site collections and attach new db’s.

Armed with the knowledge of the recent restrcuture functionality coming to ShareGate, plus my own personal feeling that any remediation activities to our current environment may in of itself carry adverse risk to the estate we proposed a different approach.

Trial migration begins

With all the reporting capabilities at our disposal via ShareGate, I was able to get a firm grasp of what resides within each site collection in our environment, in terms of:

  • The size of each sub-site underneath the top-level
  • Number/ size of libraries and lists
  • Number of items in each of the above
  • Any workflows running in any of the above

From this I ran a trial migration of a sub-site from SharePoint 2010 to a newly created team site in SharePoint Online.

Pre-migration

Before I kicked off the migration, I ran the source analysis tool within the Migration > Plan section of ShareGate. I noted the following obersavations:

  • The source analysis within “migration” in the ShareGate tool, although listed as only being able to analyze up to SharePoint 2013, does in-fact work for 2010
ShareGate source analysis
  • The source analysis cannot run at the sub-site level, meaning that you need to run it at the site collection level then just filter down to the sub-site in question through the report itself
  • Source analysis gives you a report of all checked-out files within a source site.From this, I created a simple view within each of the libraries that contained checked-out files to send to the site owners for action

Post-trial migration

The trial migration completed successfully as expected, however there were several interesting results I noted:

1. Everyone receieves a welcome email

If you migrate the permissions, once the source permission groups migrate each user will recieve a welcome email to the new SharePoint Online site.

There’s no GUI control for this as of this time of writing, but you can switch off the email notifications via PowerShell.

2. /Pages/ or /SitePages/…that is the question

Publishing sites seem to be the trickiest to migrate, especially those with custom master pages or page layouts. When migrating publishing sites, the Pages library is migrated wholesail, meaning the content won’t reside in the SitePages library (where new client-side pages are located).

3. Un-editable modern homepage

After the migration had completed, the new team site homepage threw up an error every time you tried to edit it.

I tried some of the documented resolution steps found here, but none of them worked for me. My solution was to just create a new page to replace the broken homepage, add all the relevant webparts and make this one the new default homepage.

Transforming classic publishing site pages to client-side pages

Publishing site pages will all be migrated as classic SharePoint pages, without the modern look and feel of a client-side page. My understanding is that for publishing pages with custom page layouts, additional metadata or custom content types will need to be transformed via PowerShell and creating a custom mapping file.

(I’m planning on writing a seperate blog post walking through an advanced publishing page transformation in the near future)

Its also worth considering that in the release notes for ShareGate 11.0 it makes mention of the fact they are researching the ability to transform classic to modern pages, so that could well simplify this process in a future release.

Conclusion

Overall, I was happy with our trial migration and believe it is a viable approach for us to move from on-prem to O365. Some lessons learned for myself would be to consider and SharePoint permissions audit prior to migration to remove any unecessary permissions, send an inventory out to site owners aswell as checked-out files, all in the name of reducing the migration effort.

This will be an ongoing series of posts, which i’ll focus more the on the nitty-gritty of the migration effort than anything else, but as always if there is any feedback or suggestions on how to improve this site, please let me know!

Configure a default view for Document Sets

(This post was written using a SharePoint 2010 environment)

Document sets are a pretty awesome way to convert people from traditional folders to using metadata in SharePoint, but retaining the ‘feel’ of a nice folder structure.

When you start using document sets you might want to play with the default view of your files, this isnt as straight forward as just setting a new default view. Here’s how to do it

Create and configure the view

  • Create your view
  • Under Folders > ensure show this view is set to In folders of content type: document set
  • Press OK
Select the document set content type in the view settings

Select the view for the Document Set Content Type

  • Navigate to the Library Settings
  • Under Content Types Select the Document Set
  • In Settings > Select Document Set settings
Document Set settings
  • Under Welcome Page View > select your view
  • Press OK

Now the changes made to your custom view will be present inside each document set!

Bonus

For more on document sets, check out my write up of the quirks of document set routing.

How to show the folder path of a file in library views

Introduction

Firstly, I want to make a mention to this great blog post by Tom Benjamin for giving me the direction as to how to achieve this. His post is way more complex than what I was looking to do, so if you want to find out more about how to show files from a folder using CAML queries take a look:

Link: https://www.tombenjamin.ca/blog/designer/view/using-caml-query-show-files-folder/

The scenario

A common request I get is:

How do I see what folders/ sub-folders my files are in at a glance

– all users everywhere

Out of the box, there aren’t any columns available that you could potentially leverage to display this information in a standard SharePoint 2010 library.

The solution

So, just by adding one value in SharePoint Designer, here’s how you do it:

  • Navigate to the library you wish to change, create a new view under Library Tools > Library > Create View
  • Choose the relevant format of your view, give your view a name and press OK
  • Open SharePoint Designer > Open the site > open the library you were just working in
  • In the Views pane > click to open the view you just created
In SharePoint Designer, clicking on the view name will open the view in edit mode
  • In the code editor window, scroll down until you see something like the following:
<ViewFields>
	<FieldRef Name="DocIcon"/>
	<FieldRef Name="LinkFilename"/>
	<FieldRef Name="Modified"/>
	<FieldRef Name="Editor"/>
</ViewFields>
  • 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
Add the field reference to the View Fields list
  • 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.

Library root displaying a files path
File in sub-folder displaying relative location

Bonus

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
  • Press OK
Filtering to only show word documents, also showing items without folders
  • 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:
<GroupBy Collapse="FALSE" GroupLimit="30">
	<FieldRef Name="FileDirRef"/>
</GroupBy>

Save and preview your view, it should now be grouping by the Path field:

I know this has proven really useful for my company, so hopefully this helps out someone else too 🙂


Problems creating list or library views based on created date

The situation

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!).

The setup

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.

The problem

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.

Conclusion

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 🙂

SG-report