SharePoint: Create a View for Only Last Month

 

You have to learn some real tricks to get views filtered just the way you want, especially with dates. Dates are fun in that the filter can only compare a date column to an exact date or [Today]. Here's two examples that should get you a start on a wide range of date related filters.

 

Just Last Month Please

What we need: Only the items where some date is during the last full month. Not 30 days ago, but between the first and last day of last month.

 

Create two new columns

These two columns calculate the range of dates to show this item. So to figure this out, stop thinking about this month and last month and think about when do you want an item with a certain date displayed. If the item has a date of 2/14/2015 then we want it displayed from 3/1/2015 to 3/31/2015.

For this example the date column we are testing against is named TheDate. (Creative huh!)

Column 1:  (to calculate the first day of the month after TheDate)

  Name: StartDisplayDateForLastMonth
  Type: Calculated
  Formula: =DATE(YEAR(TheDate),MONTH(TheDate)+1,"1")
  The data type returned from this formula is:  (o) Date & Time
  Date and Time Format: (o) Date Only

Column 2:  (to calculate the last day of the month after TheDate)

  Name: EndDisplayDateForLastMonth
  Type: Calculated
  Formula: =DATE(YEAR(TheDate),MONTH(TheDate)+2,0)
  The data type returned from this formula is:  (o) Date & Time
  Date and Time Format: (o) Date Only

Here's a screen shot of the first column. (click to enlarge)

   

 

Create the View

Create a view with your usual choice of columns, but not the two we just added. You may want to add these columns for test though.

   

Display and test the view!

 

How does it work?

=DATE(YEAR(TheDate),MONTH(TheDate)+1,"1")

DATE needs three values, a year number, a month number and a day number. The first two are easy, if you know a weird fact. YEAR() returns the year number (2015) and MONTH() returns the month number (3). Obviously MONTH()+1 is next month… but what about when it's December plus 1? That's the weird fact. Month 13 bumps up the year value!  So DATE(2014,13,5) is 1/5/2015. The 1 at the end of the formula is for day "1" of the month. Don't believe me? Fire up Excel and play around with the DATE function.

=DATE(YEAR(TheDate),MONTH(TheDate)+2,0)

This one depends on another weird fact… day zero is the last day of the previous month!  DATE(2015,3,0) is actually 2/28/2015. And of course, DATE(2016,3,0) is 2/29/2016! So what we are doing here is calculating a date 2 months minus one day in the future.

Bonus! What's the date of the 250th day of the year?  =DATE(2015,1,250) or 9/7/2015.  (I guess that would also be January 250th, 2015!)

 

What about next month?

Just a slightly different formula.

For the start date: =DATE(YEAR(TheDate),MONTH(TheDate)-1,"1")

For the end date: =DATE(YEAR(TheDate),MONTH(TheDate),0)

 

What an easy way to create these?

Fire up Excel! (And see this article: http://techtrainingnotes.blogspot.com/2010/08/sharepoint-creating-calculated-column.html)

Have fun!

 

.

SharePoint 2013 Query Suggestion Files

 

Over the last three months I have been working on a Search Administration class that has a focus on improving the end user search experience. If you have attended one of my governance classes or consulting sessions then you have heard me preach on the need for a "Search Administrator". If you are interested in this area of administration then check out my new class: Microsoft SharePoint 2013 Search Administration.

I have been experimenting with SharePoint 2013 search Query Suggestions and found that there are some interesting aspects of the import files that don't seem to be documented anywhere.

• One phrase per line.
• The “Always Suggest” file does not need quotes, but they are acceptable.
• The “Never Suggest” file must have quotes around multiword phrases.
• Files can have blank lines.
• Files with duplicates cannot be uploaded.
• The order of the phrases is not important. The phrases will be sorted in the dropdown. For that matter, if you import the file and then export it, the items are in alphabetical order.
• It's a good idea to keep your own backup of the files. Do not depend on the export as the order has been changed. You may also want to keep a "documented" version of the file with some comments. (Remember to remove the comments before uploading!)

I tried most of the ways to add comments, but none of them worked. You may want to keep two copies of your suggestion files, one with comments and one without.

Example of an Always Suggest file:

Example of a Never Suggest file:

In this example we are hiding common misspellings learned by search's automatic query suggestions. We are also dealing with the fact we don’t have a "Cincinnati region". It’s really the South Western Ohio Region.

 

Tip: If you are working with an on premises SharePoint 2013 then you could add a thesaurus entry to expand the user's search for "Cincinnati Region" to include "South Western Ohio Region". 

.

Add JavaScript to Quick Launch in a 2010 Publishing Site

 

Note: The following will work in SharePoint 2010. For SharePoint 2013 you will need to use PowerShell. See here: http://techtrainingnotes.blogspot.com/2015/02/working-with-quick-launch-from.html

 

When you attempt to add a link that does not look like a URL to a SharePoint 2010 Publishing site you will get an error message like this one:

   

The example above works perfectly with a non-Publishing site. What's different? The dialog box being used to do the edit.

The Work Around?

Cheat! Use the old editor for Quick Launch! (This will not work in SharePoint 2013.)

Go to your publishing site and then edit the URL in the browser's address bar so it points to _layouts/quiklnch.aspx

Something like http://yourServer/sites/yourPublishingSite/_layouts/quiklnch.asp

Click New Navigation Link or New Heading and add your JavaScript!

   

And there you go!

For a SharePoint 2013 workaround using PowerShell see:

 

.

Working with Quick Launch from PowerShell

 

The following will work with both SharePoint 2010 and 2013. It will also work with SharePoint 2007 if you "manually" create the SPWeb object.

The basic steps to add a new link to Quick Launch:

1. Get the SPWeb object for your site.
2. Get the SPWeb.Navigation.QuickLaunch object.
3. Create a new node .
4. Add the new node to an existing node (like "Lists" or "Libraries") or the root of Quick Launch.
5. Done… No need to call Update() on anything!

Note: This will work with publishing sites as long as you are not using Managed Metadata Navigation.

 

Get your web object and the QuickLaunch object:

$web = Get-SPWeb http://yourServer/sites/yourSite
$quicklaunch = $web.Navigation.QuickLaunch

You can explore your existing Quick Launch from here. Just type $quicklaunch and press enter to see your headings / top level nodes. Type $quicklaunch | where {$_.title -eq "Lists"} to see the links in one of the headings. You could even list all of the headings and their children with this one: $quicklaunch | select -ExpandProperty Children | Select {$_.Parent.Title}, {$_.Title}.

 

Create a new node object:

Each item added to Quick Launch is an SPNavigationNode object. It has three parameters: the text to display, the URL to the linked resource and $true if the link is external to SharePoint or $false if the link is internal to SharePoint. It appears that the $true/$false in the third parameter is used to see if the URL is validated as a real SharePoint URL or not. I could still add SharePoint links by leaving it as $true.

Example for a SharePoint link: (note the $false)

$navnode = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("Get help!", "/sites/helpsite", $false)

Example for Bing or link external to SharePoint:

$navnode = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("Bing", http://www.bing.com, $true)

Example for JavaScript:

$navnode = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("Say Hello!", "javascript:alert('hello')", $true)

Example for JavaScript to open a new dialog box:
(Note the "`" in front of the "$" is needed because "$" means something special in PowerShell. (a variable follows))

$navnode = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("Add a new task", "JavaScript:var options=SP.UI.`$create_DialogOptions();options.url='/sites/Publishing/Lists/Tasks/NewForm.aspx?RootFolder=&IsDlg=1';options.height = 400;void(SP.UI.ModalDialog.showModalDialog(options))", $true)

For more on JavaScript in Quick Launch see SharePoint: JavaScript in Quick Launch and Top Link Bar! and SharePoint: Opening a 2010 Dialog Box from Quick Launch.

 

Add the node to Quick Launch

To add the new node as a "heading" just add it to the Quick Launch object:

$quicklaunch.AddAsFirst($navnode)             or .AddAsLast

To add the new node as a child of a "heading" then use Where to find that heading:

$heading = $quicklaunch | where {$_.title -eq "Lists"}
$heading.Children.AddAsLast($navnode)

To add the new node after an existing node (i.e. not as First or Last) you will need to retrieve the existing node and then use the .Add method.

$existingLink = $heading.Children | where {$_.title -eq "Search the web with Bing"}

$navnode = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("Search the web with Google", "http://www.google.com", $true)

$heading.Children.Add($navnode,$existingLink)

 

Delete a link?

Sure… Just retrieve the node and then call Delete.

$heading | Select -ExpandProperty Children | where { $_.Title -eq "Tasks" } | ForEach { $_.Delete() }

If you know that there's exactly one node that matches the Where test then you can shorten it to this:

($heading | Select -ExpandProperty Children | where { $_.Title -eq "Goog
le" }).Delete()

 

Bulk updates?

Sure… with extreme caution!  Danger! Will Robinson! Danger! Do the following at your own risk. No liability assumed, batteries not included!

Here's an example to add a Help link to every site in a site collection or to all site collections in the entire farm. (You way want to filter out the My Sites and the publishing sites!)

First confirm what you are about to change!

Get-SPSite http://sharepoint/sites/training | Get-SPWeb -Limit All | Select URL, Title
   or
Get-SPSite -Limit All | Get-SPWeb -Limit All | Select URL, Title

Create the new link / node.

$navnode = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("HELP!", "http://yourServer/sites/yourHelpSite", $true)

Now add it to every site in a site collection!

Get-SPSite http://sharepoint/sites/training | Get-SPWeb -Limit All | ForEach { $_.Navigation.QuickLaunch.AddAsFirst($navnode) }

or for all site collections in the farm:

Get-SPSite -Limit All | Get-SPWeb -Limit All | ForEach { $_.Navigation.QuickLaunch.AddAsFirst($navnode) }

 

And if you want to delete all of those helpful links?

Get-SPSite http://sharepoint/sites/training | Get-SPWeb -Limit All | ForEach { ($_.Navigation.QuickLaunch | where {$_.Title -eq "Help!"}).Delete()  }

 

What about a Publishing Site?

Publishing Sites use the "Navigation" editor for links and it does not permit "URLs" that don't begin with "http". This means that you cannot add JavaScript to a Quick Launch link using that editor. For SharePoint 2010 you can use the workaround here, or for 2010 and 2013 you can use the PowerShell above. (PowerShell to the rescue!) For more on JavaScript in Quick Launch see SharePoint: JavaScript in Quick Launch and Top Link Bar! and SharePoint: Opening a 2010 Dialog Box from Quick Launch.

The PowerShell above will work as long as in your Navigation settings you have not selected "Managed Navigation" or "same as parent".

 

References:

MSDN's article showing C# examples: (This is for 2010 but applies to 2007-2013.)

https://msdn.microsoft.com/en-us/library/office/ms427791(v=office.14).aspx