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

  .

SharePoint Search Weirdness – Part 5: Search REST API Ignores Duplicates

A continuation of the "Search Weirdness" series!

If you are a developer, or a SharePoint 2013 workflow designer, then you probably have used the SharePoint Search REST API. Did you know that you are probably not getting all of the results expected?

Here’s a typical REST search for the word “sharepoint”:

http://yourSiteUrl/_api/search/query?querytext='sharepoint'

Or if you would like to be a little more selective:

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'

or you would like to return more than the default number of items:

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'&rowlimit=1000

The problem with the above searches is that Search thinks some of your results are duplicates, so it removed them! To solve this problem just add this to your URL:

    &trimduplicates=true

Your search URLs then might look like these:

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&trimduplicates=true

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'&trimduplicates=true

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'&rowlimit=1000&trimduplicates=true

.

Adding HTML to SharePoint Columns – Color, Images and More – Round 2!

Back in June Microsoft announced they were blocking HTML created by Calculated Columns with the June 2017 Public Update (PU) for SharePoint 2013, 2016 and SharePoint Online.

See here: http://techtrainingnotes.blogspot.com/2017/12/no-more-html-in-sharepoint-calculated.html

Before the June update:

After the June update:

You can turn this new “feature” off using PowerShell… but only for on-prem.

https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields

$wa = Get-SPWebApplication http://yourWebAppUrl
$wa.CustomMarkupInCalculatedFieldDisabled = $false
$wa.Update()

Repeat for each web application as needed.

There’s a workaround!

There's a fairly simple solution that works in all versions, if you don't mind using a workflow.

1. Edit the Calculated Column with the HTML and change it's "The data type returned from this formula is" back to "Single Line of Text". (Just change the result type... leave the column as a Calculated Column.)
2. Create a new Multiple Lines of Text column and set it to "Enhanced rich text (Rich text with pictures, tables, and hyperlinks)".
3. Create a workflow that simply copies the Calculated Column to the new Multiple Lines of Text column. Set the workflow to run on Created and Changed.
4. Edit your views to hide the Calculated Column and add the Multiple Lines of Text column.

The workflow is just a single Set Field in Current Item action.

Set the “field” to the new Multiple Lines of Text column and set “value” to the Calculated column. Publish and test!

This solution will let you keep the Calculated Column for easy revising of the formula logic. You could also let the workflow do all of the work to create the logic and HTML using a String Builder, and eliminate the need for the Calculated Column.

Update the Existing Items

You now need to get the workflow to run on all of the existing items. You can run a PowerShell script to start the workflows, you can run a PowerShell script just to copy the data from the Calculated column the new column, you can manually run the workflows on each item, or if you don't mind the Modified date and Modified By being changed switch to the Quick Edit view and copy all the items in one column and then paste them right back.

After the workaround:

What does not work?

Script tags and Style tags. (and I’m sure there are a few more) Style blocks are emptied and script blocks are completely removed. But, basic HTML for hyperlinks, image tag, etc. still work.

Before: <style>#test { color:red }</style><script>alert(1)</script> more HTML…

After: <style></style> more HTML…

.

SharePoint 2013 Workflow Error: Invalid Text Value

I got the not so obvious error below from a simple SharePoint 2013 style workflow.

Invalid text value.
A text field contains invalid data. Please check the value and try again.

The problem was actually pretty simple. I was trying to write more the 255 characters to the Workflow Log. You will get the same error writing more than 255 characters to a Single Line of Text column. You can use an Extract Substring from Start of String action to retrieve only the first 255 characters.

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.

No More HTML in SharePoint Calculated Columns!

Update: Here's a workaround using a workflow: http://techtrainingnotes.blogspot.com/2018/01/adding-html-to-sharepoint-columns-color.html


Just in case you missed it:

“Some users have added HTML markup or script elements to calculated fields. This is an undocumented use of the feature, and we will block the execution of custom markup in calculated fields in SharePoint Online from June 13, 2017 onwards. The June 2017 Public Update (PU) and later PUs will make blocking a configurable option for on-premises use in SharePoint Server 2016 and SharePoint Server 2013.”

https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields

So, no more of this:
  
Or this:
    =REPT("<img src='http://yourPath/yourImage.GIF' style='border-style:none'/>",[Value])