Part 6 – Making our Apps Mobile with PowerApps

Over the past few months, this series of articles has focused on actually building an AWA based solution. In this article, we are going to look at the options available to you for making your App mobile with the new Microsoft PowerApps .PowerApps is currently available as a Public Beta – by invitation. Request your invitation at: https://powerapps.microsoft.com

What is PowerApps?

PowerApps is a tool designed for Power Users and developers to build mobile apps from your corporate data fast. The tool uses a model similar to Excel to build expressions and formulae and so should feel familiar to many:

 App mobile

Apps created in PowerApps run inside the PowerApps clients on Windows, iOS and Android.

Preparing our AWA Database for External Access

As we discussed in the previous section, AWA applications can be either Locked (distributed using Save for Deployment) or unlocked. It is only possible (at the moment) to open the database up to external access in Unlocked apps and this is handled in the Access client:

Image 2

Click on Manage Connections:

Image 3

From here you can click on Enable Read-Write Connection and either From My Location or From Any Location, as required. Once this is done, you can click View Read-Write Connection Information”

Image 4

I have redacted some of this information – but this can be then used to create a Connection String which can then be entered into PowerApps to connect it to your database:

Server=tcp:XXXXXXXXyw6.database.windows.net;Database=db_XXXXXXX_51e9_4bc9_8575_8e36ebaade20;User ID=db_XXXXXXXX_51e9_4bc9_8575_8e36ebaade20_ExternalWriter@yqaqwyeyw6.database.windows.net;Password=-XXXXXX^/dD-Rm;Trusted_Connection=False;Encrypt=True;

At this point you should use Notepad to take the connection information for your copy of the app and create a Connection String.

Creating Your First PowerApp

Step 1: Creating a Connection

The first thing you need to do to connect PowerApps to your AWA database is to create a Connection:

Image 5

Image 6

Choosing SQL Azure from the list of available connections, enter your connection string and then click Connect:

Image 7

Unfortunately, at the moment, you don’t seem to be able to name your connection so you may end up with several called SQL Azure which could become confusing!

Step 2: Your First App – Using Start from your data

PowerApps gives you the choice to build an app from scratch, build it automatically from a Data Source or build from a template. As we are talking about making mobile apps to extend the scope of your AWA application, we’re going to choose Start from your data:

Image 8

Having clicked, you can then choose which data you want to connect to:

Image 9

The next step, having chosen SQL Azure, is to choose the Dataset – click Default and then select a Table:

Image 10

In this example we’re going to select [Access].[Contacts] which is the name AWA gives to our Contacts table from within our Web App. Note that at this point it doesn’t seem possible to connect to queries in the database – just Tables.

PowerApps now goes away and creates a default 3 page app based upon this single table with Browse, Detail and Edit screens:

Image 11

So, we now have a mobile app connected to the Contacts table from our AWA – you can run it right here in the PowerApps client by clicking the play Icon at the top of the screen.

This app can be customised and improved from here in the PowerApps designed – there are many improvements you may want to make – for example changing which fields are shown in the Browse screen, changing the sorting of the records, etc…

Sorting and Filtering

If we select the ‘Gallery’ in the browse page (the control which display the grouped fields) we can see it has an Items property which includes an expression:

Image 12

Here’s the expression:

Sort(If(IsBlank(TextSearchBox1.Text), ‘[Access].[Contacts]’, Filter(‘[Access].[Contacts]’, TextSearchBox1.Text in Text(Address))), Address, If(SortDescending1, Descending, Ascending))

The syntax of the Sort expression is:

Sort(Table,Formula[,SortOrder] )

So, looking at the expression from our app, we can see that the screen will present data from the Contacts table if there is no entry in the Search box and Filtered records if there is. However, the search and sort are both referring to the Address field and I would prefer to use Contact Name for this – here’s what we need to change:

Sort(If(IsBlank(TextSearchBox1.Text), ‘[Access].[Contacts]’, Filter(‘[Access].[Contacts]’, TextSearchBox1.Text in Text(‘Contact Name’))), ‘Contact Name’, If(SortDescending1, Descending, Ascending))

If you make this change you will see, even in design view, that the order will change straight away.

Another interesting part of this expression is that the Sort is either Ascending or Descending depending upon the value of aContext Variablecalled SortDescending1. This variable is set by clicking the ⇵ button on the top bar in the app. Selecting this button reveals how this variable is updated:

Image 13

We have the following expression in the OnSelect property of the image:

UpdateContext({SortDescending1: !SortDescending1})

Again, looking at the syntax:

UpdateContext( { ContextVariable1: Value1 [, ContextVariable2: Value2 [, … ] ] } )

We can see that variable SortDescending1 is reassigned a value !SortDEscending1. The “!” symbol represents a logical NOT and therefor this expression is toggling the value of the variable from 0 to 1 and back – and this controls the sort with no further code.

What to do next

If you would like to give PowerApps a try, then you need to apply for an invitation to try it out as mentioned at the beginning of this article. In addition to this brief introduction, you will also find the help for PowerApps pretty good – it is available directly by clicking the ? on the PowerApps designer and here:

https://powerapps.microsoft.com/en-gb/tutorials/

I hope you have found this brief introduction a useful extension to this series and hope you enjoy PowerApps. This marks the end of these articles for the time being – download the e-book containing the full series.

Read more from this series: 

Access Web Apps Part 1 – Introduction on Building Solutions for SharePoint

Access Web Apps Part 2 – Editing Views, Automating with Macros

Access Web Apps Part 3 – Capturing User Information, Sending Reminders / Email Alerting

Access Web Apps Part 4 – Formatting Emails and sending reminders

Access Web Apps Part 5 – Managining and Deploying Solutions

Download the Full Series within the free eBook here

About the Author: Julian 2

Julian Kirkness has been an Access user since version 1 and has spent many years developing and marketing Human Resources and CRM applications built in Access and Access Web Apps.

His first business started in 1996 with an HR application built entirely in Access 95. This business grew considerably over the years as did the product – moving to a SQL Server back end and ultimately to a fully Web based solution. Selling the business in 2010, Julian had a period of inactivity (due to non-compete clauses) and about 3 years ago started looking for technology to build new applications and a new business.

He decided to use Access again – in the form of Access Web Apps (AWAs) this time – because of the uniquely rapid development process, combined with the robustness of SQL Azure and SharePoint. The ability to market the apps through the SharePoint App Store was also critical.

He quickly became the first person to publish apps built with the technology on the App Store and so far has 4 apps available. For further information seehttp://software.kirknessassociates.com.

 

Share this on...

Rate this Post:

Share: