Uncategorized

The Power of SharePoint Lists, Flows and PowerApp

 

Atul Gawande’s book The Checklist Manifesto talks about the power of checklists. Gawande discusses the use of checklists to reduce failures caused when we do things without making use of the things we know. The point being, that in an increasing complex world, mistakes are inevitable but the risk of mistakes can be reduced by using checklists.

Every workplace, both digital and non-digital can benefit from checklists to help ensure people are following the agreed process for frequent and infrequent tasks. Everything from apply for leave, through to landing an airliner in an emergency or carrying out surgery will benefit from checklists. No more winging it!

If you have Office 365, you have a great set of tools for building digital checklists without code. In this blog, we will discuss how to build a simple checklist solution using:

  • SharePoint: create lists with various fields to store and track information
  • PowerApps: build forms based apps for web and mobile users
  • Flow: build workflows and integrate various Office 365 tools

Example:

The best way to demonstrate the capabilities of these tools is to use an example. Consider a contractor on-boarding process. When a new contractor is registered to work onsite, they are added into Dynamics CRM and tagged as a contractor, someone checks they have the relevant certifications, a health and safety form is sent to the contractor and once a signed copy is received the contractor is authorised to be onsite and an ID card is issued.

Step 1: Planning and Design

Find a whiteboard and draw an outline of the system you want to build. Use the following checklist to capture the details needed to build the system:

  • The information you want to capture on the form
  • Details of any reporting requirements (check that you capture the information needed)
  • Workflow steps
  • Who can use the system
  • Any special permissions or security around the data

Step 2: Create a list

The SharePoint list will hold the information captured when the checklist is createdList-Settings

In SharePoint Online, create a new list (Modern lists are needed for Flow and PowerApps) and add columns to capture the values you want to track.

List-Checkbox.PNG

In addition to Yes/No checkboxes, you can add choice lists, text, number and date columns. There are currently some limitations with PowerApps when using some types of fields such as People fields and Manage Metadata.

Step 3: Create a Flow

Flows can be initiated when an item is added or updated in a SharePoint list.

Flows can also be started when something happens in another system that supports Flow e.g. When a new contact is added in Dynamics 365 (CRM), create a new item in the Checklist (SharePoint list) and notify customer support.

Create-Flow

Flow-CRM-SharePoint

Step 4: Build a form with PowerApps

PowerApps can be built from a SharePoint lists allowing users to view and update list items from mobile devices or web browser.

Create-PowerApp

From the list created in step 2, choose “PowerApps” and create PowerApp. Office 365 will automatically create a PowerApp using the list columns with Search, View and Edit forms. The PowerApp can be accessed either in the browser or using the PowerApps mobile App (iOS or Android).

PowerApp-InitialScreen

Inspiration

SharePoint list based systems are quick and easy to build, making them very popular for all sorts of uses. If you’re looking for a bit of inspiration, I can recommend reading The Checklist Manifesto.  http://atulgawande.com/book/the-checklist-manifesto/

 

 

 

 

 

 

Changing SharePoint Database Servers

How do you upgrade your SQL Server that is hosting SharePoint databases?

A common scenario is a SharePoint 2010 farm using SQL 2008 R2 or SQL 2012. Their is requirement to upgrade to a newer version of SQL Server.

There are a few questions you need to answer before doing the upgrade:

  • What SharePoint build are you running? (SharePoint Build Numbers)
  • What version and edition of SQL are you currently running? (SQLserver Build Numbers)
  • What build number is supported by the new SQL Server version?
  • Are you running SSRS integrated mode?
  • How big are your SharePoint databases?
  • What name will the new server have?

The process for moving to a new SQL server has the following steps:

  1. Install SharePoint cumulative updates
  2. Install SQL Server
  3. Make the SharePoint databases read only on your old server
  4. Backup databases on the old server
  5. Restore to the new SQL Server
  6. Change the databases to writable on the new SQL server
  7. Update the SQL Alias on the SharePoint servers (see below)
  8. IIS Reset on all SharePoint hosts

Configuring a SQL Alias

On the SharePoint Servers running a Command prompt (CMD) as Administrator

Run CLICONFG.EXE

If there is an existing SQL Alias, simply change the “Server Name” in the connection parameters to the new server.

If no SQL Alias exists, the you can create an Alias using the Old SQL Servers name and point it to the new server.

SQLAlias

If the existing SQL Server is going to remain online for a period of time, you can also add an entry into the Windows HOSTS file, with the new SQLserver IP address and the old SQLserver name (Windows uses the hosts file to resolve the name before checking DNS).

NOTE: Other services accessing SQLserver from the SharePoint servers will also use the Alias.

 

 

 

 

 

Do we still need Structure in SharePoint?

I love Delve, it’s a great way of quickly accessing documents I’ve been working on recently and is particularly useful in when collaborating with people across our business. It is particularly useful when someone moves on and you need to find that document they were recently working on.

I’ve had conversations with a wide range of people over the past few days about the use of Delve and where you still need to structure content to find it in traditional ways. In my opinion structure is still very important for several reasons:

  1. Compliance – documents that must be keep for compliance reasons benefit from structured storage. It allows content to be easily identified, grouped and makes it easier to apply policies.
  2. Archive (High value) –  where you have high value content, the ability to classify the documents, track the approval history and ensure you know which version is authoritative can be important.
  3. Security – structure makes security easier to apply, maintain and audit.
  4. Third-party integration.

You may also have documents that don’t have these requirements. It is still important to think about the life-cycle, particularly what happens if the owner of the documents leaves the organisation and how do I find documents that have been cold for a longer time period but still have value.

Why don’t I just store everything in OneDrive for Business? For all the reason listed above and because we want to ensure the documents are retained over the long term.

Is traditional Site Structure and Search dead? Delve improves user experience and making content more discoverable but it doesn’t suit every use case. Delve also helps address issues such as content stored in Office 365 Groups, OneDrive for Business and other services that work with Office Graph.

What are your thoughts on Delve and SharePoint Structure?

Data Channel : Managing SharePoint Databases

I was recently interviewed for the Data Channel about managing SharePoint databases on SQL Server.

In the interview with Nagaraj Venkatesan (SQL Server MVP from Singapore) I cover many of the common questions DBA’s have about SharePoint including:

  • Things that make SharePoint databases unique
  • Capacity Planning
  • Remote Blob Storage (RBS)
  • Backup / Recovery
  • SSRS Integration
  • Patching

You can download a copy of my presentation from #SQLsat614 which covers the basics for DBA’s and is also useful for SharePoint Admins.

Thanks to The SQL Saturday team for giving me the opportunity to present and Nagaraj for the interview.

 

 

Why is Office 365 going slow?

Is your Office 365 running slow at random times? Does it seem to happen at work but be fine from home (or some other location)? Here is a short check list to help diagnose the problem.

Where is your Office 365 Tenant located?

Make sure your tenant is hosted in a location that makes sense. For us New Zealanders, the nearest location is Australia. Check the Office 365 Datacenter map.

Are you behind a Firewall or Proxy Server?

All Office 365 services use SSL. Firewalls and Proxy server with SSL Packet Inspection enabled can be a source of latency, especially if they are under a heavy load. Does turning off packet inspection improve performance? Does the Firewall’s console show high memory or CPU usage? This article ‘Should you use SSL Inspection’ by Forinet is a good read and applies to other vendors too.

Check your international bandwidth

In New Zealand some ISP’s limit the amount of international bandwidth allocated to each customer. If you have a large number of users, this could be a bottleneck. Talk to your ISP about the bandwidth allocation. Some may also have Office 365 specific plans.

Express Route is another technology that can improve performance for Azure and Office 365. See Microsoft’s Express Route partners and peering locations document. Talk to your ISP about Express Route.

Are you connecting across a WAN to your company internet connection?

If you are working from a branch office, then your internet traffic may be passing over a WAN link before getting to the internet. How much bandwidth do you have and are you sharing it with other traffic? Are you slowing down when someone prints a big file?

Other things to check

  • Network switches on your local LAN. Look for packet loss, latency and retry errors
  • Wireless network. Is the issue related to your WiFi only?
  • Your PC (or Mac). Is the problem specific to one device?
  • Run a speed test on your internet connection.
  • Are you over your data cap?

For a details example of how to troubleshoot Office 365 performance, read this article from Microsoft Premier Support.

There are other reasons Office 365 might be running slow, but in my experience most issues relate to the environment users are in. Try to eliminate the easiest things first.

Surface Pro alternative keyboard

I was first in line to get a Surface Pro 3 when they first came out. It’s a great piece of hardware with good performance, a great screen, plenty battery capacity and will run all the applications I need day to day. Microsoft improved many of the earlier Surface devices weaknesses and it became a popular device.

BrydgeKeyboard

I spend a lot of time away from my desk and out of the office. I take my Surface with me everywhere. The form factor combined with kick stand is great for working on planes, in cafe’s or in my car, but it does have some short comings. Sometimes I want to work from a couch or chair without a nearby hard surface to put the Surface on. The standard keyboard lacks the rigidity needed to make that type of working, leaving you thinking I really do need a laptop at times.

After considerable use for the past couple of years my keyboard was starting to wear out and so starting looking at the alternatives. Do I get the Surface Pro 4 keyboard (compatible with the Surface Pro 3)? What alternatives are there?  I stumbled across Brydge Keyboards, who make alternative keyboards for all your favourite tablets.

The Brydge 12.3 (https://www.brydgekeyboards.com/products/brydge-12-3) is not only an alternative keyboard, it also removes the need to use the Surface’s Kick Stand. You can use your Surface like a normal laptop, but without splashing out for the more expensive Surface Book. Brydge also offers a version with storage expansion.

They keyboard is made from aluminium, weights in at 675grams, has a back light, connects with bluetooth and runs for 3 months between charges. This combined with great reviews on several major websites, means it is seriously worth investigating further!

The release date is May 2017. You can pre-order now.

Update: I’ve got one!

I’ve had the keyboard for just over a week and haven’t once wanted to switch back to the Type Cover. I’ve used it at my desk, onsite with clients, in a car, on two flights and at a conference.  The typing experience is considerably better than the Type Cover and I have found myself using the Surface Pro more as my primary device.

The track pad is smaller than I was expecting, but is usable and I think better than the SurfacePro3 track pad. It is entirely usable but I do find myself using an external mouse if I have a desk to work at.

Switching to flight mode on a plane and then remember to activate Bluetooth was a little trap. I was a little tired the first time (6am flight) it did take me a moment or two to realise why the keyboard wasn’t working.

Judging from the reaction I’ve had when showing the Brydge 12.3 to other Surface Pro users, I think it is a much welcomed alternative to the Type Cover options, primarily because it makes the Surface more laptop like and it improves the typing experience.

My only real gripe is the weight of the keyboard which adds 675grams to the total weight. You do however get a feeling of quality and after a few days this has become less of an issue than it was initially.

Overall rating is 8/10.

PowerShell:Bulk load files into SharePoints

Here is a script I wrote to bulk upload files and metadata into SharePoint. To make this work you need two things, a CSV file containing the names of the files to upload and the metadata associated with the item.

In this example, the CSV file has the following format:

  • filename,cust_number,document_type

The script reads the CSV file, creates a folder in the document library named with the value of the “cust_number” field, and then uploads the file “filename” and populates the “document_type” column.

The WebClient command is used to upload the file into the document library. The script also checks the item in (if required).

Write-Progress -Activity “Connecting to SharePoint Site,” -Status “Please wait …”
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$CSVFile = “C:\FilesToImport\filelist.csv”

$SPWebURL = “http://sharepoint/site”
$SPListURL = “http://sharepoint/site/library/”
$BaseFolder = “C:\FilesToImport\Files”
$Credentials = [System.Net.CredentialCache]::DefaultCredentials

$SPWebObject = Get-SPWeb $SPWebURL
write-host $SPListURL
$SPListObject = $SPWebObject.GetListFromUrl(“library/Forms/AllItems.aspx”)
$WebClient = New-Object System.Net.WebClient
$WebClient.Credentials = $Credentials

Write-Progress -Activity “Importing CSV File,” -Status “Please wait …”

$CSVObject = Import-CSV $CSVFile
$Index = 0
$Max = $CSVObject.Count

ForEach($CSVItem in $CSVObject)
{
$Index++
Write-Progress -Activity “Updating Metadata” -Status “Processing Item $Index of $Max”

$FileName = $CSVItem.File_name + “.pdf”
$ID_Number = $CSVItem.Cust_Number
$DocumentType = $CSVItem.Document_Type

$FullFileName = $BaseFolder + “\” + $FileName
write-host $FullFileName
if (Test-Path ($FullFileName))
{
$UploadPath = $SPListUrl + “/” + $Cust_Number + “/” + $FileName
$WebClient.UploadFile($UploadPath, “PUT”, $FullFileName)$SPListItemsObject = $SPListObject.Items | where {$_[‘Name’] -eq $FileName}
ForEach($SPListItem in $SPListItemsObject)
{
$SPListItem[‘Document_Type’] = $DocumentType

$SPListItem.Update()
if ($SPListItem.file.CheckOutStatus -ne “None”)
{
$SPListItem.file.CheckIn(“”)
}
}
}
else
{
Add-Content ErrorLog.txt $FullFileName
}
}

I’ve used this script in a few scenarios. I hope you find it useful too.