Thursday 15 February 2018

PowerApps – implementing offline support in your app

In the previous article I talked about my views on the good, the bad and the ugly of PowerApps at the present time (early 2018), but today I want to focus on a particular scenario – ensuring your app can be used offline. Not all apps will require this capability, but for some scenarios it will be vital. Unfortunately there is no magic button when building the PowerApp which makes it support offline – the implementer has to build it in his/herself, and this can make a simple app quite a bit more complication.

When we talk about an app working when offline, I’d say the general expectation is that:

  • The app is usable (e.g. any dropdowns which pull data from somewhere continue to work)
  • If the app is focused on writing data to somewhere (e.g. a SharePoint list/database/Excel file stored somewhere online etc.), then it should be possible to submit the record whilst offline – even if the connection needs to be resumed for it to actually be saved to the back-end
    • I think lots of PowerApps fall into this category (e.g. log a new incident/I.T. request/holiday request/expense claim etc.), so I’m going to focus on this aspect today
  • If the app reads existing data (e.g. show existing incidents/requests), then it should be possible to access at least some of this data offline (e.g. most recent 20 records, say)

The PowerApps blog has some good info in Build offline apps with new PowerApps capabilities, but I found myself doing some things differently – so thought it worth discussing here.

    Understanding the deal with offline

      A big thing to recognize here is that *the app must be opened for a while once the connection is resumed for data to be submitted*. It’s not like the “fire and forget” experience with the e-mail application on your phone (and one or two other native or “tier 1” applications), where you can hit send and trust the data to be sent even if your phone stays in your pocket. Instead, your users need to have your PowerApp open for a while – and the specifics depend on how you implement offline. In my case I used a timer which checks every 5 seconds if the device is connected to the internet, and then saves the record to SharePoint Online if so – this means the user doesn’t have to DO anything in the app (e.g. press a button), which is something. And, of course, as these things are happening in the background it’s important to communicate the status to the user, so it’s clear when their record has been saved to the back-end.

      By the way, this deal isn’t specific to PowerApps. I’m no expert here, but it seems nearly all 3rd party app (including other apps you might use for such a use case e.g. Nintex Mobile) require this due to constraints on the background processing apps can do when not in use. Even with the right settings in place on your device (e.g. “Background App Refresh” enabled on iOS), this isn’t enough for these apps to push data it seems. So, prepare to coach your users in having the app open for a while once re-connected – and as I show below, you could consider making this really clear in the user experience.

      A sample experience (from my POC app)

      Here’s a video of offline support in an app I built recently, recorded from my iPhone – the holiday/leave requests app I mentioned in the last post. This simulates a user submitting a leave request without a connection (flight mode enabled), and then the record actually being saved to SharePoint Online once the connection is back (flight mode disabled):

      The recipe for offline in PowerApps

      In this section I’ll go through the different bits which are making the offline behaviour work, and provide some starter PowerApps formulas where appropriate (with the important functions highlighted). Remember my case focuses only on adding a new item to a SharePoint list, so it’s the PATCH function which is doing this work. Overall, the recipe I used was:

      • Two info screens within the app:
        • Pending screen
        • Confirmation screen
      • Formula behind submit button:
        • If connected, PATCH record to SharePoint
        • If not connected, CLEARCOLLECT to save record locally
        • RESET form (to clear existing values)
        • NAVIGATE to pending screen

          If(Connection.Connected, Patch('My SharePoint list', Defaults('My SharePoint list'), {Title:Concatenate("Leave Request - ", User().Email, " - ", Text(Now(), "[$-en-GB]dd/mm/yyyy hh:mm:ss" )),SomeField1:SomeControl1.Value, SomeTextField1.SomeTextControl1.Text,SomeChoiceField: {    '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Value: SomeDropDownListControl.Selected.Value, Id: 1 });
          Navigate(ConfirmationScreen, ScreenTransition.Fade),
          ClearCollect(LocalRecord, {Title:Concatenate("Leave Request - ", User().Email, " - ", Text(Now(), "[$-en-GB]dd/mm/yyyy hh:mm:ss" )),SomeField1:SomeControl1.Value, SomeTextField1.SomeTextControl1.Text,SomeChoiceField: {    '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Value: SomeDropDownListControl.Selected.Value, Id: 1 });
          Navigate(PendingScreen, ScreenTransition.Fade))
      • Formula for Timer control (implemented on both “start” and “pending” screens – so user can have app open in any logical location, and record will be submitted). In the OnEnd event:
        • If connected and have record(s) in “LocalRecord”, iterate each record with FORALL and then PATCH to SharePoint
        • NAVIGATE to confirmation screen
        • CLEAR “LocalRecord”

          If(Connection.Connected,ForAll(LocalRecord, Patch('My SharePoint list', Defaults('My SharePoint list'), {Title:Concatenate("Leave Request - ", User().Email, " - ", Text(Now(), "[$-en-GB]dd/mm/yyyy hh:mm:ss" )),SomeField1:SomeControl1.Value, SomeTextField1.SomeTextControl1.Text,SomeChoiceField: {    '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Value: SomeDropDownListControl.Selected.Value, Id: 1 }});
          Navigate(ConfirmationScreen, ScreenTransition.Fade));

      • A “status bar” within the app indicating whether the device is connected or not (as a simple reminder to the user)

      WARNING – those formula extracts are edited/simplified versions of my real ones, and maybe the closing brackets aren’t exactly right. Take care if you try to use them for your formulas – really I’m just trying to convey the concepts!

        Dealing with reading from data sources when offline (e.g. for dropdowns)

        One thing to look out for is if your SharePoint list has choice or lookup fields – you might be presenting the options in a dropdown list or with radio buttons in your app. Since November 2017, you can now have multi-select on such fields, but unfortunately if the user happens to use your app for the first time in offline mode, the dropdown list will not be populated (it will display “Find items”, but no list appears when the user clicks). The user can type in an option, but since they are not selecting from a list of valid choices this will generally go horribly wrong and the item will not be added to SharePoint.

        Also consider that you are most likely not using taxonomy/managed metadata fields, because they are still read-only (as at February 2018).

        So, you need to work around this. One option, especially if you’re already using the PATCH command or similar to update SharePoint, is to bind your control to some static data which can be accessed offline. This is a bit lame, as you’re essentially duplicating the items as hardcoded data within your app – you lose the benefits of your choice/lookup field, i.e. the form dynamically fetching the right options based on what is defined in SharePoint. You could probably do something clever with a formula which refreshes a collection when the app has a connection, but it would be nice if this was handled in a better way by the PowerApps infrastructure. Maybe in the future :)


        PowerApps can work just great offline, but it’s all down to the implementer. The PowerApps framework provides the ability to detect if the device currently has a connection, a timer control (so that actions can be taken automatically), and the various functions for adding/updating data in a data source (e.g. PATCH, UPDATE etc.). These are the ingredients to use when implementing offline support in your app – there’s work to do, but you can control the experience and make sure it makes sense in your context.

        Further reading -

        Tuesday 6 February 2018

        PowerApps–the good, the bad and the ugly (early 2018)

        I’ve been doing quite a lot of work with PowerApps for clients recently, and it’s been an interesting time. No doubt I’m still not the world’s most capable PowerApps implementer by any stretch of the imagination, but I feel like I’ve been through the usual denial/anger/acceptance phases that often come with a new technology ;) Some of my work has centred on a proof-of-concept application for one of the big airlines – specifically, building an app that pilots should be able to use whilst offline on an iPad. The project was to digitise some forms related to booking leave, which is an important part of flight crew scheduling. We effectively rolled 3 forms into one app:


        I gave a presentation on this recently to my colleagues at Content and Code, but I’ve also genericised some of the information so it can be shared more widely (Contoso Airlines!). The deck shared below is a combination of some typical lessons learnt and tips, but also has my thoughts on where PowerApps is a technology, structured into “the good, the bad and the ugly”.

        Potentially transformative, but with a learning curve..

        My conclusions are that it’s a great technology and although it’s aimed at forms-type applications in general, it really shines for relatively simple mobile apps. The organization just has to deal with getting users to have the PowerApps app installed (either from the app store, via an MDM solution such as Intune or Airwatch, or another approach) and then all the PowerApps forms and applications the organization provides will show up there. This is *far* easier and cheaper than developing individual native apps on iOS and Android, which each need to be distributed to users once the build is complete. In contrast, it’s feasible that you could have your Office 365 users working with a simple PowerApp on their mobile device within hours. Think about that for a moment! Sure, PowerApps doesn’t give you the richness that a high-end native app can have, but the barrier to entry is much lower.

        However, it’s not always the simplest for the app implementer to work with. Frequently, things can get into a territory that I’d argue most power users would struggle with. An example in my case was having to abandon the default SharePoint integration/data-handling, and craft my own formulas which use the Patch() function to add an item to a SharePoint list (necessary because my form used multiple screens). This is fiddly work if you have lots of form fields and columns in the underlying list, even for a developer used to such things.

        Customizing SharePoint lists vs. creating a standalone PowerApp

        The implementer must choose between two approaches – customizing the form of a SharePoint list, or creating a “standalone” PowerApp (which might talk to the very same list). Conceptually these are quite different, and have different behaviours. This decision is expressed in the PowerApps menu on a SharePoint list:


        A key thing here is that customized SharePoint forms do not show up in the PowerApps app:


        If users need to work with a customized SharePoint list on their mobile device, then using the SharePoint mobile app instead provides a reasonable experience – in fact, the SharePoint app should hand-off to the PowerApps app when you go to the list, and the customized PowerApps form should then be loaded. However, this currently seems to be a bit hit and miss – most often, I actually just get a web rendered view of the PowerApp, and here it seems that the experience isn’t as optimized for mobile as a true PowerApp – some pinching/zooming is often necessary.

        I’m sure things will be straightened out here eventually, but I still think I’d prefer a different model – what about if certain customized SharePoint lists could be flagged to appear within the PowerApps app (perhaps by a tenant administrator)? That would seem a better arrangement to me at least.. 

        Other interesting aspects

        The presentation includes some high-level detail on other things I’ve worked with that might be interesting:

        • Installing the on-premises Data Gateway to connect to on-premises data (SQL Server in my case)
        • Implementing offline support in a PowerApp (using a Timer control to submit data once reconnected)
        • Some implementation details around using a Gallery control for navigation, and the 10 PowerApps functions I found most useful from the 150+ functions which are available

        My “good, bad and ugly” summary:

        If you’re not that interested in the presentation and want to skip straight to the summary, here are my conclusion slides:




        By the way, if you’re wondering what I mean by “web experience (upscaled phone/tablet view)”, let me try to explain. Certainly a form/app looks good on a mobile device, and is optimized for that form factor – all the controls are very “thumb-able”, and even typing into a textbox works fine (shown here at tablet/iPad size):

        4 - BA PowerApp - paternity leave screen

        But when a PowerApp is accessed on a PC, there is no responsive design or optimization for the device – the very same rendering for phone/tablet is used, which is just plain weird in my view:

        PowerApps - PC 800 - msg

        And no, I don’t know the Office 365 suite bar displays “Dynamics 365” either (particularly since Dynamics is not even in use in this tenant). But I’d happily live with that if I could have a better experience than the strange “phone screen in a PC browser” deal that we currently get. Nintex Forms does a better job of this, so I’m not sure why PowerApps went down this route. Again, I’m hopeful some enhancements to this will appear at some point.

        So that's some headlines from what I spoke about. I'll most likely publish some other PowerApps articles which dive into more detail on implementing offline/using the on-premises Data Gateway etc., but essentially I just wanted to share the slide deck in case it’s useful to anyone:

        The slide deck: