Fun with PowerApps part 2: creating a new data connection

This is part two in my fun with PowerApps series where I’ll go through my personal experience of creating a brand new PowerApp from scratch and go through step-by-step for each part of the process…

You can read part 1 of this series below:

Part 1: setting up the default gateway

Continuing on from my previous post – setting up the default gateway, we should now be in a position to create a new data connection within PowerApps. On the surface this is pretty easy to do, all that’s needed is to choose the connection type – press create – authenticate – job done!

However, when we need to create a data connection for an on premise SharePoint environment, its a little bit of a different story. Here are the steps involved for setting up a new data connection for a SharePoint on premise environment:

  1. Go to web.powerapps.com
  2. Under Connections – press New Connection

  3. In the search bar, search for “SharePoint” – press the plus icon

  4. The SharePoint data connection popup box should appear, under “how do you want to connect to your data?” select Connect using on-premises data gateway
  5. Set the authentication type to Windows and then specify your username and password (you may need to include a domain name as part of the username)
  6. I used credentials of a user that had owner permissions for the SharePoint environment I connected to, it makes sense to me that the account you specify here would at the very least need to be able to read/write to the SharePoint list that acts as the data source.

  7. Choose the gateway we set up in part 1 of this series, if you can’t see it you might need to install it again or refresh the list until it displays.
  8. I actually had to create the default gateway using a different account than the one I created my first PowerApp with, so it wouldn’t show for me initially. I had to share the gateway I created and set my PowerApps user as an admin for it to show in this list.

  9. Press Save

You will now see your newly created data connection appearing in the connections list! Your able to edit and delete the data connection at any point and can also check who the owner is, the connection name and crucially the status of the connection under the details button.

BONUS

There is also this article from Microsoft talking through connecting to SharePoint from PowerApps that lists the known issues with certain column types:

https://docs.microsoft.com/en-us/powerapps/connections/connection-sharepoint-online

Fun with PowerApps part 1: setting up the default gateway

I’ve recently been playing with PowerApps for the first time, I set myself a challenge of creating an IT service desk in PowerApps using a SharePoint 2016 on premise environment as the main data source.

So before I start going through all the steps I found were needed for me to setup the default gateway, this article from Microsoft is a great place to start and contains most of the steps I’ll go through below:

Setting up the default gateway

  1. Download the gateway installer here
  2. Run and begin the install of the default gateway, this can be installed anywhere that is able to connect to the server name provided. Press Next
  3. Specify the install location for the gateway and accept the terms of use. Press Install

  4. Enter the email address you wish to use with this gateway. On the next screen of the wizard, press Sign in


  5. Select Register a new gateway on this computer, press Next


  6. Give your new on-premises data gateway a name, enter a recovery key and confirm it. Press Configure


  7. In the PowerApps Studio, under Gateways you should now see your Gateway listed
NOTES

I found that the internal firewall where I had setup the gateway was blocking connections, in my case the system admin allowed these connections and it worked a treat!

I also found that the 365 account used to register the default gateway needed a Power BI license assigned to it to be able to connect to a SharePoint on premise data connection.

I’m planning on doing a series of posts over the next few weeks on my fun with PowerApps so part 2 will be coming soon!

Page declared a record or placed on hold and is read-only

If you’ve ever been in the situation where you try to edit a page in SharePoint and you see this seemingly unchangeable message appear at the top of the page stating:

“Page has been declared a record or placed on hold and is read-only”

Fear not! I’ve seen this message appear and it usually occurs when a user (or service account) is operating as a system account. This could have been set manually by said user if they have access to the web application via central admin:

If this is the case, you can overwrite the system account check in via SharePoint Designer. You’ll obviously need the correct permissions to access SPD beforehand! In the following example I’m using a SharePoint 2010 environment. To do this:

  1. Connect to your web application in SharePoint Designer
  2. Navigate to All Files – Pages
  3. Right-click on the page that is currently locked
  4. Undo checkout

NOTE: You won’t be able to view the individual pages within each Page Library if you navigate through Lists and Libraries. This space is used to view and manage the settings for each list type you have. All Files takes you to the ‘root’ of your web application, where you can see everything that sits under the web application.

SharePoint 2019 announced! plus even more from #msignite

As announced by Microsoft on day of of #msignite, SharePoint Server 2019 is set to be released in 2018…

It seems there are a raft of new features an improvements in the pipeline for SharePoint, here’s a quick look at what was announced:

  • Security and Goverance enhancements
  • Several new webparts
  • Improvements to team/group integration
  • Better mobile app!
  • Plethora of improvements to intranet sites
  • Overhaul of SharePoint lists
  • Search improvements and much much more…

There are a few good write ups already on this subject which cover the major changes in depth:

https://collab365.community/sharepoint-2019-announced-complete-round-ignite/

https://techcommunity.microsoft.com/t5/SharePoint-Blog/Connecting-the-modern-workplace-with-SharePoint-and-OneDrive/ba-p/110399

Exciting times!

Configuring outgoing email SharePoint 2016

One of the first things a user may ask their shiny, new SharePoint 2016 site collection to do is to have the ability to send email. Luckily, this is pretty easy to do and will just rely on you getting to grips with a few things about SMTP server:

  • SMTP server address
  • SMTP server port number

If you don’t know either of these, then it might be worth getting friendly with your system admins as they should be able to help! To start configuring outgoing email in SharePoint 2016, we need to set it up at the central admin level:

  • Navigate to central admin – system settings
  • Under Email and Text Messages – Configure outgoing email settings
  • In Outgoing email settings – enter your SMTP server information, from, reply-to address and press OK

Now your SharePoint environment should be configured to send outgoing email, next we will check that outgoing email is set up correctly at the web application level:

  • Navigate back to Central Admin – under Application Management – Manage web applications
  • Select the web application you wish to manage
  • Press the General Settings drop down – Outgoing email

Check that SMTP server details are correct (based on the farm level configuration we did earlier), you can also personalise the from and reply-to addresses for the web application if you want them to be different to the top-level.

BONUS

One issue I’ve encountered when configuring outgoing email in SharePoint 2016 is where lists or libraries aren’t sending email alerts. The initial email is sent when alerts are setup, but the subsequent email alerts don’t fire.

The issue quite a specific one but worth mentioning, as at first I applied this security update which included an update for outgoing email, but didn’t solve the problem. It turned out that in Exchange Server, if anonymous Relay settings isn’t enabled, then your SharePoint servers need to be added to the allowed hosts relay list. So back to my earlier point, if this issue affects you – make sure you take your system admin to lunch to pick his brain about exchange relays!

Updating manage access requests

As a SharePoint administrator, you should be fairly familiar with this error message:

If you’re not, it could mean that your SharePoint’s site access requests aren’t going to the correct email address…or you might just be ignoring them! In any case if you find that you need to manage where these site access requests go you can do.

When a SharePoint site collection is provisioned, site access requests are configured to be sent to the email address(s) specified at the point which the site was created, but a site administrator can change this for each site within the site collection they administer. By default, when a sub-site is created the same email address(s) that are configured on the parent site are used for access requests to the sub site.

Follow the steps below to change these settings:

In Site Actions – Site Permissions

Select Manage Access Requests from the ribbon

Specify the email address to send requests to and click OK. Note: you can add multiple email addresses here, just separate each address with a semicolon.

Note: With manage access requests configured users can click a link on the access denied page when they are unable to access content. If no email address has been configured for this site, the link will not appear on the access denied page.

Microsoft free eBook giveaway!

I recently received an email alert about a free eBook giveaway from Microsoft, at first I thought it was too good to be true – but it turns out it’s not!

There are literally dozens of resources available that arent just SharePoint, but there are quite a few good reads SharePoint-wise too…

https://blogs.msdn.microsoft.com/mssmallbiz/2017/07/11/largest-free-microsoft-ebook-giveaway-im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-office-365-office-2016-power-bi-azure-windows-8-1-office-2013-sharepo/

Conditionally show or hide fields within a custom list form based on user permissions

InfoPath, I really like InfoPath. I like the interface and how easy it makes editing custom list forms in SharePoint (especially if you want to make snazzy looking forms in SharePoint 2010). However, custom actions do not like InfoPath, not one bit.

The problem

Here’s the situation, we have a heavily customised custom SharePoint 2010 list which was leveraging InfoPath based forms. We were getting reports of latency when trying to open the InfoPath forms and the decision was made to revert back to the default ones.

Also, another requirement was to have a two edit forms for this list, one set as default with several fields omitted for end users – another one for administrators of the list. Once the edit forms were created I embarked on my journey of creating a custom action that would open the administrator’s edit form from the ribbon, utilising rights masks to make it only appear for those with adequate permissions.

I quickly found that this wasn’t going to work. I tried several times to create the custom action but it just wouldn’t appear. Even after reverting the list back to the default forms from InfoPath through list settings and deleting the InfoPath forms from server the custom action wouldn’t show. I found some useful conversations about this issue below:

After I realised the custom action approach wasn’t going to work, I made the decision to go down the route of having one custom edit form that would conditionally show or hide fields based on their permissions – the answer I found was within a XSLT conditional if test!

The solution

Here are the steps taken to hide a field based on a user’s permissions within a custom edit form:

  • Open the list you want to edit in SharePoint Designer
    SharePoint Designer homepage
  • In the Forms section, open your custom edit form
  • Switch to Advanced Mode
    advanced mode sharepoint 2010
  • Use the design view and select the field you wish to hide
    split view sharepoint 2010
  • In the code view, add the following code snippet above and below your field:

<xsl:if test="ddwrt:IfHasRights(2048)">
</xsl:if>

  • The end result should look something like this:
  • Save the changes

If this has worked, you should now be able to test the edit form as a user with the correct permissions and see the field, then verify that for a user without the relevant permissions it’s hidden.

Within the if test, the number corresponds to a permissions mask that assigns a particular value (i.e. 2048 = Manage Lists).

Here is a list of all the values and permissions masks:

PermissionPermission Mask
ViewListItems1
AddListItems2
EditListItems4
DeleteListItems8
ApproveItems16
OpenItems32
ViewVersions64
DeleteVersions128
CancelCheckout256
PersonalViews512
ManageLists2048
ViewFormPages4096
Open65536
ViewPages131072
AddAndCustomizePages262144
AppleThemeBorder524288
ApplyStyleSheets1048576
ViewUsageData2097152
CreateSSCSite4194314
ManageSubwebs8388608
CreateGroups16777216
ManagePermissions33554432
BrowseDirectories67108864
BrowseUserInfo134217728
AddDelPrivateWebParts268435456
UpdatePersonalWebParts536870912
ManageWeb1073741824
UseRemoteAPIs137438953472
ManageAlerts274877906944
CreateAlerts549755813888
EditMyUserInfo1099511627776
EnumeratePermissions4611686018427387904
FullMask9223372036854775807

The SharePoint date format problem

I know what you’re thinking, what date format problem? Well this is something that’s cropped up for me time and time again so let me explain. Most requests I get for new SharePoint lists will usually contain a variety of custom date columns and said list will also require that the default display form shows a unique view of this data.

On the default display form, you will notice that the custom date columns will be showing data that looks like this:

bad date format

Well this just won’t do, will it? The good news is we can fix it!

To be able to customise the display form, you will need to be able to connect to your SharePoint environment using SharePoint Designer, if you don’t have it you can download it from Microsoft here:

SharePoint Designer 2010 (32 bit)

For this example, we are only focusing on one custom date column, at the end of the post I will summarise how to fix this issue for multiple custom date columns.

  • Open SharePoint Designer and connect to your environment
  • Navigate to the lists and libraries and open the list you want to change
  • Within the Forms section – New Form
SharePoint New Form

(I’d recommend creating a new display form for no other reason than to avoid potentially breaking the active one).

  • Give the form a logical name so you can identify it at a glance as a display form (I usually go for DispForm2)
  • Press OK
  • The code behind the display form will open, don’t worry too much about this we are only interested in a small portion of the code.
  • In the Ribbon – under the Home Tab – Press Advanced Mode
Advanced button on ribbon
  • If it’s not already, at the bottom of the form – change the view to split view
SharePoint split view
  • Scroll through the design view of your form until you find the custom date field – select it
  • In the code view, make sure the following line is highlighted:
    <xsl:value-of select="@Start_x0020_Date"/>
    (NOTE: the bits after the @ will be the name of your date field)
  • If you amend this line of code to look like this:
    <xsl:value-of select="ddwrt:FormatDate(string(@Start_x0020_Date), 2057, 3)"/>
  • you should see something like this:
    11 June 2014
  • Save the display form
  • Press Preview in browser to double check the format date function is working as expected
  • Close preview browser, close the display form
  • In the forms section of the list – select your form
  • In the Ribbon – Set as Default
set as default

That’s all there is too it! It might seem like quite a few steps but it isn’t too bad once you get comfortable with the forms code view.

If you have more than one custom date column that you need to format, just amend the code of each custom date column using the example above as a guide (just make sure if you copy/paste the code that each @ name is different.

FormatDate function and locales explained

What our updated code is doing is inserting a FormatDate function, which allows us to add the locale parameters to the end of our line code (the 2057, 3). The locale parameters control what the output of the FormatDate function will be, here are some examples of the outputs and locale parameters:

OutputLocaleFormat
3/23/200910331
3/23/2009 12:00 AM10332
Monday, March 23 200910333
12:00 AM10334
Monday, March 23, 2009 12:00 AM10337
3/23/2009 12:00:00 AM103313
Monday, March 23, 2009 12:00:00 AM103315
23/03/200920571
3/23/2009 12:00 AM20572
23 March 200920573
00:0020574
23/03/2009 00:0020575
23 March 2009 00:0020577
00:00:00205712
23/03/2009 00:00:00205713
23 March 2009 00:00:00205715

If you are looking for a list of all the available locales you can find them here:

https://msdn.microsoft.com/en-us/library/ms912047(v=winembedded.10).aspx

That’s it for now, if you’ve got anything you’d like me to cover feel free to get in touch or leave a comment!

SharePoint Virtual Summit 2017 – Roundup

Yesterday’s SharePoint Virtual Summit highlighted some of the latest innovations for SharePoint and OneDrive. It was an interesting event that continued to further Microsoft’s cloud and mobile first vision.

Here are some of the key takeaways from the summit:

  • Over 250k organizations and 85% of the Fortune 500 have SharePoint
  • OneDrive lets you work with all your files in Office 365 with Files On-Demand
  • Connect existing SharePoint Online sites to Office 365 groups
  • Submit documents for approval with new approval flow.
  • Trigger and share flows in lists & document libraries!
  • Personalised search lets you find more of what you need. Faster!
  • New web parts help you do more in SharePoint and Office365
  • Yammer adds conversation and drives engagement within communication sites
  • Modern SharePoint Online admin experience!
  • Microsoft Graph & Webhooks across sites & lists make SharePoint even more powerful.
  • SharePoint Framework for SharePoint Server 2016 in FP2!

Here’s a great roundup by collab365 of the summit!

Here’s Bill Baer’s blog on the summit

Watch the SharePoint Virtual Summit 2017 in full