Last week I blogged about the Date Time tips with reference to SharePoint and PowerShell – Refer here. In this blog post let me show case how to implement in SharePoint practically.
Create a List and name as you wish and in my case it’s “Employee Feedback”! Create two columns and name it as “WeekStart” and “WeekEnd“. The columns I have is shown below!
Here is the exact formulas. (Weekdays Wednesday through Tuesday)
=Created-(WEEKDAY(Created)+IF(WEEKDAY(Created)<4,-3,4)-1)
=Created-(WEEKDAY(Created)+IF(WEEKDAY(Created)<4,-3,4)-1) + 6
With no wait let us see the result
Here comes another requirement for which we need to put a small effort! Feedback is a Choice column in SharePoint list which has Good, Bad and Neutral as values! We need to create a SharePoint view which shows 2 Good and 2 Bad per employee entries for the current week (Wednesday till Tuesday) .Yes, we don’t need to work hard on date time because we have Week Start and Week End columns as our saviour.
Simple logic is to add a group filter for Feedback and Created By Column and set the item limit for view.
Filter
In my machine the Start Week Day is Monday till Friday! So Manager View turns empty. That’s obvious. So, let me spin PowerShell to change my Created At value to AUG 15, 2018.
Note: DO NOT ENTERTAIN MANIPULATING CREATED or MODIFIED FIELDS IN PRODUCTION BOX – YOU MAY VIOLATE PROCESS. USE THE BELOW CODE AT YOUR OWN RISK
Update List Item
Import-Module ".\assemblies\Microsoft.SharePoint.Client.dll" Import-Module ".\assemblies\Microsoft.SharePoint.Client.Runtime.dll" $UserName = "SuperAdmin" $Password = "SuperPassword" | ConvertTo-SecureString -AsPlainText -Force $Credential = New-Object pscredential -ArgumentList ($UserName , $Password) $Url = "https://<tenant>.sharepoint.com" $SPOClientContext = [Microsoft.SharePoint.Client.ClientContext]::new($Url) $SPOClientContext.Credentials = [Microsoft.SharePoint.Client.SharePointOnlineCredentials]::new($Credential.UserName,$Credential.Password) $ListItem = $SPOClientContext.Web.Lists.GetByTitle('Employee Feedback').GetItemById(1) $ListItem["Created"] = (Get-Date).AddDays(2) $ListItem.Update() $SPOClientContext.Load($ListItem) $SPOClientContext.ExecuteQuery() $SPOClientContext.Dispose()
See the end result – Manager View
Can we hide the Show All on the web page? Let me think about it and blog if possible! Cheers and Enjoy PowerShell and SharePoint.