PowerApps Job Tracker Demo

Microsoft PowerApps for Office 365 allows you to build your own apps without needing to be a developer. It is particularly useful for building solutions that use SharePoint as a backend. Mobile users can interact with and update information from SharePoint Lists via PowerApps.

PowerApps are cross platform working on Windows, iOS and Android devices, simply download the PowerApps App from the Apple AppStore or Google Play.

Here’s a short video I made that shows some of the basic features of PowerApps for SharePoint. In SharePoint I have created two lists, one for Clients and one for Jobs. The Jobs list has a looking on the Clients list, creating a one to many relationship between Clients and Jobs. This PowerApp allows users to search for clients and then select jobs related to the client with view and edit functionality.

The only trick part of the app was linking Galleries together so that clicking on a Client filters the Jobs list based on the SharePoint Lookup Column. SharePoint stores the ID of the list item from the Lookup list, so I needed to use this formula in PowerApps:

Filter(Jobs,Client.Id=ClientGallery.Selected.ID)

In the video, I show the functionality and how to link the various screens and galleries together.

I’m sure you can think of lots of ways to improve this basic PowerApp but hopefully this gives you a good idea of how easy it is to build your own applications.

Learn more about PowerApps

Advertisements

Upgrading to SharePoint 2016 : 101

How do we upgrade to SharePoint 2016? This is a question I’ve been asked a lot lately.

Before I answer the question, I usually start by asking one of my own. Have you considered moving to SharePoint Online? Some people have a very good reason for choosing to stay on-premises but many don’t. Let’s consider both scenarios.

Scenario 1: Staying on-premises

The simplest option is a Content Database migration. This is the same tried and tested method used to upgrade from older versions of SharePoint e.g. SP2010 to SP2013. If you are moving from SP2010, you will need to do an interim upgrade to SP2013 first, just for the content database upgrades.

Steps:

  1. Install a new SharePoint 2016 Farm. If you need high-availability or want to take advantage of mini-roles to reduce downtime during patching, you’ll need a minimum of 4 SharePoint servers. If that isn’t needed then a single server farm is possible, but do your homework before going down this path.
  2. You may also need to upgrade your SQL Server depending on the version.
  3. Once installed, create a new Web Application
  4. Restore the Content Database(s) from the SP2013 farm to the new SQL Server
  5. Install any third-party solutions. Mega Menus, Workflow tools, Custom web parts etc
  6. In Central Admin, attach the new database to your new SP2016 web application. SharePoint will automatically upgrade the database schema during this process, which can take time, especially if the database is big.
  7. IISRESET
  8. Test everything

You may decide that an in-place upgrade isn’t practical or possible. In this case, you can setup a new farm and then use a migration tool (DocAve, MetaLogix, ShareGate etc) to move the content across. This can be a time consuming process but is worth consideration if you need to restructure content or if you have a lot of customisation that you don’t want to bring across as part of the upgrade.

Scenario 2: Moving to SharePoint Online

Moving to SharePoint Online often requires more planning upfront. There are some things you can do in SharePoint server, that can’t be done online or require a rethink. Here’s a short list of common differences, but there are others that may apply too:

–          Server side solutions cannot be deployed to the cloud

–          Site Collections can’t use explicit paths (URL’s to sites may change)

–          You cannot change the URL from https://mytenantname.sharepoint.com

–          User Profile Sync back to Active Directory is not supported

–          SQL Server Reporting Services integration is not supported

–          Email enabled document libraries are not supported

–          Many third-party mega menus aren’t supported (yet)

–          Integration with other systems may need to be updated

This is by no-means a full list, but it does give you an idea of where pain could start.

You will need to develop a strategy for migrating content across.

–          What content are you migrating?

–          How much content is there?

–          What tool are you going to use?

I highly recommend using a migration tool such as Metalogix, ShareGate or DocAve. Unless you have a trivial amount of content, these tools will save you time. They can map metadata from your old site to the new one or copy entire sites and site collections across. All of these tools handle version history and system metadata such as created date and created by.

Steps:

  1. Identify what you will be migrating and determine if it includes features that may not be supported. Workarounds or alternative solutions may be needed to address those issues.
  2. Ensure Azure AD Connect is setup and syncing users and groups
  3. I recommend that you move Exchange across before SharePoint if possible. There are some things in the Delve profiles which work better
  4. Setup you SharePoint Online tenant
  5. Create site collections
  6. Use a migration tool to copy over the sites, lists and libraries from on-premises.
  7. Setup navigation
  8. Check site security

You can do this process in stages e.g. pre-copy the bulk of the content and then migrate over the changes before you ‘go live’.

I should stress that in many cases you will have other challenges to address as you migrate sites across. Give yourself time to test and find solutions for those things that don’t migrate nicely.

Moving to SharePoint Online will give you many advantages over the long term and reduce the amount of infrastructure needed for your SharePoint farm. For many of us the chances are you will move to the cloud eventually anyway, so why delay?

If you have any good tips, please share in the comments below.

SharePoint and SSRS: The given key was not present in the dictionary

Consider this scenario. You have SharePoint with SQL Reporting Services (SSRS) running in SharePoint Integrated mode. You want to use a SharePoint list as a data source for a report.

In the SSRS Data Connection you enter the URL and credentials for the SharePoint site but when you test the connection the following error appears:

The given key was not present in the dictionary

After a bit of trial and error I discovered that this error occurs if the URL entered isn’t the default URL for the web application.

To resolve the issue, go into Central Admin and check the Alternative Access mappings on the SharePoint site used in the data source. Check that you are using the default URL.

Similar but unrelated issue

Note that there is a similar issue that occurs when added managed accounts in Central Admin. These issues are not related. Further details of that issue can be found here: https://support.microsoft.com/en-nz/help/2463865/sharepoint-2010-error-message-the-given-key-was-not-present-in-the-dic

 

 

Changing SSRS database names

I’ve been working on a project where multiple servers including two SharePoint farms running SQL Server Reporting Services (SSRS) in SharePoint Integrated mode need to be moved to a single SQL Server. Each farm has its own SQL Server to host the SharePoint and SSRS databases. To make the job a little more challenging the databases in each farm have the same name.

To complete this job I need to change the names of the databases to avoid duplicate database names on the new SQL Server. Here’s the approach I have taken and few tips to resolve issues caused by the renaming process.

Before attempting the following process, make sure you have a backup.

SQL Reporting Services has two databases:

  • ReportingServerDatabase
  • ReportingServerDatabaseTemp

Backup / Restore :

  1. Backup and restored the databases to the new server, renaming the databases in the process. In this case I added a prefix to distinguish between the two SharePoint farms.
  2. On the SharePoint Server run the SSRS Configuration Tool
  3. Change the database to the new database server name. Note that you don’t get the opportunity to change the Temp database name only the reporting server database.

Update the references to the Temp database:

  1. In SQL Management Studio, locate the ReportingServerDatabase. There are references to the Temp database in several views and many stored procedures (83 in my case).
  2. Generate a script for the Views in the database (Drop and Create)
  3. Generate a script for the Stored Procedures in the database (Drop and Create)
  4. In both scripts search and replace the old Temp database name with the new one
  5. Stop the SSRS Services on the SharePoint Server (in the SSRS Configuration tool)
  6. Run the updated View script
  7. Run the updated Stored Procedures script
  8. Restart the SSRS service

Testing:

  1. In SharePoint navigate to the SSRS Reporting library (in my case called ReportServer)
  2. Edit a Data Connection
  3. View a report

If you get an error in either of these functions, you should see the name of the Stored Procedure and database it is referencing. Go back into SQL Management Studio and check the references have been updated.

 

 

 

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.

 

 

 

 

 

Intro to PowerApps and Flow

I was lucky enough to get the opportunity to speak at the Digital Workplace Conference 2017 in Auckland on the topic of PowerApps and Flow.

The Digital Workplace Conference covers a full range of topics related to Office 365 and still has a strong emphasis on SharePoint. Topics cover a good mix of technical, thought leadership and customer case studies. I highly recommend this conference for anyone in the intranet, knowledge management, collaboration and SharePoint space.

Microsoft are supporting InfoPath and SharePoint Designer through to 2026, but aren’t adding new features to these tools. The future isn’t a like for like replacement, so it is important to pay attention to what is happening with the new tools.

Here a simple example using a SharePoint list as the starting point to build a basic PowerApp.

Presentation Agenda:

  • Overview of Flow
  • Security and Policies
  • Flow Demo
  • Overview of PowerApps
  • PowerApps Demo
  • Advanced features
  • Coming soon

The demo covered a number of simple but useful scenarios for using Flow and PowerApps and covered off some of the challenges and limitations. Both services are developing rapidly, so keep an eye on the roadmap.

Download Presentation

Thank you to everyone who attended my talk.