SharePoint Dates are Always Date AND Time

A Quick SharePoint Date Tip!

When you create a Date column you have the choice of Date and Date & Time.

   

Note the keyword “Format” in that option. Even if you select “Date Only”, your users can still type, or copy and paste, a date and a time and it will be stored as a date and time. But… only the date will be displayed.

Times are a Fraction of a Date

Times are represented as parts of a day.

   

Converting a Date and Time to a Date

As you saw above, SharePoint dates are always dates and times. Even if you set a column to be “Date”, users can still type, or copy and paste, Date and Time values. Columns formatted as “Date” may display as just a date, but they will still be filtered and calculated as a Date and Time.

If you are calculating the number of days between two dates you might write “[Date2]-[Date1]”, which will produce the expected result if both dates are true Date values. If Date2 is “1/16/2018 6:00 AM” and formatted as “1/16/2018”, and Date1 is “1/14/2018” subtracting the two will return 2.25 days, not the hoped for 2 days.

There are several ways of converting a Date and Time to a Date:

• Use the DATE function:
      DATE( YEAR( [SomeDate] ), MONTH(( [SomeDate] ), DAY( [SomeDate] ) )
• Use the ROUND function:
      ROUND( [SomeDate], 0) (This will round up or down depending on the value)
• Use the FLOOR (round down) function:
      FLOOR( [SomeDate], 1)
• Use the CEILING (round up) function:
       CEILING( [SomeDate], 1)
• Use the INT function (which also rounds down):
      INT( [SomeDate])

I prefer the INT as it is the least typing and I most often need to round down to remove the fractional part of a Date and Tim   

The Mysterious Semicolon in SharePoint Search

Discoveries:

• A semicolon in a search is the same as the keyword "AND".
• You cannot uniquely find text that contains a semicolon. (such as ABC;DEF)
• A semicolon used as data in a checkbox enabled Choice column causes data and search problems.

If you have more info, or know of an escape character for “;” please post a note to this article!

";" equals "AND"?

If I were to search a SharePoint list for "111 AND 222", I would find items where "111" might be in one column and "222" might be in another, or both are two words in the same column. I was trying to find data that looked like this, "111;222", and was getting wrong matches. At first I thought the semicolon was behaving as a wildcard, and that would have been really cool as SharePoint does not support anything other than an "*" at the end of a word.

From testing, it appears that a semicolon is identical to the keyword "AND". In the example below, note that all of the sample text entries are using "word delimiters" that SharePoint recognizes as search word breakers. As "data" a semicolon is treated as "white space" or a word breaker, and is ignored by search!

Here is a search for "111" AND "44" and note that no items were found.

You can't uniquely search for a semicolon!

If your data contains symbols in the middle of text, you will have a problem when searching for text that contains a semicolon. In the example below, note that the search for "555;777" also finds other items.

The addition of quotes will solve this problem for other special characters, but not the semicolon.

Semicolons and Choice Columns

A discussion of the semicolon would not be complete if we don't talk about what it does to a Choice column!

Consider Choice column with these choices: (third choice has a semicolon)

And for this Choice column we selected Checkboxes.

Now let's select some data. Notice the display when I select two items in a QuickEdit view. The semicolon is part of one item's data, and is also there as a delimiter between items.

When I click off of that row, note that the two selected items are being treated as three items!

When I attempt to edit that column again, the item with the semicolons has been lost because there are no "aaa" or "bbb" items in the list.

So…

• Don't put semicolons in your data if you plan to search for it.
• Don't put semicolons in multi-choice (Checkbox) columns.

SharePoint TEXT() Function Bug for Scientific Notation

In Excel you can use the TEXT function to display very large and very small numbers in scientific notation.

TEXT([Number],"0.00E+00")

Due to a bug in SharePoint Calculated Columns, the TEXT function adds some extra zeros to the end of the formatted number.

Here’s a workaround…

Assuming you want the equivalent of TEXT([Number],"0.00E+00")

=LEFT(TEXT(Number,"0.00E+00"),6) &
 TEXT(MID(TEXT(Number,"0.00E+00"),7,LEN(TEXT(Number,"0.00E+00"))-8),"00")

This part:

LEFT(TEXT(Number,"0.00E+00"),6)

Pulls off the "3.70E+".

This part:

MID(TEXT(Number,"0.00E+00"),7,LEN(TEXT(Number,"0.00E+00"))-8)

Pulls off the "9" and formats it as "09”.

To change the format, adjust the number of zeros in the obvious places, and adjust the "6", "7" and "8" as needed. Here two examples to help you figure out which numbers to change.

For the equivalent of TEXT([Number],"0.00E+000")

=LEFT(TEXT(Number,"0.00E+000"),6) &
 TEXT(MID(TEXT(Number,"0.00E+000"),7,LEN(TEXT(Number,"0.00E+000"))-9),"000")

For the equivalent of TEXT([Number],"0.000E+00")

=LEFT(TEXT(Number,"0.000E+00"),7) &
 TEXT(MID(TEXT(Number,"0.000E+00"),8,LEN(TEXT(Number,"0.000E+00"))-9),"00")

Here the "0.00E+00", "0.00E+000" and "0.000E+00" results.

Added to sharepoint.uservoice.com

I just listed this as a bug. If you would like to vote for a fix, go here: https://sharepoint.uservoice.com/forums/329214-sites-and-collaboration/suggestions/33878629-fix-text-function-bug-for-scientific-notation

As the feature has minimal usage, and has been there forever, I don't expect too much... ;-)

.

Shorter SharePoint Calculated Column OR Statements

Tested in SharePoint 2010, 2013, 2016 and Online.

Available on Amazon!

Array Constants

Excel will often let you use an array of values where you would normally use a range of cells.

The Excel version:

=VLOOKUP( [StatusCode], 
 {"a","Active";"i","Inactive";"c","Closed"},  , 2, 0 )

The array constant part of the formula is from “{“ to “}” and represents a two dimensional table. The Excel range equivalent to this array looks like this:

   

But…

While that is kind of cool, it does not always work in SharePoint Calculated Columns. One place it does work is with an OR function where it works much like the “IN” keyword in other technologies. The following two functions are equivalent:

=IF( OR( StateCode="OH", StateCode="KY", StateCode="IN", StateCode="IL" ),
     "Central Region", "Other" ) 

=IF( OR( StateCode = {"OH","KY","IN","IL"} ),
     "Central Region", "Other")

(If you see this anywhere other than at TechTrainingNotes.blogspot.com, it was stolen and used without permission!)

Both of the above produce this result:

   

Pretty cool!

Adding Fractions (1/4) to SharePoint Calculated Columns

(The following works SharePoint 2010, 2013, 2016, SharePoint Online, and most likely, 2019!)

0, 1/8, 1/4, 3/8, 1/2, 5/8, 3/4, and 7/8

This is a sample from my soon to be published book “SharePoint Calculated Columns and Validation Formulas”. If you would like to see a lot more about what you can do with Calculated Columns, and maybe win a free book, attend my session at SharePoint Cincy 2018 on April 10th!

In Excel you can get fractions using =TEXT(yourNumberColumn,"# ?/?"):   

The same function in SharePoint will round off the number, and for each of the above values, return 5. If you need fractions then you will need to write your own function to create them. Below we have several versions of this function depending on the fractions and formatting needed.

  

Function to return the fractions 0, 1/4, 1/2, and 3/4

Note: Read to the end to see a shorter version that uses CHOOSE instead of nested IFs.

Just a couple of nested IFs…

=IF( yourNumberColumn - INT(yourNumberColumn) = 0.25,
          INT( yourNumberColumn) & " 1/4",
   IF( yourNumberColumn - INT(yourNumberColumn) = 0.5,
           INT( yourNumberColumn) & " 1/2",
     IF( yourNumberColumn - INT(yourNumberColumn) = 0.75,
              INT( yourNumberColumn) & " 3/4",
         INT(yourNumberColumn) ) ) )

The above formula only works if the entered values end in exactly 0, .25, .5 and .75. If you need your function to round off to the nearest 1/4^th, replace the first value in each IF with:

    ROUND( yourNumberColumn * 4, 0 ) / 4

=IF( ROUND( yourNumberColumn*4, 0 ) / 4 - INT(yourNumberColumn) = 0.25,
     INT(yourNumberColumn) & " 1/4",
   IF( ROUND(yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.5,
       INT(yourNumberColumn) & " 1/2",
     IF( ROUND(yourNumberColumn * 4 , 0 ) / 4 - INT(yourNumberColumn) = 0.75,
         INT( yourNumberColumn) & " 3/4",
         ROUND( yourNumberColumn,0) ) ) )

If you would like to see ¼ instead of 1/4 then use the CHAR function to get those symbols. While the ¼, ½, and ¾ characters are available, 1/3 and 1/8 are not.

• Replace " 1/4" with " " & CHAR(188)
• Replace " 1/2" with " " & CHAR(189)
• Replace " 3/4" with " " & CHAR(190)

=IF( ROUND( yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.25,
     INT(yourNumberColumn) & " " & CHAR(188),
   IF( ROUND(yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.5,
       INT(yourNumberColumn) & " " & CHAR(189),
     IF( ROUND(yourNumberColumn * 4, 0 ) / 4  -INT(yourNumberColumn)=0.75,
         INT( yourNumberColumn) & " " & CHAR(190),
         ROUND( yourNumberColumn, 0 ) ) ) )

Function to return the fractions 0, 1/8, 1/4, 3/8, 1/2, 5/8, 3/4, and 7/8

To add support for eighths we need to add four more IFs. If we change the number used in our rounding code from 4 to 8, and add a few lines, then web can display values to the nearest eighth.

=IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.125,
       INT(yourNumberColumn) & " 1/8",
   IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.25,
         INT(yourNumberColumn) & " 1/4",
     IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.375,
            INT(yourNumberColumn) & " 3/8",
        IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.5,
              INT(yourNumberColumn) & " 1/2",
           IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.625,
                 INT(yourNumberColumn) & " 5/8",
              IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.75,
                    INT(yourNumberColumn) & " 3/4",
                 IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.875,
                      INT(yourNumberColumn) & " 7/8",
    ROUND(yourNumberColumn, 0 ) ) ) ) ) ) ) )

Now we have fractions to the nearest 1/8th.

    

A Much Shorter Version that uses CHOOSE

Here is a much shorter version of a formula to display a number as a fraction:

=ROUNDDOWN( yourNumberColumn + 0.062499, 0 ) &
  CHOOSE( ( ROUND( yourNumberColumn * 8, 0 ) / 8 
            - INT(yourNumberColumn) ) * 8 + 1,
          ""," 1/8"," 1/4"," 3/8"," 1/2", " 5/8", " 3/4", " 7/8",""
        )

This uses CHOOSE to pick the text to display for “less than 1/8^th”, “1/8^th” … “7/8^th” and “more than 7/8^th”. (Nine possible values.)

To get the fraction:

1. Round the number to the nearest 1/8^th:
       ( ROUND( yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) ) * 8
2. And as CHOOSE starts with 1 and not 0, add one to the result:
       ( ROUND( yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) ) * 8 + 1
3. Use CHOOSE to pick the text:
       CHOOSE( … , ""," 1/8"," 1/4"," 3/8"," 1/2", " 5/8", " 3/4", " 7/8","")
4. Concatenate that to the rounded down number:
       ROUNDDOWN( yourNumberColumn + 0.062499, 0 ) & …
   The “+ 0.062499” is added to deal with the last 1/16^th after “7/8^th” so we round up to the next higher number.

.

Adding Roman Numerals to SharePoint

In a Calculated Column

Assuming an integer in a numeric column named the "TheNumber", just add this equation to a Calculated Column:

    =ROMAN(TheNumber)

  

You can also select alternate formats:

   

See: https://support.office.com/en-us/article/ROMAN-function-D6B0B99E-DE46-4704-A518-B45A0F8B56F5

In a Page, Content Editor Web Part or a Rich Text Multiple Lines of Text column.

The Rich Text ribbon offers buttons to create bullets and numbers, but does not have one to select the other list formatting options. With a little HTML edit you can get any of the HTML list styles.

1. Create the numbered list as usual. (1., 2., 3....)
2. Click the "Edit Source" button in the ribbon.
3. Find the <ol> tag and change it to <ol type="I">. (Use a lower case "i" for lower case Roman numerals.)
4. Click OK.

Additional List Styles

When starting with a numbered list you can choose any of the HTML ordered lists (<OL>) types:

• type = "1" – numbers
• type = "A" – Uppercase letters: A, B, C
• type ="a" – Lowercase letters:  a, b, c
• type – "I" – Uppercase Roman numerals: I, II, III
• type = "I" – Lowercase Roman numerals:  i, ii, iii

When starting with a bulleted list you can choose any of the HTML unordered lists (<UL>) types:

• disc
• circle
• square
• none

You can also use your own custom images. See: https://www.w3schools.com/cssref/pr_list-style-image.asp

.

A SharePoint Calculated Column for all 50 States (workarounds for nested IF limits!)

Just in case you might ever need a formula to convert state abbreviations into state names…

• You will need 50 nested IFs,
• but SharePoint 2007 and 2010 only allows 7, and 2013 and later only allows 19.
• You will need a little more than 1300 characters in the formula,
• but SharePoint 2007 and 2010 only allow 1024. (2013 and later are around one billion!)

The trick for the IFs is to only nest 19 at a time and return a state name, or an empty string (""), and then concatenate another 19 nested IFs that return a state name, or an empty string… repeat until done! If you are using 2007 or 2010, then nest 7 at a time, and then concatenate another 7.

But what about the 2007 and 2010 1024 character limit? Renaming the "state" column to just one letter brings the formula down to 1111 characters, but that's still more than the 1024 allowed. Solution? Three Calculated columns. The first has the formulas for the first 25 states (in multiple IF nestings of 7 or less) that returns a state name or an empty string, The second has the next 25 states and returns a state name or an empty string. The third just concatenates the first two columns.

Here's the formula for SharePoint 2013 and later for a column named "State":

if(State="AL","Alabama",
if(State="AK","Alaska",
if(State="AZ","Arizona",
if(State="AR","Arkansas",
if(State="CA","California",
if(State="CO","Colorado",
if(State="CT","Connecticut",
if(State="DE","Delaware",
if(State="FL","Florida",
if(State="GA","Georgia",
if(State="HI","Hawaii",
if(State="ID","Idaho",
if(State="IL","Illinois",
if(State="IN","Indiana",
if(State="IA","Iowa",
if(State="KS","Kansas",
if(State="KY","Kentucky",
if(State="LA","Louisiana",
if(State="ME","Maine",""))))))))))))))))))) &
if(State="MD","Maryland",
if(State="MA","Massachusetts",
if(State="MI","Michigan",
if(State="MN","Minnesota",
if(State="MS","Mississippi",
if(State="MO","Missouri",
if(State="MT","Montana",
if(State="NE","Nebraska",
if(State="NV","Nevada",
if(State="NH","New Hampshire",
if(State="NJ","New Jersey",
if(State="NM","New Mexico",
if(State="NY","New York",
if(State="NC","North Carolina",
if(State="ND","North Dakota",
if(State="OH","Ohio",
if(State="OK","Oklahoma",
if(State="OR","Oregon",
if(State="PA","Pennsylvania",""))))))))))))))))))) &
if(State="RI","Rhode Island",
if(State="SC","South Carolina",
if(State="SD","South Dakota",
if(State="TN","Tennessee",
if(State="TX","Texas",
if(State="UT","Utah",
if(State="VT","Vermont",
if(State="VA","Virginia",
if(State="WA","Washington",
if(State="WV","West Virginia",
if(State="WI","Wisconsin","")))))))))))

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 …….

.

SharePoint 2016 Durable Links

I recently had a question in class about “Durable Links”. I did a search of the Microsoft sites to find anything official on SharePoint 2016 “Durable Links”, and basically only found a beta vintage blog article.

https://blogs.technet.microsoft.com/wbaer/2015/09/22/durable-links-in-sharepoint-server-2016-it-preview/

While I did find a number of other blog articles from the beta period, mostly of the “what’s new in SharePoint 2016” type, I found no TechNet articles. So… I thought I’d share part of one of my courses that has a section on Durable Links. This course is available from many Microsoft Learning partners, and of course, from MAX!

From:
Course 55198A: Microsoft SharePoint Server Content Management for SharePoint 2013 and 2016

SharePoint 2016 Durable Links

Prior to SharePoint 2016, renaming or moving a file would break all of the links and shortcuts that pointed to the file. In SharePoint 2013 you might have had a file named “FinancialStatementFY14Q2.xlsx” that had no spaces in the name. This is both an ugly filename and a name that will cause problems with search. (Users searching for “Statement” or “FY14” would never find it based on the title.) The 2013 URL would look something like this:

http://yourServer/sites/yourSite/Shared%20Documents/FinancialStatementFY14Q2.xlsx

Renaming this file to include spaces in the name would create the following URL. But, users with links to the old file will no longer be able to find it.

http://yourServer/sites/yourSite/Shared%20Documents/Financial Statement FY14 Q2.xlsx

Note the spaces in the URL will be replaced with “%20”.

Durable Links
SharePoint 2016 now appends a “d” query string to the URL with a unique ID that will not change even if the file has been renamed. (But not always… see notes below…)

http://yourServer/sites/yourSite/Shared%20Documents/FinancialStatementFY14Q2.xlsx?d=w780e689061e44dbfb4123fe450f4b957

After renaming, SharePoint will still find the correct document as it looks for the Durable Link ID first to find the document.

http://yourServer/sites/yourSite/Shared%20Documents/Financial%20Statement%20FY14%20Q2.xlsx?d=w780e689061e44dbfb4123fe450f4b957

To find the Durable Link in SharePoint 2016, or the SharePoint Online “Classic UI”, click the “…” next to the filename.

Notes:

• Durable Links are not a feature and cannot be enabled or disabled.
• Durable Links require Office Online Server to be part of the farm.
• Works with Office documents like Word, Excel and PowerPoint (i.e. things displayed in Office Server), but not other files like .jpeg, .png, etc.
• At the time of this writing, the Office 365 / SharePoint Online “Modern Library” pages do not offer a way to copy the URL that includes the Durable Link query string.
• Documents that are moved (drag and drop or cut/paste) will preserve the Durable Link ID. Documents that are copied and pasted will get a new Durable Link ID.
• The Publishing site Content and Structure feature does not preserve the SharePoint 2016 Durable Link. (A new ID is assigned after a Move.)

  .