SharePoint Date Search Tips

 

Applies to SharePoint 2013 and later.

 

A few SharePoint Search Tips!

 

Time Zone

Search internally stores dates in Universal Time. Because of this, a file uploaded at “2/7/2017 10:50 PM EST” will not be found with a search for “Write=2/7/2017” (or “LastUpdateDate=2/7/2017”). That file will be found with a search using “Write=2/8/2017”.

 

Date Ranges

You can create searches on date ranges using “..”.

      Example: write=2/1/2017..2/8/2017

 

Named Date Ranges

You can also use the names of some date ranges. Quotes are required around any range name that includes a space.

     Example: write="this week"

   

The supported ranges are:

• today
• yesterday
• this week
• this month
• last month
• this year
• last year

But sadly… no “"last week”!

 

Comparison Operators

Note: All of the following operators work with the DateTime, Integer, Decimal and Double data types.

Operator
	Equals
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
<>	Not equal to
..	Range

 

.

Creating Random Numbers in SharePoint Calculated Columns

 

One of my examples for tonight’s Cincinnati SharePoint User Group meeting! See you there!

 

I wanted to add a "motivational" message to a list of new sales. To be "fair" (i.e. I did not want to think and create a good algorithm!) I wanted the messages to be random. Something like this:

  

But… Calculated Columns do not support the Excel RAND() or RANDBETWEEN() functions.

 

So, how to get a random number???

Calculated columns do support the =Now() function. This returns a numeric value that represents the current date and time. If formatted as a Date, or Date and Time, then you will see the current date. But, if you format it as Single Line of Text you will see something like: 42,691.3977137731, or a few seconds later: 42,691.3983521875. The last number starts to look like a random number! And if accurate, it changes every .0000000001 of a day, or about every 0.00000864 seconds. Close enough for me.

 

Get a random number between 0 and 9.

This one looks easy, just pull off the last digit from NOW()!

    =RIGHT( NOW() ,1)

But.. there’s one flaw with this… The last digit of a fractional value is never zero!  (I.e. you will never see .111111110 unless custom formatted.)

So we need to pull off the next to last digit!

  =LEFT( RIGHT( NOW() ,2) ,1 )

 

Get a random number between 1 and 5

With just a little math we can limit the range a bit. As we don’t want the zero value we can skip the LEFT function for this one.

   =ROUND( RIGHT( NOW()) / 2+0.5 ,0)

  

Here’s a sample:

  

 

Get a random number between 0 and 999.

If you need bigger numbers, just return more digits:

    =RIGHT(NOW(),3)

As RIGHT creates a string (text), you will get leading zeros (“012”). To remove the leading zeros just do some math!

    = 0 + RIGHT(NOW(),3)

  

But… (there’s always a “but”), this will never return a value that ends with a zero. So… back to the LEFT function:

    =LEFT( RIGHT(NOW(),4), 3)

I.e. get the left three of the right four digits…

 

Random Messages?

This little exercise started out to create random messages. All we need to do is combine a random number with the CHOOSE function. As CHOOSE starts with item 1 and not item 0, we will need to add one to the random number.

   =CHOOSE( LEFT( RIGHT( NOW() ,2), 1) + 1, "Good Job", "Wow!", "Good Work", "Thanks!", "Could be better",
                      "Gold star for you!", "a free coffee for you!",":-)", "You are the MAX!","Do it again!" )

 

Notes

• These are not guaranteed to be mathematically pure random numbers!
• The values depend on the exact instant that an item is added to a list and will change with each edit. (But will not change with each view.)

.

Using PATCH with PowerShell’s Invoke-RestMethod

 

A story about a bug, an inconsistency and a solution…

 

I recently did a demo of using PowerShell's Invoke-RestMethod to create, read, update and delete (CRUD) data to a REST service written using ASP.NET's WEBAPI project template and ODATA controllers. Everything worked pretty much as expected except for using the PATCH method to change an existing item.

My GET worked as expected:

  Invoke-RestMethod 'http://localhost:41613/odata/Courses' | select -ExpandProperty value

 

My DELETE worked as expected:

  Invoke-RestMethod 'http://localhost:41613/odata/Courses(33)' -Method DELETE

 

My POST (create) worked as expected:

  $bodynew = @{ CourseCode='aa111'; Description='test'; Category='test'; Title='Test Course'}
  invoke-restmethod 'http://localhost:41613/odata/Courses' -Method POST -Body $bodynew

 

My PATCH (and MERGE) failed!

  $bodyupdate = @{ Title='Updated Title!'}
  invoke-restmethod 'http://localhost:41613/odata/Courses(34)' -Method POST -Body $bodyupdate

At least that gave me two hints… "no body" and "The inferred media type 'application/octet-stream' is not supported for this resource." The second one was probably the easiest to fix… tell it that I'm sending JSON.

   $headerJSON = @{ "content-type" = "application/json;odata=verbose"}

This got past the Invoke-RestMethod error when using PowerShell 4, but got a new error when using PowerShell 3! "Invoke-RestMethod : The 'content-type' header must be modified using the appropriate property or method." (it's a bug!) But now my ASP.NET WebApi application threw an error, "NullReferenceException". No data was received! (Remember the "no data" error?) So, maybe it was not in the expected format. When using the jQuery AJAX method you serialize your object into JSON before sending. Maybe that would work here:

  $bodyupdateAsJSON = @{ Title='Updated Title!'} | ConvertTo-Json
  Invoke-RestMethod 'http://localhost:41613/odata/Courses(34)' -Method PATCH -Body $bodyupdateAsJSON
      -Headers $headerJSON

  Bingo!

 

The Question then is…

Why can I pass a -Body for a create (POST) without any additional work, but when I pass a -Body with an update (PATCH or MERGE), I have to pass the data as JSON and add a header to state that I'm sending JSON?

 

Will POST work with those two changes?

Actually it will! Invoke-RestMethod will do a POST using default PowerShell objects for -Body as long has you don't add a -Header that specifies JSON as the format. Invoke-RestMethod will also do a POST using JSON data as long as you do supply the right -Header. It's probably a best practice to be consistent and explicitly use JSON for both.

 

Live and learn…

 

 

Details:

Tested with PowerShell 3.0 and 4.0. PowerShell 3.0 fails when trying to set a header for JSON with "Invoke-RestMethod : The 'content-type' header must be modified using the appropriate property or method." (It's a bug!)

The .NET application:

• Visual Studio 2015 ASP.NET Web API project with ODATA controllers
• .NET Framework 4.5.2
• Entity Framework 6.0
• SQL Server in Azure
• Test project hosted in Azure.

.

Forest and Trees Problem: "A network-related or instance-specific error occurred while establishing a connection to SQL Server"

 

A "Can't see the tree for the forest" problem.

There's an old phrase, "Can't see the forest for the trees", that in reverse, "Can't see the tree for the forest", applies to a recent "demo fail" of mine.

During a break in a C# class I was delivering last week I typed up a little demo for accessing SQL Server, and got an error. A quick read of the error said that it couldn't find the server, and it hinted at a protocol error.  

Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

   

 

Seeing the big "Named Pipes" tree standing there… I Binged and Googled and found all kinds of stuff… that did not help.

While the answer to the problem was clearly stated in the message above, "Verify the instance name is correct", I didn't see it as I was looking at all of the other "trees" in that little code forest. The "tree" that I needed to deal with in this case was a C# beginner error of putting a backslash in a string. (I copy and pasted it without looking at it!) The back slash is an escape character to flag the next character as a special code. In this case "\v" is the code for a Vertical Tab. So, I had created a connection string looking for a server named "(localdb)VerticalTab11.0".

What made this little error a bit painful was that in this class I had mentioned escape characters in C#, and how to deal with them, at least four times! Oh well…

To solve the problem, escape the escape character ("(localdb)\\v11.0") or mark the entire string as a literal string with the At sign ("con.ConnectionString = @"Data Source=(localdb)\v11.0 …").

  

For a list of the C# escape characters see this MSDN article:
   https://msdn.microsoft.com/en-us/library/h21280bw.aspx

.

You Can Now Create "Modern" Pages in SharePoint Online

 

It looks like "modern pages" are now rolling out to the tenants with the preview options on. You can still create Wiki Pages and Web Part Pages in addition to the new "Site Page" type. Here's both the new and classic Site Pages library “New” menus.

  

The new pages have the "warm and fuzzies". Click to enter a page name. Note the Publish button. New pages are left checked out until "published". Major versions are enabled on the Site Pages library.

While web parts are not listed yet, you can add some "widgets" to the new pages. (Hey! There's a Yammer thing there!)

 

So… stay tuned to see what will appear next!

 

.

Hide the Windows Explorer Button in SharePoint Libraries

 

Tested in SharePoint 2013, 2016 and SharePoint Online.

 

The Windows Explorer view of a SharePoint library has so many issues that I'm often asked to hide it. Turns out that this is pretty easy to do. Two solutions:

• Create a SharePoint Feature and deploy it to the desired site collections.
• Add CSS to your master page, or to selected view pages.

 

Create a SharePoint Feature and deploy it to the desired site collections

This is the best solution! And it's been documented elsewhere: https://blogs.msdn.microsoft.com/tejasr/2010/07/19/how-to-remove-open-with-windows-explorer-button-from-document-librarys-ribbon-menu/

I would only add one more step to this solution… make sure the WSP file does not include an unneeded DLL so the solution can be deployed to SharePoint Online. (No code allowed!) The one extra step: In the project's Properties panel click "Include Assembly in Package" and change it to False.

Once the Feature has been installed in the Site Collection, just visit each subsite and activate the feature. This will impact all libraries in the site.

 

Add CSS to your master page, or to selected view pages

Add one little piece of CSS to your master page, or open SharePoint Designer and edit the library's views to add a CSS block and the button will disappear. If added to the master page then this will impact every library in the site. If added to a view page, then it will impact only that view.

This CSS will not work with the SharePoint Online “New Library Experience”. But then the new “experience” does not currently include a link for Windows Explorer!

The CSS:

<style type="text/css">
  #Ribbon\.Library\.Actions\.OpenWithExplorer-Small {
    display: none;
  }
</style>

Note: The backslashes have been added to the ID due to the non-standard naming convention that uses periods.

If you would like site owners to still be able to see the button then wrap additional CSS in a SharePoint:SecurityTrimmedControl. Note that this control can only be added directly to a page, typically using SharePoint Designer. It will not work if added to a Content Editor Web Part.

<style type="text/css">
  #Ribbon\.Library\.Actions\.OpenWithExplorer-Small {
    display: none;
  }
</style>
<Sharepoint:SPSecurityTrimmedControl runat="server" PermissionsString="ManageWeb">
  <style type="text/css">
    #Ribbon\.Library\.Actions\.OpenWithExplorer-Small {
      display: inline;
    }
  </style>
</SharePoint:SPSecurityTrimmedControl>

 

.

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!

.