SharePoint Validation Formula Tip – Don't Use IF!

I often see questions about SharePoint validation formulas in the online forums that include IF statements to return True or False. Something like this:

    =IF(  someCondition , True, False  )
    =IF( Amount > 100, True, False )
    =IF( AND( State = "OH", Amount>100 ), True, False )

The IF is simply not needed!

Simply replace this:
    =IF( Amount > 100, True, False )
With this:
    =Amount > 100

It's either greater than, or it is not. The greater than test returns a True or False all by itself. For that matter, the expression in the first parameter of an IF statement must return True or False!

Here's what it looks like in both Classic and Modern UI.

What if you need to reverse the True and the False result?

Use the NOT function to reverse the True/False value.
    =NOT( Amount > 100 )

ANDs and ORs Return True or False

Simply replace this:
    =IF( AND( State = "OH", Amount>100 ), True, False )
With this:
    =AND( State = "OH", Amount>100 )

Two more examples…

If the state must be within the tri-state area you could write:
    =IF( State="OH", True, IF( State="KY", True", IF( State="IN",True, False ) ) )
Or you could just write:
    =OR( State="OH", State="KY", State="IN" )

While there are examples that require IF, you can solve most validations with a simple comparison, or the with the use of AND, OR and NOT.

    =IF( AND( Amount>100, IF( State="OH", True, IF( State="KY", True", IF( State="IN",True, False ) ) ) ), True, False)

Yup… I have seen those. And, it can be replaced with:
    =AND( Amount>100, OR( State="OH", State="KY", State="IN" ) )


And… book plug here… I have a lot more on validation formulas in my book!

Numbers are Being Added to My SharePoint List Internal Names

When you create a list or library, the name you enter becomes both the internal name (used in the url), and the display name. When you rename a list, only the display name is changed. If you later create a new list with the same name as a renamed list’s original name, the new list’s internal name will have a number added.

Here are the steps to show what's happening:

1. Create a new Custom list and name it "TestList".
2. Navigate to the list and note that the URL contains "TestList".
3. Go to the list and List Settings and use "List name, description and navigation" to rename it to something like "TestListNorth".
4. Note the URL. It's still "TestList".
5. Create a new Custom list and name it "TestList".
6. Navigate to the list and note that the URL contains "TestList1". This is also the internal name. The display name is "TestList".
7. Change the display name of this list to "TestListEast" and note that the URL is still "TestList1".
8. Create yet another new Custom list and name it "TestList".
9. Navigate to the list and note that the URL contains "TestList2". This is also the internal name. The display name is "TestList".
10. Change the display name of this list to "TestListWest" and note that the URL is still "TestList2".

The internal name is both unique and not changeable from the browser user interface. The display name is also unique amongst the display names, but can be different than the internal name.

Keep in mind that the deletion of large objects in SharePoint is a gradual and background process. You might get numbers added to the internal name when you delete a large list, or even a Site or Site Collection, and then recreate those objects and lists using the same names.

Run SharePoint 2013 and 2016 Search Reports from PowerShell

Updated to include IDs for SharePoint 2016!   Original article here.

Update! Need these reports for every site collection in the farm? See Part 2: http://techtrainingnotes.blogspot.com/2015/04/run-sharepoint-2013-search-reports-from_21.html

In my Search Administration class I stress that admins should dump the search reports on a regular basis as the data is only kept in detail for 14 days and in summary form for 35 months. But who wants to both run these reports at least once every 14 days, even they can remember to do so. So, PowerShell to the rescue… Schedule this script to run each weekend and your work is done.

The following script works for on premise SharePoint 2013. To work with Office 365 you will have to figure out how to include your credentials. The example included here works on premises by using "UseDefaultCredentials = $true".

After lots of hacking, detective work (see below) and just plain trial and error, here's the script:

# This is the URL from YOUR Central Admin Search Service Usage Reports page:
#
# The script will not work unless this is correct!
#
$url = "http://yourCentralAdminURL/_layouts/15/reporting.aspx?Category=AnalyticsSearch&appid=ed39c68b%2D7276%2D46f7%2Db94a%2D4ae7125cf567"  

# This is the path to write the reports to (must exist, but can be anywhere):
$path = "c:\SearchReports\"




function Get-SPSearchReports ($farmurl, $searchreport, $path, $version)
{
  # TechTrainingNotes.blogspot.com

  if ($version -eq "2013")
  {
    # Report names and IDs
    $Number_of_Queries          = "21be5dff-c853-4259-ab01-ee8b2f6590c7"
    $Top_Queries_by_Day         = "56928342-6e3b-4382-a14d-3f5f4f8b6979"
    $Top_Queries_by_Month       = "a0a26a8c-bf99-48f4-a679-c283de58a0c4"
    $Abandoned_Queries_by_Day   = "e628cb24-27f3-4331-a683-669b5d9b37f0"
    $Abandoned_Queries_by_Month = "fbc9e2c1-49c9-44e7-8b6d-80d21c23f612"
    $No_Result_Queries_by_Day   = "5e97860f-0595-4a07-b6c2-222e784dc3a8"
    $No_Result_Queries_by_Month = "318556b1-cabc-4fad-bbd5-c1bf8ed97ab1"
    $Query_Rule_Usage_by_Day    = "22a16ae2-ded9-499d-934a-d2ddc00d406a"
    $Query_Rule_Usage_by_Month  = "f1d70093-6fa0-4701-909d-c0ed502e3df8"
  }
  else # 2016
  {
    $Number_of_Queries          = "df46e7fb-8ab0-4ce8-8851-6868a7d986ab"
    $Top_Queries_by_Day         = "06dbb459-b6ef-46d1-9bfc-deae4b2bda2d"
    $Top_Queries_by_Month       = "8cf96ee8-c905-4301-bdc4-8fdcb557a3d3"
    $Abandoned_Queries_by_Day   = "5dd1c2fb-6048-440c-a60f-53b292e26cac"
    $Abandoned_Queries_by_Month = "73bd0b5a-08d9-4cd8-ad5b-eb49754a8949"
    $No_Result_Queries_by_Day   = "6bfd13f3-048f-474f-a155-d799848be4f1"
    $No_Result_Queries_by_Month = "6ae835fa-3c64-40a7-9e90-4f24453f2dfe"
    $Query_Rule_Usage_by_Day    = "8b28f21c-4bdb-44b3-adbe-01fdbe96e901"
    $Query_Rule_Usage_by_Month  = "95ac3aea-0564-4a7e-a0fc-f8fdfab333f6"
  }

  $filename = $path + (Get-Variable $searchreport).Name + " " + (Get-Date -Format "yyyy-mm-dd")  + ".xlsx"
  $reportid = (Get-Variable $searchreport).Value

  $TTNcontent = "&__EVENTTARGET=__Page&__EVENTARGUMENT=ReportId%3D" + $reportid

  # setup the WebRequest
  $webRequest = [System.Net.WebRequest]::Create($farmurl)
  $webRequest.UseDefaultCredentials = $true
  $webRequest.Accept = "image/jpeg, application/x-ms-application, image/gif, application/xaml+xml, image/pjpeg, application/x-ms-xbap, */*"
  $webRequest.ContentType = "application/x-www-form-urlencoded"
  $webRequest.Method = "POST"

  $encodedContent = [System.Text.Encoding]::UTF8.GetBytes($TTNcontent)
    $webRequest.ContentLength = $encodedContent.length
    $requestStream = $webRequest.GetRequestStream()
    $requestStream.Write($encodedContent, 0, $encodedContent.length)
    $requestStream.Close()

  # get the data
  [System.Net.WebResponse] $resp = $webRequest.GetResponse();
    $rs = $resp.GetResponseStream();
    #[System.IO.StreamReader] $sr = New-Object System.IO.StreamReader -argumentList $rs;
    #[byte[]]$results = $sr.ReadToEnd();
    [System.IO.BinaryReader] $sr = New-Object System.IO.BinaryReader -argumentList $rs;
    [byte[]]$results = $sr.ReadBytes(10000000);

  # write the file
  Set-Content $filename $results -enc byte
}


# Note: Change the version to 2013 or 2016

Get-SPSearchReports $url "Number_of_Queries" $path "2013"
Get-SPSearchReports $url "Top_Queries_by_Day" $path "2013"
Get-SPSearchReports $url "Top_Queries_by_Month" $path "2013"
Get-SPSearchReports $url "Abandoned_Queries_by_Day" $path "2013"
Get-SPSearchReports $url "Abandoned_Queries_by_Month" $path "2013"
Get-SPSearchReports $url "No_Result_Queries_by_Day" $path "2013"
Get-SPSearchReports $url "No_Result_Queries_by_Month" $path "2013"
Get-SPSearchReports $url "Query_Rule_Usage_by_Day" $path "2013"
Get-SPSearchReports $url "Query_Rule_Usage_by_Month" $path "2013"
 

The Detective Work…

I could not find anything documented on how the reports are called or details on things like the report GUIDs. So here's how I got there:

• Go the search reports page in Central Admin and press F12 to open the Internet Explorer F12 Developer Tools then:
  • Click the Network tab and click the play button to start recording.
  • Click one of the report links.
  • Double-click the link generated for the report in the F12 pane to open up the details.
  • Make note of the URL (It's the same as the report page!)
  • Note the Accept, and Content-Type Request Headers.
  • Click the Request Body tab.
  • Stare at 3000 characters in that string until your head really hurts, or until you recognize most of what is there is the normal page postback stuff like VIEWSTATE. So we need to find what's unique in the string. (It's the Report IDs.)
  • Click on each of the nine reports and copy out the report IDs.
  • With a lot of trial and error figure out what the minimum string needed is to generate the reports. (It's ""&__EVENTTARGET=__Page&__EVENTARGUMENT=ReportId" plus the report id.)
  • Find out how to do an HTTP POST using PowerShell. (Steal most of it from here: http://www.codeproject.com/Articles/846061/PowerShell-Http-Get-Post.)
  • Find some other needed .Net code and convert the C# to PowerShell.
  • Fill in some gaps with PowerShell putty …….

.

PowerShell to Bulk Add Lists and Libraries to SharePoint’s Quick Launch

Someone created a bunch of lists and libraries in a new site, and then a few days later wondered why they were no longer in Quick Launch. They had seen them there the day before in the “Recent” section.

I told them the steps to edit the properties of each list to add them to Quick Launch, and then they told me that there are over 30 lists. So… PowerShell to the rescue!  Here’s the on-prem version:

$site = Get-SPSite http://sp2016/sites/calcdemo

$web = $site.RootWeb     #or which ever web is needed

$lists = $web.Lists

$lists | where {-not $_.Hidden -and $_.Created -gt (Get-Date 12/21/2017)} |
           foreach { $_.OnQuickLaunch = $true; $_.Update() }

 

All done!

I filtered by date so that I would not change any lists that existed before their new work, and filtered by Hidden to exclude the SharePoint auto-generated lists.

Auto-populated Choice Columns in SharePoint!

 

This was tested in SharePoint 2013 and 2016.

Had a list with 100,000 items with a State column. I found that some of my users did not know their state abbreviations. (KE is Kentucky?) After cleaning up the "nonstandard" states, I decided to convert the column from Single Line of Text to Choice.

Magic!

After editing the column and clicking "Choice" I scrolled down and found that the list of choices was already populated!

For this to work for lists with more than the List View Threshold number of items (5,000 by default) you will need to be:

• a server administrator, or
• an auditor (Configured in Web Application settings, and only for under 20,000 items.), or
• working with the list during Happy Hour!  (Offically “Daily Time Window for Large Queries”.)

 

Bonus!

The State column was now available in my Metadata Navigation Settings options.

 

Too Easy!

In the future when importing large amounts of list data I’ll just make the columns that should be Choice as Single Line of Text and then after the import change them to Choice.

.

SharePoint 2016: List View Threshold Limit to Delete a List is 99,993 Items???

 

SharePoint 2013 had a default List View Threshold that used the number 5,000 for a lot of limits. SharePoint 2016 has made a few changes to the List View Threshold to give us a little more flexibility. If you take a look at the TechNet article “Software boundaries and limits for SharePoint Server 2016” you will find that the old 5,000 limit is still there for normal list activity, but they have made a few changes for Site Owner maintenance activities.

These include:

• When adding or removing a column index, the threshold is 20,000 by default.
• When deleting a list or folder, the threshold is 100,000 by default.
• When renaming a folder within the same library, the threshold is 100,000 by default.

Note that these limits are for Team Members, Site Owners and Site Collection Administrators. Server administrators can exceed these limits and everyone can during “happy hour!” (Officially, the “Daily Time Window for Large Queries” limit set by the SharePoint Server administrators.)

As I am working on a new course, “Microsoft SharePoint Server Content Management for SharePoint 2013 and 2016”, I have to both test these limits and create screen captures for classroom demos. I ran into two interesting discoveries:

• I could rename folders when there were more than 100,000 items. So this one must be for when there are up to 100,000 folders at the same level.
• I could NOT delete a list with 100,000 items. Or, 99,999 items.

The delete issue was a bit more interesting… I started deleting items, even emptied the Recycle Bin after each delete, but still could not delete the list… until I hit 99,993 items. Weird huh? That number is not even a magic number (a power of 2). I guess there must seven hidden, for SharePoint’s use only, items in that large list. Who knows…

I could not delete the following list until the item count was below 99,994.

99,993… now I can delete it.

 

Now… should I go an tie up the bandwidth to create a 100,000 item list in SharePoint Online to test there?

Of course!

 

.

Get the Version Number of a PowerShell Module

 

When a PowerShell script works for one person, but not for another, sometimes it's because the PowerShell module is a different version.

To find the version number:

Get-Module -ListAvailable "Microsoft.Online.SharePoint.PowerShell" | 
select name, version

 

If you need to deal with multiple versions in your scripts:

if ( (Get-Module -ListAvailable "Microsoft.Online.SharePoint.PowerShell").
  Version.ToString() -eq "16.0.4915.0") 
  { … do this } 
else 
  { … do this }

or maybe

if ( (Get-Module -ListAvailable "Microsoft.Online.SharePoint.PowerShell").
   Version.ToString() –lt "16.0.4915.0") 
   { "Must have 16.0.4915.0 or later"; Return; }

.

Using Relative URLs in SharePoint 2013 Workflow Calls

 

(For SharePoint 2013, 2016 and SharePoint Online.)

It's generally a good idea to use relative URLs when creating something that you will want to use in more than one place. While not real obvious, you can easily do this in SharePoint 2013 workflow web service calls.

Absolute URL:
   http://yourServerName/sites/yourSiteName/_api/web

Relative URL:
   /sites/yourSiteName/_api/web

What we would like to have in a workflow web service call:
   _api/web

Steps:

1. After adding your Call HTTP Web Service action, click “this”
   .
2. Click the "…" button to open the String Builder dialog.
    
   1. Click the Add or Change Lookup button.
   2. For Data source select Workflow Context.
   3. For Field from source select Current Site URL.
   4. Immediately after the lookup place holder (i.e. no spaces) type the rest of the URL for the web service call:
         _api/web
      
      
   5. Click OK.
3. Continue with web service call action configuration…

 

As you can probably guess… I’m working on a new class that includes workflows!

.

PowerShell to Find SharePoint Views Configured for More Than 5000 Items

 

Have any of your site owners been trying to create views with more than 5000 items? Have you changed the row limit to more than 5000 and have decided to set it back? Here’s some PowerShell to find all Views with a RowLimit set to more than 5000.

Here’s some PowerShell to find those views in an on premise SharePoint.

Get-SPSite -Limit All | 
   Select -ExpandProperty AllWebs |
   Select -ExpandProperty Lists |
   Select -ExpandProperty Views |
   Where {$_.RowLimit -gt 5000} |
   Select {$_.ParentList.ParentWebUrl}, {$_.ParentList.Title}, {$_.ParentList.ItemCount}, {$_.paged}, {$_.RowLimit} | ft -autosize

 

There are two properties of interest in the View object:

• Paged – Boolean – is paging enabled for the view.
• RowLimit – integer – number of items to display per page.

If you wanted to only include views without paging then change the Where line to:

    Where { ($_Paged –eq $false) –AND ($_.RowLimit -gt 5000) } |

 

.

SharePoint Online / Office 365 “Modern Library” Blank Pages

 

One of my PCs cannot display any of the new “Modern Lists / Library Experience” pages, including the OneDrive for Business pages. The pages are just blank. The issue turned out to be a URL that was in one of my blocked lists that is used to load a JavaScript library used by these new pages:
<script type="text/javascript" src="https://spoprod-a.akamaihd.net/files/odsp-next-prod_ship-2016-07-18_20160720.003/require-951f856e.js">

Removing akamaihd.net from my blocked list let these pages work again. The weird part is that this was only an issue in Internet Explorer 11. The pages loaded fine when using FireFox.

 

About the “Modern Library” Pages

These pages are almost completely generated from JavaScript, not HTML embedded in the page. If you use IE’s View Source command you will find that these pages are actually a bit weird, at least from an HTML point of view. The W3 validator page (http://validator.w3.org/check) has nothing nice to say about these pages!

 

The page as delivered, before the JavaScript runs, basically looks like this:

• A DOCTYPE directive that says the following is XHTML (but the W3 validator has issues with this):
  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
• An HTML tag that includes two meta tags and a number of <script> tags:
    <html dir="ltr" lang="en-US"><meta http-equiv="X-UA-Compatible" content="IE=edge"/>
  • A meta tag (outside of the HTML and HEAD tags!):
      <meta name="viewport" content="width=device-width,   …   />
  • A link tag to an icon:
      <link rel="shortcut icon" href="_layouts/15/images/favicon.ico   …   />
  • Seven script blocks, one of which points to an external source:
      <script …. ></script>
  • There are no <HEAD> tags!
• An end HTML tag:
     </html>
• An then an empty <BODY> tag:  (And I thought this had to be between <HTML> and </HTML>!)
    <body>   </body>
• And then several <script> tags (again outside of <HTML> … </HTML>!):
    <script …. ></script>

 

Oh, customizers take note, the traditional SharePoint master pages are not used here! There go all of our customizations… so far anyway.

 

.

Office 365 / SharePoint Online Site Contents Page Changes

 

SharePoint Online Latest Change of the Week / Day / Hour / Minute…

If you use Office 365 / SharePoint Online then you should now be used to the constant tinkering with the user interface. I’m starting to feel like SharePoint Online is kind of like the weather in Cincinnati… if you don’t like it, hang around, it will be different tomorrow.

One of the latest changes is to the Site Contents page. A preview of this page is documented in the link below. But… it’s already out of date! They have since added the Top Link bar back and the site icon. (To see these new pages in advance of general release you need to enable Preview Features in the tenant’s SharePoint Settings page.)

https://support.office.com/en-us/article/The-SharePoint-Site-Contents-page-ba495c1e-00f4-475d-97c7-b518d546566b?ui=en-US&rs=en-US&ad=US

 

The page as of 6/19/2016…

 

Changes to Site Contents:

• This is a “New SharePoint” style page. It is responsive and will somewhat adapt to screen resolution and device size. But like the other new responsive pages, a change of screen resolutions or zoom levels will make well known navigation elements move to new locations, or disappear. (Usually being rolled up into another navigation element.)
  Where did Quick Launch go? (It’s now the three slashes button) Where did the App launcher/waffle button go? (It’s now changed colors and has moved to the right into the middle of the other buttons.)
  
• This is no longer a master page based page or even a typical ASPX page. Right-click the page, select View Source and you will see that there’s basically an empty HTML tag and the loading of a bunch of JavaScript. If you use the F12 developer tools in your browser you will see that everything’s a DIV and there are MANY JavaScript files being loaded. The page is still stored in “_layouts” so there’s no customization through web parts or SharePoint Designer.
• +++ They changed the list of lists and libraries into a list!!! No more ugly blue squares, in no useful order and having to click Next, Next, Next.
  
• +++ The lists are sortable!!! (But not filterable or customizable. It would be really nice to group on list type or especially a custom property!)
• +++ They also changed the list of subsites into a list!!! It’s also sortable!
  
• - - - They added new big ugly blocks that we have to scroll past to get to the list of lists and subsites. These are site activity reports that really should be in their own page somewhere, maybe a “Site Activity” page. The first two big tiles do link to their own report pages.
  
• - - - They gave the page a new “New” button that will confuse the heck out of people.
     
  Click New and then List, you get a “Custom List”. No options. Click New and then Library, you get a generic library. If you want a Tasks list or an Announcements list, you have to click New and App. And then we are back to the ugly big blue tiles. (The New App page would be a great place to replace the blue tiles with a list! Give it two tabs, “Lists and Libraries” and “SharePoint Apps”.  Oops, I should have said “SharePoint Ad-ins”. They did tell us that they renamed these, right?)

 

So…

They cleaned up, and cluttered up, the Site Contents page.

 

Don’t like the new design… hang around!

(Today it’s hot and sunny in Cincinnati…)

 

.

SharePoint: Search for People using Properties (LastName:smith)

 

The typical SharePoint end user knows less than 1% of the SharePoint Search feature set. In other words, they know how to do a Google search. They just type a word or two, press Enter and hope for the best. Just think what they could do if they just knew a few search properties!

You can really improve search results by adding a Search Administrator to your team and letting them invest an hour or three a week in improving the end-user search experience. Train your end users, add tips to your search pages, create cheat sheets - there’s lots of options.

Just adding a few search tips to the search pages will do wonders:

  

So, let’s take a look at how users can do a better People search…

 

Searching for People

Let’s say I do a search for people using the keyword “training”. I could find people in the Training department, people with the word “training” in About Me, and even people with the last name of “Training”. If you would like to focus your search then you can use the predefined search Managed Properties. For example:

  

While a few of the properties can be used with the equals operator (“=”), most will only return results with the contains operator (“:”). For example, searching for a work phone number using “=” returns nothing. Using “:” will return the person with that number.

  

 

Managed Properties for People Searches

Most of the User Profile properties are searchable in a People search by just typing a keyword in the search box. You can also perform a People search using some of the out of the box Managed Metadata properties that are linked to the User Profile Services properties. In the table below you will find most of the User Profile properties along with the matching search Managed Property names. A few of the properties below have obvious names. A few are wrapped up in a single search property named “ContentHidded”. Some are “indexed” (crawled), but do not have the needed matching Managed Property. For most of those that do not have a matching Managed Property you can manually add a property to the search schema.

I will follow up with an article on adding the missing search Managed Properties.

Property for search	Property name found in the user’s profile	Notes
AccountName	Account Name	Example: accountname:contoso\samc
AboutMe	About Me
Interests	Interests
Responsibility	Ask Me About
FirstName	First name
LastName	Last name
PreferredName	Name	This is the full name. “Mike Smith”
WorkPhone	Work Phone	To find all users in the same area code or a partial number, use wild cards: workphone=513*
JobTitle	Title
WorkEmail	Work Email
MobilePhone	Mobile phone
	Home phone	Mapped to ContentsHidden. Can be mapped to a new Managed Property.
	Fax	Not mapped, but can be. (People:Fax)
Department	Department	This is a String property. This department maps to “Department”
	Department	This is a Managed Metadata property. Not mapped, but can be. (People:SPS-Department)
Schools	Schools
If you see this list	anywhere but on	TechTrainingNotes.blogspot.com, then it was “stolen” and used without permission.
OfficeNumber	Office	Generally used for “room number”.
BaseOfficeLocation	Office Location
PastProjects	Past Project
Skills	Skills
	Manager	Not mapped, but can be. (People:Manager) (returned as domain\username)
	Assistant	Not mapped, but can be. (People:Manager) (returned as domain\username)
	Birthday	Not mapped, but can be. (People:Birthday) (returned as “2000-03-01T00:00:00.0000000Z”)  All birthdays are set for year 2000.
	Hire Date	Not mapped, but can be. (People:SPS-HireDate)
ContentsHidden	(many)	This maps to several crawled properties as a single merged property: People:Office People:SPS-PastProjects People:SPS-School People:WorkPhone People:CellPhone People:Fax People:HomePhone People:SPS-MemberOf People:AboutMe People:OrganizationParentNames

 

.

SharePoint Folders Are Not EVIL!

 

It seems that everyday I run across another blog article, forum post or social media that says “Never Use Folders!” While one of the common analogies for SharePoint is the Swiss Army Knife, a better one might be a tool box, and one with a lot of tools. Saying “Never Use Folders” is kind of like saying never use an adjustable wrench because we have box wrenches. Tools are tools and you need to select the correct tool for the job.

The following is not an excuse to not create a formal taxonomy and use a pure metadata approach to content management. It is a description of one of your many SharePoint tools in your toolbox. Remember everything is not a nail, and your only tool is not just a hammer.

 

Sometimes You Just Can’t Afford Metadata

Not an excuse so much as a reality.

You just built your new SharePoint farm. You have hundreds of thousands of documents to migrate to SharePoint. Who’s going to add all of the metadata? You employees (in their free time?), summer interns, contractors?

If you maintain the folder structure during your migration from network shares then your users can still find content as they always have. And, when you have added all of your metadata you can then either hide the old folders in your views, or move the content into one giant folderless library.

 

Folders are metadata!

In fact, Folders are “instant metadata”. Just upload or drag the document to the right folder and everyone will know something about it. If it’s in the folder named “Chlamydoselachidae” then it must be something about “Frill Sharks”!

(I’ll give anybody at Microsoft a couple of dollars if they will add the folder name property to the available columns in a view. It would then be true metadata!)

Folders can have custom metadata

A folder is a Content Type. You can create new Content Types that inherit from Folder and then add metadata columns. While a search on the metadata does not return the files in the folder, it will return the folders.

Here’s an article I wrote back in 2007 that still applies to SharePoint 2010, 2013 and 2016: http://techtrainingnotes.blogspot.com/2007/08/sharepoint-how-to-create-links-from.html

  

  

  

 

Want really smart folders with metadata that shares their metadata with their contents?

Take a look at Document Sets. Not the out of the box example, but rather a custom one that you create by inheriting from the Document Set Content Type. If you add a Site Column named “Product Category” then every document in that Folder / Document Set will be findable from search on that property. If you move a document from one Document Set to another Document Set, the document’s inherited metadata is updated to match!

https://technet.microsoft.com/en-us/library/ff603637.aspx

https://support.office.com/en-us/article/Introduction-to-Document-Sets-C49C246D-31F1-4BFE-AFE2-E26698B47E05

https://support.office.com/en-us/article/Create-and-configure-a-new-Document-Set-content-type-9DB6D6DC-C23A-4DCD-A359-3E4BBBC47FC1

 

Folders can be nested more than two levels deep

Using views and metadata you can create two levels of grouping. If you have SharePoint 2007 or 2010, you can use SharePoint Designer to create views that are up to 16 levels deep. But for SharePoint 2013 and 2016 they have changed (broken) SharePoint Designer so you can only group deeper than two levels by hand crafting XLST and HTML.

You can nest folders as much as needed, up to the maximum URL limits of Path to Library + Folders + Filename.

 

Folders are ideal for a rigid taxonomy

If the primary way of accessing content is by a single hierarchy then a folder structure may be the better choice. While still limited to the maximum length of a URL, it clearly supports more than the two levels offered by a grouped view.

  

Want a full crumb trail like we had in SharePoint 2007? See here: http://techtrainingnotes.blogspot.com/2015/11/add-crumb-trail-to-sharepoint-2013.html

 

Folders can be navigated using a Tree View

There are actually two tree views available, one out of the box, and one that is hidden.

The Quick Launch Tree View (Settings, Site Settings, Navigation Elements):

  

The hidden SharePoint 2010 “Navigate Up” button:

(See: http://techtrainingnotes.blogspot.com/2014/06/sharepoint-2013-restoring-2010-navigate.html)

  

Note: Currently neither Tree View is available in the “new library experience” for SharePoint Online, and one day for SharePoint 2016 on premises.

 

Metadata is not always searchable as a property

Unless you have created Site Columns, and configured them as friendly search Managed Properties, then as far as seach is concerned, all of those columns of metadata might have just been typed into a single “Keywords” column.

 

Search Likes Folders

Search includes several managed properties to make finding folders and content in folders easy to do. Unlike Site Columns, these folder properties do not require any Search Service setup to work.

Path:    path:https://yourServer/sites/site/library/folder
            path:"https://yourserver/sites/taxonomy/Fish/Agnatha and Lampreys/Myxini/Myxiniformes"

Searching with Path works, and is very precise, and returns all of the content in that path. The negative is typing the full path to the folder.

contenttype:folder     contenttype:folder Myxiniformes

contenttype finds all folders and all content types that inherit from Folder. (This will also return folders that have a column with the keywords being searched. In the example above you will get folders with “Myxiniformes” in the folder name and folders with a column with “Myxiniformes” in its name.)

IsContainer:true        IsContainer:true Myxiniformes

IsContainer returns Sites, Libraries and Folders that have the keyword in their name or metadata. IsContainer also returns Team Site Notebooks (OneNote files) and content stored in Asset libraries (The thing you click on in an Asset library is a folder, not the actual picture or video.) as they are represented as folders.

Library search box

The search box at the top of each library assumes you only want to search the content in the current folder! (You can then click “Some files might be hidden. Include these in your search” to search the rest of the library.)

  

 

Microsoft / SharePoint Really Likes Folders!

Take a look at OneDrive for Business… you can’t even add metadata columns or use Content Types. “Name”, “Modified”, “Modified by”, “File Size” and “Sharing” are all you get. The only “metadata” I can add is by using folders.

   
    (Yes, I really have a folder named “junk”!)

In my OneDrive I have to embed metadata in the filename and/or the folder structure. Kind of like network shares!

   

 

The New Library Experience likes folders!

The new library experience in Office 365 makes it easy to arrange and rearrange documents by folder. (Seems to encourage the use of folders!)

   

 

Sync Only Sync’s Folders

All three of the sync clients only sync folder structure, not metadata. If you want any obvious classification of your local sync of the content then you have to use folders. The only metadata you can add from client side is in the filename and the location/folder.

 

Security and Folders

Remember when Microsoft’ advise was to never use item level permissions? At least until SharePoint 2013 where they gave everybody a “Share” button. Now SharePoint 2013 and 2016 encourage users to break inheritance everywhere!

See here for what can happen with unlimited use of the Share buttons: http://techtrainingnotes.blogspot.com/2015/10/trick-or-treat-day-in-life-of.html

For a simple example consider:

1. We create a site for Sales Managers. We create a library for their files.
2. The sales managers start clicking the Share links on various documents, most to share with the “Summer Interns” group and the “Marketing Team” group. Over time there are 500 items with broken inheritance.
3. Management asks you to add Regional Sales Managers to the site, with their own group.
4. You create a SharePoint group and add the Sales Managers and grant it access to the site.
5. The Regional Managers visit the site and complain that they can’t find all of the files the Sales Managers have told them about.
6. You now have to:
   1. Find the 500 files with broken inheritance.
   2. Grant permissions to each of the files to the Regional Managers group.

So what can you do? Use folders for permissions.

1. Create the library.
2. Add a folder for “Everyone”. (Optional as the files in the root of the library will be available to everyone by default.)
3. Add a folder for “Sales Managers Only”. Break inheritance and grant permissions to the Sales Managers group.
4. Add a folder for “Visible to Marketing Team”. Break inheritance and grant permissions to the Sales Managers group and to the Marketing Team group.
5. Add a folder for “Visible to Interns”. Break inheritance and grant permissions to the Sales Managers group and to the Interns group.
6. Create a new view named “Sales Files”:
   1. Make it the default view.
   2. In the Folders section hide the folders.

Users will now see a single list of content, which can also be grouped using metadata, but they will only be able to see the content they should see. The users who maintain the content use the AllItems view so they can quickly upload documents into the correct folder, and automatically apply the correct permissions. (Now all you have to do is hide those pesky Share buttons! http://techtrainingnotes.blogspot.com/2015/08/hiding-evil-sharepoint-2013-share.html)

 

So which should you use?
  Folders or
    Metadata+Views or
      Folders+Metadata+Views?

Use the best tool for the job!

 

.

SharePoint 2013 and SharePoint Online Built-In Accounts

 

I’m often chasing one SharePoint rabbit or another down a rabbit hole and spending hours there when I only wanted to ask the rabbit one simple question. In this case the question was who is “Everyone” and are they related to “NT AUTHORITY\Authenticated Users”. A simple question, or so I had thought. In this rabbit hole I found all kinds of interesting accounts, so I thought that I’d take a few notes while I was there. As to “Everyone”, I’ll follow up with another blog article. I also got distracted by two Office 365 users hanging around the hole named “Guest Contributor” and “Guest Reader” that will also get their own article.

If you would like to dive into the rabbit hole, here’s a few tools to investigate user accounts:

• PowerShell on prem: 
  $site = Get-SPSite http://yourDomain/sites/yourSite
  $site2.RootWeb.AllUsers | FT –AutoSize
• PowerShell for Office 365:
  Get-SPOUser -Site http://yourDomain/sites/yourSite | Select DisplayName, LoginName
• In the browser:
  • Go to Settings (gear), Site Settings, People and Groups
  • Edit the URL and change the GroupId to 0    (“?MembershipGroupId=0”)
  • Click any interesting user name. If the user has a User Profile you will probably be redirected to their profile page. If not, you will be redirected to userdisp.aspx where you can see the user name and their internal Account property as listed in the table below.
• Third party security and auditing tools.

 

Claims Based Authentication

SharePoint 2013 and later uses Claims Based Authentication which can support more than one authentication source. This slightly complicates the UserLogin property as it must have both the user name and the claims source data in the property value. In a non-Claims system the user name might be as simple as contoso\msmith. In a Claims system you need to know where the user was authenticated, so you end up with UserLogins that might look like i:0#.w|contoso\msmith for a Windows AD user or i:0#.f|ContosoFBA|susan for a Forms Based Authentication user.

If you would like to learn more about the Claims identity codes (“c:0!.s”, etc.) see: http://social.technet.microsoft.com/wiki/contents/articles/13921.sharepoint-2013-claims-encoding-also-valuable-for-sharepoint-2010.aspx
and
http://www.wictorwilen.se/Post/How-Claims-encoding-works-in-SharePoint-2010.aspx

 

The Users

Who are all of these users? Well… I’m still negotiating with the rabbit for more details, but I’ll soon add these articles with what I have discovered:

• SharePoint: All Users vs. Everyone vs. Everyone But External vs. NT AUTHORITY\AUTHENTICATED USERS
• SharePoint Online “Guest Contributor” and “Guest Reader” - Who’s Guest Contributor,
  and what are they doing in my library?
• SharePoint internal and hidden accounts hiding in your Site Collection

For now:

• NT AUTHORITY\AUTHENTICATED USERS represents all of the users in your Active Directory, on prem or in the cloud.
• Everyone at the AD level is NT AUTHORITY\AUTHENTICATED USERS plus the Guest account. The Guest is disabled both by default and as a best practice. (You don’t see this one in SharePoint, but it is often listed as being the same as the SharePoint “Everyone”.)
• Everyone is defined at the SharePoint level and includes all users authenticated to SharePoint.
• Everyone except external users is found in SharePoint Online / Office 365 and is as named. External users are people not in your Active Directory, most likely not employees, who got their access from site members clicking the SHARE buttons.
• All Users (<somename>) is SharePoint defined and represents all of the users from a selected authentication provider. (If I created a Forms Based Authentication provider named “Vendors” then I would have “Everyone (Vendors)”
• All Users (windows) is SharePoint defined and is same as NT AUTHORITY\AUTHENTICATED USERS. After adding “All Users (windows)” to a site it is displayed as “All Users (windows)” in 2013 on prem and 2016 on prem, but is displayed as NT AUTHORITY\AUTHENTICATED USERS in Office 365.
• Guest Contributor and Guest Reader are at this time only found in SharePoint Online / Office 365 and represent users with anonymous / link access.

 

Best Practices

I was reviewing some training materials recently and ran across a statement to the effect you should put NT AUTHORITY\AUTHENTICATED USERS in all of your site Visitors groups so everyone can find content in SharePoint. Should you do this? Should everything in your SharePoint be freely accessible to everyone who can logon to your network? Contractors, vendors, summer co-ops, part timers? If you don’t already have a policy or governance on this, then you should be working on it.

SharePoint does not give us any way to prevent the use of the “Everyone” accounts, so you will need to deal with this through education and auditing.

UPDATE! Anders Rask responded to this post with info about a SharePoint Online cmdlet that can hide these “everyone” options in the people pickers. Turns out there are three options:

Set-SPOTenant -ShowEveryoneClaim $false
Set-SPOTenant -ShowEveryoneExceptExternalUsersClaim $false
Set-SPOTenant -ShowAllUsersClaim $false

The Set-SPOTenant cmdlet: https://technet.microsoft.com/en-us/library/fp161390.aspx 
Blog: https://blogs.office.com/2015/07/16/new-it-management-controls-added-to-onedrive-for-business/

 

Here’s a short list of best practices. The term “everyone” used here includes NT AUTHORITY\AUTHENTICATED USERS and any account that starts with “Everyone” or “All Users”.

• Educate your users on security, including the use of the “everyone” accounts.
• Do not use “everyone” accounts if a site contains non-public data.
• Document who “everyone” is. There’s more than one “everyone” group in SharePoint.
• Perform regular audits using PowerShell or 3rd party tools to track the usage of “everyone” groups.
• Document, audit and enforce your SharePoint content policies. Document what is allowed, and what is not allowed to be stored in SharePoint.
• If you do encourage the use of the “everyone” groups, add a banner to the top of every page that declares “Do not post confidential data in this SharePoint site! It can be seen by everyone with network access.”

 

The Built-In Accounts

While your SharePoint may vary… see the Notes column… here’s a list of the accounts that may include users other than those who you were expecting. This is not complete, so if you discover others please post a comment to this article.

DisplayName	UserLogin or SystemUserKeyProperty	Notes
All Users (membership)	c:0!.s|forms%3amembership	Only O365
All Users (windows)	c:0!.s|windows	Same as NT AUTHORITY\ authenticated users
All Users (yourFBAMembershipProviderName)	c:0!.s|forms%3aYourFBAMembershipProviderName	Form Based Authentication
Everyone	c:0(.s|true
Everyone except external users	c:0-.f|rolemanager|spo-grid-all-users/17b83262-5265-…	Only O365 (ID will vary)
NT AUTHORITY\ authenticated users	c:0!.s|windows
Guest Contributor	SHAREPOINT\writer_9e8a77849f89425c9cff6a6af5175…	ID varies with share
Guest Reader	SHAREPOINT\reader_cb6f6371456b4542ba0609638a4…
_SPOCacheFull	ylo001\_spocachefull	Only O365. Visible only from PowerShell
_SPOCacheRead	ylo001\_spocacheread	Only O365. Visible only from PowerShell
_spocrawler_17_3910	ylo001\_spocrawler_17_3910	Only O365 (ID will vary)
System Account	SHAREPOINT\system	Visible only from PowerShell
System Account	S-1-0-0	SystemUserKeyProperty
Company Administrator	s-1-5-21-1851826741-1401831065-3463747319-87287…	Only O365 (ID will vary)
Typical user (Sam Conklin)	samc@yourDomain.onmicrosoft.com	As seen in O365 PowerShell
Typical user (Sam Conklin)	i:0#.w|yourDomain\samc	As seen in On Prem PowerShell
Typical user (Sam Conklin)	i:0).w|s-1-5-21-2499188511-2905385804-3446143336-…	SystemUserKeyProperty
Typical FBA user (Susan)	i:0#.f|YourFBAMembershipProviderName|susan	Form Based Authentication

.