SharePoint 2013 allows users to rate individual items in a list or library. This rating can be in the form of a “star” rating from 0 to 5 or in the form of “likes”. This is disabled by default for a list or library.
The rating data is stored in 5 columns which are added to your list or library when you enable this functionality:
- Number of Ratings
- Number of Likes
- Rating (0-5)
- Rated By
- Liked By
The columns “Number of Ratings”, “Number of Likes” and “Rating (0-5)” are visible to the end user. The other 2 columns “Rated By” and “Liked By” are hidden.
This information is transformed by SharePoint in a nice view which makes it somewhat sexier for the end user. You can see this in the screenshot below where the rating information is visualized in the “Favorited” column of my library.
The user will see a “Like” or “Unlike” link. At the same time, the number of likes is displayed together with a smiley.
So far so good.
Imagine you want to use this rating information to filter the list or library to show you only those items which have been liked or rated by you.
The columns you need for this information is the “Liked By” and “Rated By” columns. These columns are collections of users. The problem with this is that those 2 fields are not available for filtering when you use the UI.
You can use CAML to create a query which returns you only those items where the current user is contained in one of these fields.
<FieldRef Name='LikedBy' />
The query above returns items which are liked by the user who executes the query.
When you look at the SPViewCollection.Add method, you notice that it accepts a “query” parameter.
So, to have a view filtered using the query, we can simply create the view using PowerShell.
$web = get-spweb http://teamsites.westeros.local/
$list = $web.Lists["Reports"]
$query = "<Where><Eq><FieldRef Name='LikedBy' /><Value Type='Integer'><UserID /></Value></Eq></Where>"
$fields = $list.Views["All Documents"].ViewFields.ToStringCollection()
$viewName = "My Items"
$paged = $true
$queryLimit = 100
$defaultView = $false
$type = [Microsoft.SharePoint.SPViewCollection+SPViewType]::Html
$personalView = $false
$list.Views.Add($viewName, $fields, $query, $queryLimit, $paged, $defaultView, $type, $personalView)
This works… on premise.
But what if you have a SharePoint Online. You can still use PowerShell but you need a slightly different approach because instead of using the server object model of SharePoint, you need to use the client object model. You can use something like below to do the same on SharePoint Online. I must admit, I’m not an expert on CSOM with PowerShell (yet ) and I’m still getting up to speed with this. I suppose the code below can be simplified. But hey… it gives you an idea on the general approach what you need to do to create a view with a query on a library which is hosted on a SharePoint Online .
# Load CSOM assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
# Get credentials
$UserName = Read-Host -Prompt "Please enter your O365 Username (ex. firstname.lastname@example.org)"
$Password = Read-Host -Prompt "Please enter your O365 Password" –AsSecureString
$Site = "https://westeros.sharepoint.com/sites/Winterfell"
$ClientContext = New-Object Microsoft.SharePoint.Client.ClientContext($Site)
$ClientContext.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$Password)
$web = $ClientContext.Web
$lists = $web.Lists
$list = $lists.GetByTitle("Shared Documents")
if ($list -ne $null)
$views = $list.Views
$view = $list.DefaultView
$viewFields = $view.ViewFields
$viewCreationInfo = New-Object Microsoft.SharePoint.Client.ViewCreationInformation
$viewCreationInfo.Paged = $true
$viewCreationInfo.PersonalView = $false
$viewCreationInfo.RowLimit = 100
$viewCreationInfo.SetAsDefaultView = $false
$viewCreationInfo.Title = "MyDocuments"
$viewCreationInfo.ViewTypeKind = [Microsoft.SharePoint.Client.ViewType]::Html
$viewCreationInfo.Query = "<Where><Eq><FieldRef Name='LikedBy' /><Value Type='Integer'><UserID /></Value></Eq></Where>"
$viewCreationInfo.ViewFields = $viewFields