Category: Powerapps

How to create a Parent / Child PowerApp with related SharePoint Lists

Dealing with Parent / Child data in PowerApps can be a little challenging. In this post, I will show you exactly how to render data from a master SharePoint List and display the related (child) records in PowerApps.

Related Lists Concepts

A lot of custom applications have been built over the years. Using Microsoft Access we could easily create relational data with a related form, then Infopath came along and we had repeating tables (but this made data hard to get to), and now PowerApps allow us to have relational data and a nice Parent-child UI to go with it, although it can be a little complicated to achieve.

Here are a few examples of what you might use relational data for.

Each Project has:

  • Multiple Project Tasks
  • Multiple Project Files
  • Multiple Project Goals

Each Expense report has:

  • Multiple Expense Costs
  • Multiple Expense Approvers
  • Multiple Expense Receipts

Each Company Location has:

  • Multiple Location Employees
  • Multiple Location Audits
  • Multiple Location Sales

SharePoint Lookup Fields

Here is a visual example of an expense report showing the Parent-Child relationship. The example shows that each Expense report has multiple child items. This is achieved in the data source by adding a Lookup column to the child SharePoint list.

So let’s have a look at how we add lookup columns to provide this relational view of the data.

We can add a Lookup column to our child list. In the example above, the Expense Items list has the Parent ID field to link it to the Parent list of data, the Expense Report.

When you create a lookup field you can pick another SharePoint list that is on the same site to do the looking up from, and then pick which column of data from that Parent list you want to display in the child list. In this case we are bringing over the ‘Title’ which shows the title of the Expense report, however, you could bring other fields across as well if there is other useful information to see.

For End-users this field in the child list is going to appear as a drop-down box when they are entering data, so they can select which Expense Report this Expense item is to be allocated to.

That covers some real basics of how you might have data setup in SharePoint lists to provide related lists. Let’s now take a look at how that might be used in PowerApps.

Case Management PowerApp Example

The main PowerApp screen shows a list of cases (Parent data) :

Then each case you click on shows the data associated with that case. In this instance showing that the case has 2 sets of child data, it has multiple tasks and multiple files associated with the case (the child data):

Branch Offices PowerApp Example

The main PowerApp screen shows a list of Branch Offices (the Parent data):

Then each Branch you click on shows the data associated with that Branch. In this instance showing that the Branch has multiple audits (the child data):

Expense Report Example

The main PowerApp screen shows a list of Expense Reports (the Parent data):

Then each Expense Report you click on shows the data associated with that Expense Report, i.e. the related Expense items for the Report (the child data):

These real-world examples hopefully help you understand how relational data and relational SharePoint Lists can be used within a PowerApp. We will be looking into the Expense Report PowerApp in a little more detail later on in this post.

SharePoint Data in PowerApps

Complex Column types

There are a few types of fields that are considered more complex fields. Complex column types are:

  • Lookups.
  • People.
  • Managed Metadata.
  • Choice.

Within PowerApps complex data is structured like a small table with one row in it.

Today we are mostly going to be covering Lookup fields, the way data is stored for a looked up value is:

  • ID = The SharePoint ID of the item in the list.
  • Value = The title of the item.

To automatically populate a complex field, a more advanced syntax is used.

Tip: If at all possible avoid using these types of fields in your App, it’s not always possible but it is much easier if you can.

SharePoint Lists in PowerApps

Here is an example of how PowerApps views a SharePoint list. Simple fields have visible data, complex fields have a clickable icon.

When you then click on one of the complex icons, a screen is presented with the structure of the Complex field. This example is a lookup field called ‘Parent_Expense’, showing the ID and Value:

So, if we refer back to where we added a lookup column in our child SharePoint list, this is the ‘Title’ field we added, and in PowerApps you can get to this data using the formula as shown below:

Getting the Parent ID within your PowerApp

The SharePoint item ID does not exist until the new item has been saved, therefore working with Parent and child in your PowerApp is easier on existing items, here are some examples:

We will look now at a solution that works with new items and existing items by using the power of having the parent record as a variable.

Parent Record as a Variable

You can set a whole record to be a Variable in PowerApps. This would mean the variable is set to be a whole row from a SharePoint list.

This is useful in many cases in an App e.g:

  • OnClick in a gallery.
  • Filtering child items in a “sub-list”.
  • Having data about the last submitted form.

Parent-Child Relationships in PowerApps

Let’s now look into a simplified view of the PowerApp Parent-Child concepts. The example below shows a Parent Expense report with ID = 42.

The Child items each have that Parent ID lookup associated with them to show they all go together with that expense report.

This sub-list of items would be a Gallery in PowerApps and would be filtered so that the ID is equal to the Parent ID of the Parent Variable, meaning only related child items are shown.

Then if we are creating new items using a form, to enter new expense items, the Parent ID needs to have a default value set to the ID of the parent you have already selected or created in the PowerApp. This would be achieved by setting the control default as shown in the screenshot.

Before we look at building this solution in a very simplistic way in PowerApps, let’s review some of the Syntax we will need to use.

Creating a new Parent item

We will do this with the ‘OnSelect’ for the “New Expense Report” or ‘+’ Button in the App:

  • This will create a new form to fill in.
  • Clear out the Parent variable, in case it has any data already in it.
  • Then trigger navigation, this is an optional step depending on how you have built your App and will not be needed if you created it all on one screen.

This creates a new form but does not help with filling out the form or saving the data, it just takes the user to the screen where the new form is.

Saving the New Item

Once we have filled in the details and click the ‘Next’ or ‘Submit’ button, whatever we have called it, we will need the following syntax:

  • The ‘OnSelect’ will submit the form, trying to create the expense report.
  • If it is successful, then the ‘OnSuccess’ property will trigger, which will set the variable to what we have just submitted, and also create a new child form after the Parent has been created.  This means the child is only created after the Parent exists, otherwise, there would be no Parent ID to link to the Child.
  • Then the ‘Item’ property for the Parent form control is set to the value of the variable we have just set, i.e. the details we just entered on the form.

Editing an Existing Parent Item

This syntax is for when we are selecting an existing Expense report in the App.

This will:

  • Set the Parent Variable to be equal to this Parent record.
  • Navigate to the expense report parent screen if it is on a separate screen.

That about covers all the different fundamentals of creating this kind of application with Parent-child relationship, so now we can take a look at building a sample PowerApp to get this working.

DEMO: Expense PowerApp

You will need two SharePoint lists for ‘Expense Report’ and ‘Expense items’ containing some data to be used in the PowerApp. For demonstration purposes, we are going to add all the controls onto one screen.

Step 1 – Firstly create a Canvas app from blank, using the tablet format, and give it a name.

Step 2 – Select the ‘View’ menu and ‘Data Sources’. Select ‘Add data source’ and add your SharePoint lists (‘Expense Report’ and Expense items’) as data sources.

Step 3 – Insert a Vertical Gallery control to hold all the expense reports.

Set the ‘Layout’ in the right-hand panel to be ‘Title and Subtitle’.

Set the ‘Items’ property of the Gallery to be the name of our Expense Report SharePoint list:

Name the Gallery – ‘galExpenseParentList’.

Add a label control to the top with the text ‘Expenses’ and then size and position both controls to look something like this:

Step 4 – Next from the ‘Insert’ menu select ‘Icons’ and add a ‘+’ above the Gallery control, which will be used to add new expense reports.

Step 5 – From the ‘Insert’ menu select ‘Forms’ and add an ‘Edit’ from. This is to add a form for new expense reports.

Position it to the right of the Gallery and set the ‘Data source’ also equal to ‘Expense Report’.

Then choose which fields from the data source to add to the form. For the purpose of demonstration we just have Title and Department fields.

Set the ‘Columns’ property from the right-hand panel to ‘2’ and position the form next to the Gallery.

Set the ‘Item’ Property of the form to equal ‘varParentItem’ variable. This will be the variable that is set to the Parent record we are creating or looking at.

Give the form a good name e.g. ‘frmExpenseParent’.

Step 6 – Add a button below the fields on the Form and set the text on the button to ‘Next’. This will be the save button for a new expense report. We should now have something looking like this:

Step 7 – Now we need to make the form show when the ‘+’ icon is clicked.

Select the ‘+’ icon and in the ‘OnSelect’ property we are going to add the following:


This is to create a new form and set the Parent variable to be blank.

NB: We will set the default visible statuses of our controls as the last step in building the PowerApp.

Step 8 – We will now update the ‘Next’ button to save a new Parent Expense Report, and allow the user to then enter the first child item for this parent.

Select the ‘Next’ button and set the ‘OnSelect’ property to:

Step 9 – We now need to update the ‘OnSuccess’ property of the Form to carry out the actions we want to happen after the Parent record has successfully been submitted.

To do this, select your form and set the ‘OnSuccess’ property to:

This is going to set our variable to be equal to the last record submitted, and also set the form to still be in edit mode so that we can add further items or edit our entry.

NB: This code will also add a new child item, but we need to set the child form up first so will come back to it later in the post.

Step 10 – To set up the child items we will need a Gallery to show all the Expense sub-items for each Expense Report, as well as a form to add each Expense into the child list.

Gallery

Firstly add a ‘Blank vertical’ Gallery control, and set the ‘Items’ property (the data source) to be our ‘Expense Items’ list, or whatever the name of your equivalent child SharePoint list is.

Set the Layout in the right-hand panel to be ‘Title and Subtitle’.

Set the Gallery name to ‘galExpenseChildren’.

NB: At the moment this is not filtered and will show all items in the list, we will filter it to only show items related to the Parent in later steps.

Form

Next, add another Edit Form, and set the ‘DataSource’ property of this form to also be the ‘Expense Items’ list.

As we did with our other form, you will also need to click ‘Edit fields’ in the right-hand panel and choose which fields you want to show on your form, for this example we are adding ‘Amount’, ‘Category’ and ‘Date’.

The other field we will need to add is the Parent lookup, the field that links the child to the Parent, so add the ‘Parent ID’ into the form too. We will need to set this fields’ default value to be that of the Parent ID that is in the Parent form. We will do this in later steps.

Set the Form name to ‘frmExpenseChild’.

Shuffle and size the items so it looks something like this:

NB: This is just for demonstrations purposes, you would likely want it to look a little smarter in a real PowerApp, and there is every chance you might want a different screen for each aspect.

Step 11 – Now we need to go back to our ‘OnSuccess’ property of the first form ‘frmExpenseParent’ to add the New form command so that a new child form is created when an Expense report is submitted successfully on the Parent form.

Add the New command as per the screenshot below:

Step 12 – Next we are going to set the default value of the ‘ParentID’ field in the child form, to be that of the ParentID that has just been submitted in the Parent form. This will create the Parent to Child link when a new record is created.

To do this, select the ‘Parent ID’ field on the child form, and on the right-hand panel click ‘Advanced’ and then click the padlock icon to unlock it. This will allow us to select the data card in the left-hand controls panel.

In the left-hand controls panel, select the ‘Parent ID_DataCard1’, choose the ‘Default’ property and update it as per the screenshot below:

This will now set the ID and Value of that field based on the data in our Parent variable ‘varParentItem’.

Step 13 – The second form now also needs a submit button.

From the ‘Insert’ menu add a button and place it underneath our child form.  Set the text on the button to be ‘Save’.

Select the ‘OnSelect’ property as shown below:

This will submit the record to the Child list, again we want to do other things when that is done, but we don’t add them here, instead we add them to the ‘OnSuccess’ property of the Form.

Step 14 – Select the child form, and choose the ‘OnSuccess’ property, and set it as shown below:

This will mean that once the record is submitted successfully the form is reset and ready for any new records you want to add.

Step 15 – We now need to filter the Child item gallery so that it only shows the child items related to the current Parent Expense report record.

To do this select the Child gallery and select the ‘items’ property and update it as shown below:

This filters it to only show Child items that have the same Parent ID as the Parent ID in our variable ‘varParentItem’. Meaning that only the expense items associated with this Expense Report are shown.

Step 16 – When a user selects an existing Parent item from the Gallery, we need that to populate the variable ‘varParentItem‘.

To do this select the Parent gallery and go to the ‘OnSelect’ property, and set it as shown below:

Now is a good time to run your PowerApp, you should have a working App that looks something like this:

Try each of the main bits of functionality e.g.:

  • Add a new Expense report and add child entries (expenses) to that report.
  • Select existing Expense Reports and the associated expenses should show in the child gallery.
  • The child form should have the Parent title defaulted in the Parent ID field, so when you do add a new record it is associated with the Parent Expense report that is selected or has just been added.

Step 17 – Now the only thing left to do is to ensure that controls are only shown when they are needed and not visible when they are not needed.

Let’s start with when we add a new Expense Report, i.e. when we click the ‘+’ icon. When this happens, we do not want the Child Gallery or Child Form to show yet, as we want to create the Parent record first.

We are going to use a variable to control this. To do this select the ‘+’ icon and choose the ‘OnSelect’ property. We already have some code in here, we are going to add the setting of the variable ‘varShowChild’ to false to the bottom of the formula we already have, as shown:

Then on the same child Gallery, select the ‘Visible’ property and set it to equal the variable ‘varShowChild’.

Similarly, for the Child form, and the Save button on the form, select the ‘Visible’ property and set them to equal the variable ‘varShowChild’.

This now means that on clicking the ‘+’ to add a new parent record, the child elements will NOT be visible.

Step 18 – Now we want those items to become visible whenever the ‘Next’ button is clicked and the Form submission is successful in the Parent form, meaning we have just created an Expense Report, and we are now ready to enter the Expense Items associated with it.

Select the Parent Form, choose the ‘OnSuccess’ property, and add the setting of the variable to the formula we already have in there, like below:

Step 19 – Set the Parent ID field on the Child Form to not be Visible.

You don’t have to do this, but there is no need for the user to see that Parent ID against every child record, so all you need to do is select the Parent ID field or the data card from the left panel. Select the ‘Visible’ property and set it to ‘False’.

The Value will still be passed to all the child forms, but it is just not showing to the user.

Run the App and give it a go!!

Summary

You should now have a working App, give it a test through and see if there are any improvements you might want to make.  You could spend more time setting the visible and not visible elements to cater for every scenario, you could maybe add a Clear selection button to reset the screen, and you could make the application look more attractive, or even have Parent and Child elements show on different screens.

As you can see there are so many possibilities for layout and functionality within your PowerApp, this allows you to create some fantastic and functionally rich applications.

A great way to understand how your App is working in more detail, to help you either debug issues or just to help with understanding, is to run the application and at each step go back into the designer window and check the status of any variables being set or data being created.

To do this with this App, run the application and click the ‘+’ icon to add a new Parent. Then go back into the designer window and click on the ‘View’ menu and click variables:

You can see the status of the variables.

For the ‘varParentItem’ variable you can click the table icon and see more details of the variable and that at this time it is empty, this is because we have not entered details on our form and submitted the record yet.

You can also see that our ‘varShowChild’ variable is currently set to false, meaning the child gallery and child form are currently not showing, again correct for this stage.

This approach can be used for every step of the application, giving you a real in-depth view of how it all hangs together, and even help you understand how to improve or modify your PowerApp.

July 2, 2019

PowerApps Best Practices

Introduction

In this article we will look at:

  • Branding
    • Creating Responsive Screens.
    • Using Collections to standardize your UI.
    • Other UI Design best practices, including using SharePoint list to configure label text.
  • Delegation how to work with it and around it

Responsive Screens

To demonstrate how you might set up your PowerApp UI to be responsive and standard across the board we will work through some basic examples of principles you can apply throughout your PowerApps design.

We will start with a blank canvas PowerApp in tablet mode. Set the name of your first screen to ‘StartScreen’.

Responsive Top Bar

The first thing we want to look at is making the screens look similar, for this example, we are going to use a Rectangle top bar, but the principle can be used throughout your app.

Step 1 – From the ‘Insert’ menu select ‘Icons’ and scroll all the way down to find ‘Rectangle’. Add it to your screen, and size and position it to show a bar across the top of your screen, something like this.

Give it a meaningful name. e.g ‘rectTopbarStartScreen’. (NB: You may follow a different naming convention)

With all the different devices that a PowerApp can be viewed in, a shape like this can end up showing in various different sizes, you might decide to fix the height by selecting the ‘Height’ property, however doing this would mean you have to set this for every screen you create, so let’s look at how we can do that better.

Step 2 – Create a second screen by clicking the ‘…’ menu next to the first screen and clicking ‘Duplicate Screen’.

Give this second screen a meaningful name. e.g. ‘MiddleScreen’ and rename the rectangle on this screen to be ‘rectTopbarMiddleScreen’.

Step 3 – One way you might choose to set these two (or more if you have other screens) so that the elements on the screen are set to look the same, in this case we are dealing with the height, would be to set the screens height to be the same as the first screen, like this:

So, if you change the first screen the other screen will automatically change. Give it a go.

Step 4 – We can also do the same approach with other Properties, let’s try it with Color.

Select the rectangle on the Middle screen and choose the ‘Fill’ property. Set it as shown:

This will now set the Middle screen Top Bar to be the same color as whatever the Start Screen top bar is set to.

Give it a try, change the colour of the Top Bar on the Start screen.

NB: It is worth noting at this point that the more logic we add to make the screens and controls responsive, the more you have to be careful when changing or deleting those controls that refer to the parents.

Other Responsive Controls

Now as the top bar may change size and position for different device views of this screen, you want to ensure any controls on the screen are positioned in relation to the position of your top bar and in relation to the size of the screen.

For this example, we are going to use a Gallery control on the screen.

Step 1 – Add a Gallery control from the ‘Insert’ then ‘Gallery’ menu.

Name it ‘glyStartScreen’ and manually size it to fill up the remainder of the screen below the Top bar. Like this:

So, the issue with this, is that if the Top bar size changes it the Gallery control will stay where it is, so let’s look at how to solve this.

Step 2 – We are going to set the ‘Y’ property of the Gallery to reference our start screen top rectangle, this will mean that the Gallery is positioned in relation to the rectangle. Meaning that if the size and position of the rectangle move, then the Gallery will also move.

To do this, select your Gallery and choose the ‘Y’ property, and enter the following:

This will set the Y position of our Gallery to be the Y position of the rectangle + the height of the rectangle. So, the Gallery will start where the rectangle ends.

Step 3 – We can also do a similar thing with the Width.

We had previously set the Gallery manually to take up the full screen we can see in the designer window, however different devices can show different sizes. So, to make it Full screen we should set the ‘Width’ property of the Gallery to = ‘Parent.Width’.

This means it will always take its width from the Parent screen size.

Responsive Control Height

Now let’s look at controlling the position of controls on the screen in relation to the Top bar, starting with this Gallery control.

Step 1 – At the moment when we move the rectangle at the top of the screen the Gallery control will move with it, however if you move it far enough the Gallery drops off the screen. So, let’s look at how to solve that.

To Solve this, select the ‘Height’ property of your Gallery control and set it as per below:

This will set the height of the Gallery to be equal to the height of the screen minus the height of the Rectangle at the top of the screen.

NB: As soon as you change this height manually on screen of a control, you will lose any formula that was against that property.

Step 2 – Add a label to your top bar and set the text of the label to equal ‘Start Screen’. Name the label ‘lblTopBarStartScreen’ .

Add a button to the bottom of the screen and set the text to ‘Go to next screen’. It will look something like this:

Step 3 – Of course as we have it setup currently this will mean that the Gallery will go down behind the button. So, we need to update our formula with some further conditions.

Select the Gallery control, and select the ‘Height’ property, add the following formula to the end:

This is now setting the height of the gallery to the height of the screen minus the Top Bar Rectangle height and the bottom button height, meaning that with the position setting we did earlier it will display between the two even when the top bar size is changed.

Step 4 – Let’s set our button to move us to the next screen.

Select the button, select the ‘OnSelect’ property, and set it as per below:

This will move us to the Middle screen with a fade transition effect. Run your App and give it a go!

Position of Controls

Now we are going to look at the middle screen.

Step 1 – Add a label to the screen, call it ‘lblHello’, and set the ‘text’ property of the label to be:

This will say hello to the active user.  Let’s make set the ‘Fill’ property to Red and the Color; property to white, to make it look like this:

Step 2 – For this example we want to set the label to always be in the middle of the screen. To do this we need to set its X position and its Y.

X = Horizontal Position (e.g. 0 = left hand side of the screen)

Y = Vertical Position (e.g. 0 = Top of the screen)

Set the X Position

We want it in the middle, so select the ‘X’ property and set it as per below:

This is setting the X value to be the width of the Parent screen divided by 2 minus the Width of the Label divided by 2. If we didn’t do that second part then the labels’ left-hand edge would start in the middle, rather than the whole label being in the middle.

Set the Y position

Using a similar approach, we can now set the Y position:

Now we should have a Middle screen with a label in the middle of the screen looking something like this:

Using Collections to standardize your UI.

So far we have covered some great ways to create responsive screens, however as your application grows you may find it confusing which screen and which control is the parent one.

For some of the properties of your controls a better way to do this is to create a collection.

A collection is a list of values or data that we are storing in our App. You can view the collections in your App by selecting the ‘File’ menu and then choosing ‘Collections’, here you can see what collections are already in your App, at the moment there shouldn’t be any.

So, let’s create a collection.

Step 1 – In the left-hand panel select the ‘App’ at the top and choose the ‘OnStart’ property. This property defines what happens when we start the App, and we are going to use it to create our collection.  Set it as per the screen shot below:

This is creating a collection called ‘PAColors’ with a ‘MainColor’ Field set to ‘Blue’ and a ‘SecondaryColor’ field set to ‘Red’.

You can run the ‘OnStart’ formula simply by clicking the ‘…’ menu at the side of the ‘Apps’ and clicking ‘> Run OnStart’. A good way to test it works and populates your collection.

Once you run it, if you go and take a look at your collections you will now see this collection has been created, and in this example with 2 colors.

Of course, as your app gets more complicated you may need more colors for the different aspects, or more collections for different settings.

Using the Collection

How do we use the values in the Collection to set the colors in our Application? Well for each of the controls you want to set to be the MainColor, you would set their ‘Fill’ property to:

In this case this will set these controls to be Blue.

Then you would use the same approach to set controls that you want to use the Secondary color, set their ‘Fill’ property to:

This gives a great way to standardize your Applications throughout.

Other UI Design Best Practices

In this section we are going to cover some UI design techniques to give a good look and feel to your PowerApp, as well as allowing configuration of the Label text in your app via a SharePoint list.

Firstly, we will start with a Loading button.

Loading Button

There are many use cases in applications where the user triggers an action on the screen e.g. Clicks a button, and the application has to go away and carry out an action e.g. Connect to, Save, or Load Something.  Often when the user does this they are blocked from interacting with the screen, which is good practice to do, however if the user has no other information on what is happening they may think the application has crashed. Therefore, it is good practice to give some form of ‘Working…’ indication in your UI.

Let’s look at a couple of ways to achieve this:

Step 1 – Insert a ‘Button’ control to your middle screen and position it below the red label we have there already.

Set the ‘OnSelect’ property to set a variable:

So that when the button is clicked we set the variable ‘varStart’ to true.

If you want you can add a label to the screen and set the text to equal ‘varStart’. This is a good way to help debug and understand your apps as you build them, you can have a number of labels on your screen showing the status of various elements, these can then be deleted once the build is complete.

Step 2 – From the controls menu insert a ‘Timer’ control to your screen.

Set the ‘Duration’ property to – ‘6000’ – As this is in milliseconds this means it will be a 6 second timer.

Set the ‘Start’ property to – ‘varStart’ – This will mean the time starts when the variable is true, which is when we click the button.

Set the ‘OnTimerEnd’ property to reset the variable back to false:

Set the ‘Reset’ to the opposite of our variable ‘!varStart’  – NB: Notice the use of the ‘!’ NOT operator in order to set it to the opposite of the variable.

Step 3 – Now we want to disable the button whilst the timer is running, so that it can only be clicked once to trigger the 6 second timer.

To do this set the ‘DisplayMode’ property of the button as shown below:

This will set the display mode of the button to edit when the timer is ‘0’ otherwise it will be set to disabled.

Now run the app and give it a go, you should be able to see that the button is disabled whilst the 6 second timer is running.  This would be used when the button is triggering something else in the back ground, maybe a connection or data load.

On its own this is not that useful; however we can use the timer to also show some indication of progress to the user which is always good practice when building a user interface.

NB: We can set the ‘Visible’ property of the Timer control to – ‘False’ now that we know it is working.

Step 4 – Add a ‘label’ control under the button to show the progress.

Set the ‘Visible’ property of the label to:

This will show the label when the button is disabled and hide it when the button is enabled.

This label could have a message in it something like ‘Fetching data’ or ‘Connecting…’ giving the user some confidence that something is happening.

We should now have something looking like this, where on clicking the button the label is shown and the button is disabled, and then after 6 seconds the label is not shown, and the button can be clicked again:

Step 4 – There are many ways to improve this further, so let’s take a look at the label and see if we can make it give even more confidence to the user by showing different text as we progress through the 6 second timer.

Select the Label that is below the button and set the ‘Text’ property to something similar to this, you may have your own messages you want to use:

This will mean that as the timer progresses the message will change showing the user that we are getting closer to the end.

Step 5 – You could also add a loading GIF of some sort that would only display when the Button is disabled.

In the example we have used https://loading.io/ to download a loading GIF to use on the screen. You may have another image or GIF you want to use.

To set this up add your image to the PowerApp media library by selecting ‘File’ then ‘Media’ and uploading the image. Make a note of the image name.

Then add an ‘Image’ control to your screen and set the ‘Image’ property of the control the name of your image file, in this example ‘Spinner-1s-200px’.

Position the image where you want it to show on your screen.

Set the ‘Visible’ property of the image control to:

This will mean that the GIF or Image shows at the same time as our progress message label.

Now when we run the App and click the button we will have something that looks like this:

This gives a much better user experience when we are waiting for something to happen in the background after the user has taken an action, in this case clicking a button.

Pop-up Information Box

Now we are going to look at a way of giving the user a pop-up / pop-out information box on the screen. We will set the text on the information box to come from a SharePoint list. This is a great way to make your application configurable in order to easily maintain the text and information shown on your application.

Step 1 – From the ‘Icons’ menu choose an icon that you want to use as a button for the user to click to show this popup box.

Position the icon in the top right-hand corner of the screen on the blue bar, set the color to white.

Step 2 – Again from the ‘Icons’ menu add a ‘Rectangle’ to the screen. This will be the pop-up window.  Name it – ‘ infoCallout’

Position and size it to look something like this:

Step 3 – Now we will set the position of the Rectangle.

Set the ‘X’ Property of the rectangle to:

This will set the X property (left hand edge position) of the rectangle to be equal to the width of the main screen, which puts the rectangle off the screen show it will not show when the App is run.

Step 4 – From the ‘Icons’ menu add the ‘Cancel’ Icon to the top right of the Rectangle, this is to be used to close the pop-up / pop-out menu when it appears.

Name it – ‘icnCloseCallout’

Set the ‘X’ property to be:

This will set the X position of the cross to always be on the right-hand side of the call out box, by referencing the X position and width of the rectangle minus its own width.

We should have something like this:

Now we want to make the Rectangle show on the screen when we click the icon at the top left of the visible screen, and then move out again when we click the cancel / cross icon on the rectangle.

Step 5 – Select the icon that you have used in the top right of your visible screen and set the ‘OnSelect’ property as shown below:

Step 6 – Now reset your Rectangle ‘X’ Property to be:

When you click the Icon on the screen the Rectangle will show over the screen. We now need to be able to move it back using the cancel icon we added to the rectangle.

Step 7 – Select the ‘cancel’ icon and set its ‘OnSelect’ property to the same logic as the icon we used to open it:

This will change the variable and therefore change the result of the formula in the ‘X’ property of the rectangle to move it back to outside the visible screen.

Run the App and give it a go!

Configurable Screen Text

So now we have a pop-out box to display information to the user that currently has no information on it, in this section we will look at how to add text onto the pop-out that is configurable via a SharePoint list.

You will need a data source for your configuration data, in this example we are using a SharePoint list with one item in it as shown:

In a real App you could have many entries each with meaningful titles that could be being used to set the text in different places on your application, field labels, information messages, anywhere that you want the text presented on screen to be configurable.

Step 1 – Add this SharePoint list as a data source in your PowerApp. NB: In our example the List is called ‘Configuration’

Step 2 – Add a label control to your Rectangle and set the text color to white and name it ‘lblCalloutText’.

Step 3 – Connect the label to the data source by setting the ‘Text’ property to:

This will set the text of the label to be the description value of the row in the Configuration SharePoint list where the title = ‘InfoText’.

Now the text should be shown in your label. However, it will not currently be moving with the Rectangle when we open and close the pop-out.

Step 4 – Select the label containing the information text, and set the ‘X’ position property to:

This will keep the label on the rectangle when it moves, you can play with that ’30’ figure to get the position to your liking.

Run your App and give it a go!

Using a SharePoint list like this is a fantastic way to allow users to configure how their application shows to the user. You might use this approach to decide what the text is on each item in a menu, the text shown on buttons or the name shown at the top of each screen.

Delegation

What is delegation in PowerApps? ……. In short, it means that PowerApps will delegate the processing of data and functions on that data to the data source, rather than moving the data to the app for it to be processed locally.

e.g. If I want to retrieve data from a table with 3000 rows, but I only want to show the rows of data where value is equal to a “X”, then PowerApps uses delegation for this. The PowerApp will then only receive the relevant rows and does not need to get all 3000 and then filter it in the PowerApp.

However not all data, functions, or Formulas can be Delegated. You must be careful what functions you try to use particularly when you are dealing with a large data source.

Formulas that can’t be delegated will be processed locally, meaning that all the data will be brought to your PowerApp which could cause performance issues, but also could hit the limits to the number of rows of data that can be brought back in this way. This can cause false results in your filtering of the data. NB: By default, this limit is set to 500, but can be set to a maximum of 2000.

For more details on what can be delegated and what the limits and constraints are take a look at this Microsoft documentation – https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

So let’s look at an example of how Delegation can cause problems and a way around it.

For this example, we are using a SharePoint list with 300 rows of data in it. (Our example data has 100 with date in 2017, 100 with a date in 2018, and 100 with a date of later than today in 2019)

If you want to recreate this, you will need similar data.

Step 1 – Firstly we are going to set the ‘Data row limit for non-delegable queries’ to 150.  To do this select ‘File’ then ‘App Settings’, and ‘Advanced settings’.  You can see the value field at the top. Set it to ‘150’.

Step 2 – Then create a new screen in the PowerApp.

Add a ‘Vertical Gallery’ to it to show our rows of data.

Add a ‘label’ next to the Gallery to show how many rows it is showing.  So, we are starting with something that looks like this:

Step 2 – Select the ‘Text’ property of the Label and set it to:

This will mean the label will show the text “Count Rows” with the number of rows in the Gallery next to it. Calculated using the ‘CountRows’ function.

Step 3 – Connect the Gallery to the SharePoint dummy list.

Select ‘View’ menu, select ‘Data Sources’ and on the right-hand menu click ‘+ Add data source’.

Choose ‘SharePoint’ and add your dummy SharePoint List as the data source.

Now select the Gallery, select the ‘Items’ property and set it to the name of your list you have just added as a data source e.g. ‘DummyList’.

Once you have done this you will see that the label now shows in how many records are in your dummy list:

Notice how even though there are 300 items in the list, the count is only showing 100 even though we know we have 300. This is due to the lazy loading, so that no data is loaded unnecessarily.

However, if you run your App and scroll down you will notice the number going up as it loads all the records and will then show all 300 so we have all our rows.

Step 4 – Now we are going to filter the gallery using a function that cannot be delegated. We are going to try and use the Date function.

Select the Gallery and choose the ‘Items’ property, and set a filter as you can see below:

You will notice the wavy blue line in our formula which when you hover over gives a delegation warning, that looks something like this.

This warning is reminding you that you may get some invalid results where formulas can’t be delegated, and where the data row limit for non-delegated queries might affect the outcome.

So, run your App and see how many rows this formula returns, it will likely return the correct result of 100 records for our sample data.

However, if we then flip the formula to bring back the records that are less than now, meaning that if it worked we should get the other 200 records, like this:

Now run the App. You will see that because of the delegation issue along with the 150 row limit being hit, we have not got the expected result, in our example it only filtered 50 rows of data. This is because it has only brought back 150 rows of our data of 300 rows and has then applied the filter.

You may get different results depending on your data.

Delegation Work around

This shows you that you have to be careful when using data sources and when using delegated and non-delegated functions against that data source. The results you get can be incorrect and confusing. Let’s have a look at some ways to work around this constraint.

At Source Data

One method to work around this problem in this instance would be to add a field to our source SharePoint list that is a text field with the Year in it. e.g. 2017, 2018, 2019. Then for the filtering you wanted to do use the years field to do it.

E.g. set the ‘Items’ Property as follows:

Now when you run your App, you will see that the correct amount of data is retrieved, in our example that would be 200 rows.

NB: Don’t forget the Gallery will ‘Lazy Load’ sometimes so you will have to scroll down on the screen to get to the full data set.

With a Collection

If your data isn’t too large to cause performance issues e.g in the 10’s of thousands, another good way to avoid the delegation issues would be to create a collection within your PowerApp. You could do this by adding the following code to the ‘OnStart’ property of your App: – ‘ClearCollect(Dummy, DummyList)’

This would create a collection when that App starts up called ‘Dummy’ that has the data from our SharePoint list in it so that we can filter it in PowerApps and get accurate results.

We would then set the ‘Items’ property to filtered values from this collection instead of directly from the data source, thereby avoiding issues with delegation. Try doing the same filtering we did in our examples above but with this collection and you will see that the results are as expected, and we do not hit any delegation issues.

Delegation Summary

In the simple examples above we can see how delegation can cause issues in your data results within PowerApps, and that you need consider delegation and its’ limitations in your design, particularly when dealing with larger data sets.

If any part of your formula contains functions or operators that are non-delegable, this can cause false results. Watch out for the errors being indicated within your formula with the blue wavy lines, giving a delegation warning message. These errors mean it might not work, however it may be fine if you are dealing with low number of rows. The error is a good reminder to check if delegation is going to cause issue in each instance.

To work around the delegation constraints, although not always something you have in your control, setting your data up with the constraints in mind can help you achieve the functionality and filtering you need on the data in your PowerApps.  In our example we used a text field in the data with the year in so that we could use a function that is Delegable, thereby allowing the data processing to be delegated and passed back correctly to the PowerApp.

Another option would be to bring all the data into a collection within your PowerApp and using this as your data source. Whilst this can help solve your delegation problems, you wouldn’t want to do this if the data set is very large, and it would also present considerations when wanting to update the data or if the data needs refreshing in your app.

Just to remind you again take a look at this article on delegation from Microsoft to help you understand it further – https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

 

June 25, 2019

Building consistent User Interfaces across multiple PowerApps screens with minimal fuss

PowerApps is a fantastic tool allowing Power Users to create applications with minimal code, quickly. However, as with many types of IDE, it’s very easy for it to become unmanageable and overwhelming as the number of screens, controls and complexity grows.

In this post, we’re going to examine several approaches that you can use to build your PowerApps making them far more maintainable, which will lower the cost of over the life-time of your PowerApp.

Let’s get started.

Tip #1: Use a good naming convention!

The first thing you should be doing in all of your PowerApps is to change the default naming of your controls to something more intuitive. (For example, we may want to call our header label, “Header-Title” as opposed to “Label1” and our “Header-Logo” as opposed to “Logo1”). If you follow an intuitive naming convention, you will not only find it much simpler to remember their names but also find that it’s much easier to refer to them when writing code against them.

In this first example, you will notice that the controls (within “Screen1” > “Header”) are named with the defaults that PowerApps allocates to them. If you then decide to copy them into “Screen2” (from “Screen1”), they are given unique names such as:

  • Label1_11
  • Rectangle1_11
  • Icon1_11
  • Image1_11

In one aspect this is good as all of your controls have a unique name, but in many aspects, this is awful because you won’t be able to reference them in code easily or more importantly find the correct one in the PowerApps IDE.

What I recommend here is that you name all of your controls correctly from the outset, then when you copy them, the first part will still allow you to identify their purpose more efficiently.

For example:

If you set your first header label to be “Header-Title” then when you copy it will be given “Header-Title-1”. If it makes sense, you may also choose to go one step further and change the number PowerApps appends to match the screen number or even the name of the screen.

Tip #2: Have one master of the truth!

If you create individual controls within a PowerApp and need to copy them across more than one screen, then it’s a real burden having to change them all as your requirements dictate. For example, imagine you have a header label that presents the ‘App Name’ and ‘Version’ across 10 screens within your PowerApp. You will need to set the position, colour, text (amongst other things), in all ten screens. If you then need to change the text to show a different version number and perhaps decide to go with a different colour, you will need click, edit and change in all ten screens. This can be extremely tedious, error-prone and time-consuming.

However, you will be pleased to know that there is a much better way of building your PowerApp, making it easier to maintain, which is what this second tip will cover!

Note: This second tip builds on from the previous one, because if you’ve named your controls adequately, then it helps to refer to them like I will show you now.

Firstly, let’s examine the main problem.

In the screen below, you will notice that there is a copy of a logo (which is an “image” control) used as a header on both screens (“Screen1” and “Screen2”). If you change the image used for the logo in either screen then it will not reflect in the other.

To solve this, you can simply refer the Image property to that of the Image property set in “Screen1”. So you’d set it to: ‘Header-Logo’.Image. This now means that if you change the image in the “Screen1”, it will also change in the “Screen2”.

You can use this exact same process for as many of the control attributes as you wish to (e.g height, width, X, Y, colour, and so on).

Now the best bit. If you then copy the “Heading” from “Screen2” to a third screen (e.g. “Screen3”, you will notice that all the references are also copied, saving you even more time.

I am sure you will agree that this is a great timesaver in the long run, however, there’s still a problem:

  1. It is not obvious which item is being used as the “master” to which all the control properties are referenced.
  2. You can’t delete (or make major changes to) the “master” (e.g. “Screen1”) without breaking your entire application.

There is room for improvement!

Tip #3: Create global references for objects

This approach is very similar to the previous but improves it a little further. With this approach, we create a centralised repository of values and settings that we can reference, similar in concept to a CSS file, an “App Settings” used in other applications such as ASP.Net. Once we’ve configured the settings we can use them time and time again and load them via our OnStart commands. Should changed be needed to any element in the future, we know exactly where to go and look for the master version.

To create a centralised repository go into the “App” section and add your definitions using the “function box” for the variables we want to set.

In the example below, we can set our primary and secondary colours by creating a variable called “varTheme”.

We could also add variables for our Header to accommodate the example used in the previous tip.

Once you have all relevant variables defined, you can ‘right click’ “Run On start” in the App menu, and this will run the code when the application starts.

How do we use our App setting variables?

Now we can amend the properties of the items in screen 1 to refer to the variable attributes saved in the centralised repository. For example, if you click on the “Header-Logo” icon and select the “Y” option as shown here:

In the function box, we can change the properties (of the “Y” coordinate) to refer to the value we’ve already put in the central repository like this: “varHeader.Logo.Y”

We can do the same for each of the other variables we set:

  1. X (varHeader.Logo.X),
  2. width (varHeader.Logo.Width), and
  3. height (varHeader.Logo.Height).

This will set the variables for our initial logo to those saved centrally, so when you copy the heading onto subsequent pages, they will also refer to the centralised values. Should you need to change any of the variables, updating them in the centralised repository will flow through into all instances.

The same process can be completed for all other items you wish to display.

Tip #4: Consistent navigation

The “ClearCollect” function clears the previously used values and replaces them with values we specify (if we only used “Collect” the existing values would be kept and more items added to the collection). This can be used to standardise the navigation options within our application. Again, we set the parameters within the same centralised repository we used in the earlier example ensuring that we have defined all of the navigation options we want to use.

Insert a “Horizontal” gallery to “Screen1” and resize or move it to fit into the available space.

Rename the gallery to “Navigation” and delete the default features as we don’t need them.

Now insert a button into the gallery and resize the button. Notice that the gallery has automatically connected to the “CustomGallerySample” however we want to use our “colNavigation” settings that we created earlier so we need to change the function box to “colNavigation”.

Now click on the first button and select “Text” from the drop-down box on the left (it will probably say “OnSelect” at first), then add “ThisItem.Title” into the function box as shown below which will pull through the title descriptions for each button in turn that we included in the ClearCollect instructions.

We can also use the colour definitions we have stored in our Theme above. Select the first button, choose “Fill” from the drop-down parameter box and enter “varTheme.PrimaryColor” in the function box & all of the buttons should now reflect our preferred primary colour.

Using the same process we can change the style of the Hover text display to use the secondary colour.

We also need to set the navigation destinations that should be used when the button is clicked, so we need to define the “OnSelect” destination. In the function box for OnSelect insert Navigate(ThisItem,NavigateTo, ThisItem.NavigateEffect)

Now if you copy the navigation gallery into other screens, each time you use a button it will always go to the same place using the same transition effect.

Additional tips

1. If you think that you may change the size of logos or other items in the future, then you can build in the flexibility to automatically move other objects around them so they don’t overlap or get hidden if something in the chain moves by dynamically assigning starting X positions.

For example, we may have an image to the left and a heading description to its right. If you fix the X & Y coordinates for both items, it’s possible that one may cover the other if its size changes.

We can fix that by making the coordinates of the heading on the right to depend on the size and position on the logo to its left. In the example below, the starting X position of the Title is calculated by adding the starting position of the logo to its left, the width of the logo itself plus the size of gap required between them before the header begins.

Should the logo change size at all, the heading will automatically move across for them both to fit.

2. If you do dynamically assign positions as above, you may need to click “Run OnStart” a few times to see them all change. The first Run OnStart will reflect changes to the first item, the second time Run OnStart is used it will amend all the items that are dependent on the position of the first item.

3. Don’t worry if the content disappears from view whilst you are amending their properties – if you click “Run OnStart” they will reappear.

4. Generally the more properties you set for the variables the better.

5. Remember to put a semicolon (;) in between Set commands, but not at the end of the last one.

6. When setting values for colour, Color refers to text, Fill refers to the shape or area.

7. Ensure you have set all of the variables you need before copying an item (if you forget one, you’ll need to add the attributes to the central repository and also add references to it in each of the copied locations).

8. When copying items to other pages, make sure you copy all elements (i.e. copy the entire Header) not the constituent parts (i.e. Header title, Header logo and Header settings etc.) to make the process as efficient as possible.

Bonus Material: PowerApps Canvas Components

This is a new, experimental feature so is subject to change going forward but could replace some of the things we’ve looked at in this post. Please be wary of using them as if they do change in the future it could break the apps you create today.

First, we need to turn on the component, so from the File menu, chose App settings, then Advanced settings.

At the very bottom of the Advanced Settings list, you’ll find the Experimental features, and at the bottom of this list is the Components setting which you need to turn on.

This is an App level setting, so if we export components out of this app into a different application, you would also need to turn on the Components features in the second application as well. Note that if you subsequently update the component in the first application, these changes will not be reflected in the second application the component was copied to, you would need to make the changes again.

Once the components setting is turned on, new options can be seen on the page (a components option next to screens and components drop down options in the Insert menu). The option to create a new component can be found in both of these menus.

Create a new component & call it “Connected”.

This will create a new area in the middle of your screen to add the component to. Start by adding a rectangle (from the Icons menu) and resizing to fit the screen.

To make this bar be coloured green if connected to the internet or red if disconnected, we need to use an if formula as follows. If(Connection.Connected, Green, Red) . I’m currently connected to the internet, so the rectangle correctly automatically changes colour to green.

To make the display more informative, we can also add an information icon (again resize it to fit) and a Text field to add more detail… If(Connection.Connected, “You have an internet connection”, “Someone turned off the internet”)

This component can be given custom properties, so change the display name, property name and set the data type to Number as below.

Now we can set the values for the components

We can then add this connected component into our original screen,

Which will add it to the top left corner of the screen, you’ll need to drag and resize it to wherever you want it to be displayed.

A few caveats for the use of these Canvas Components:

1) Instance: A master instance of a component is scoped to the app. It is a local master.
2) Images: It is not yet possible to package media files when importing a component, but this feature is coming soon.
3) Collections: Using Collect within a component is not fully supported.
4) Data Sources: These are not saved with components.

I hope you’ve found this useful and can use these approaches to save you lots of time going forward when developing applications.

If you would like additional help, I’ll be more than willing to do so, I’m available on the MicroJobs site with the freelancer name MarkS.

June 18, 2019

How to make PowerApps work when your remote users are disconnected (Offline)

Offline capabilities in PowerApps was one of the most requested features on UserVoice according to Microsoft so it is an incredibly popular topic. Making your PowerApps work when there is no connection to the Internet, offers huge benefits for remote field workers, salespeople and frequent travellers. We are clearly in an age where we can be connected in a lot more places, but there are still those scenarios where your workers will be offline and need to be able to use your PowerApps.

In this article, I am going to explain the following:

  • The Challenges of supporting Offline Mode within PowerApps
  • How to create, read, update and delete (CRUD) data when offline (and when back online)
  • How to automatically sync offline changes back to an online data source.

This article was created based on a presentation I gave at the PowerApps Virtual Summit. If you’d like to watch the video then it can be viewed here (but does require an on-demand “Summit Pass”).

What’s out of scope in the article?

Offline Mode can get really complicated when you think of scenarios such as how to manage merge conflicts. For example, imagine 2 remote workers using the same PowerApp and editing the same piece of data. When one updates the record (while the other is offline), this would cause a conflict resolution that’d need to be handled by the PowerApps developer. Although this article isn’t going to provide a solution to this, it’s certainly worthy of consideration if your PowerApp is widely used and supports many workers accessing the same data at the same time.

As the goal of this is to be a level 300 article, we will just assume that this won’t affect our PowerApps 🙂

Challenges?

As mentioned, Offline mode in PowerApps can throw up many challenges, namely around synchronising data and resolving conflicts in that data when going back on-line. To handle this properly, you’d have to build extra PowerApps screens to allow the user to select which version of the data is the truth, at that point in time. This isn’t an insurmountable problem. Given time, developers can think of all the possible scenarios and write a UI and code to handle it. PowerApps doesn’t offer features to accommodate this, but nor does it prevent you from handling them yourself.

The biggest challenge facing, Offline PowerApps support, at the moment is based around development and testing. If you want to create “offline” files (data) with your PowerApp then this relies on using the mobile device’s memory. If you’re developing in a browser, or in “preview” mode in PowerApps studio, then the code you write to support offline mode won’t be able to access these local files. This challenge will also manifest itself when you have Collections with offline data.

In the image below, you can see the type of error you will receive when trying to save data locally if developing (or running) in a web browser.

The intellisense, that gives you type-ahead support and UI hints, also won’t work properly for offline files.

It’s also hard to design and style galleries where the data will only be resident when offline, resulting in you needing to create “fake data” and then remove it before saving.

Tip: To debug and test your apps, you can easily put your phone in “airplane mode” which will simulate the mobile device being offline.

What’s the standard (“Out of the Box”) Offline behaviour?

You may be wondering how PowerApps works if you do nothing to support offline mode. What works and what doesn’t?

If you create a standard 3-screen app based on a data source some of the things you can still do are:

  • Browse data in galleries
  • Search and Sorting of gallery data
  • View details of individual records.

However, you cannot:

  • Refresh data via your data source as the connection is down, or
  • make any edits, delete or save the data

In other words, if the data is already loaded into the PowerApp you can still navigate it, but anything requiring you to change or refresh it is not going to work.

Meet my demo Offline PowerApp

In this article, I am going to refer to a very simple looking PowerApp that I built to demonstrate the Offline techniques discussed later. The App I am building is an “Issue Tracker” where the data is stored in the Common Data Service (CDS). There are 3 fields stored; namely “Title”, “Description” and internally generated “ID”. The screen below shows what it looks like to create a new “Issue”.

Foundations of Offline Capabilities

What building blocks does PowerApps offer to allow us to build in Offline support? There are 3 main things that we need to utilise (and get very familiar with), as follows.

The Connection Signal

This is the object that we can use to query if the device is Offline or Online. For example, when we query “Connection.Connected()” it will return “true” when there is a connection and “false” when there isn’t. This is usually done like this: Connection.Connected(“Online”, “Offline”) (which could set a labels text property, for example).

SaveData()

The SaveData() function allows us to save data to the Offline file.

LoadData()

The LoadData() function allows us to load data from the Offline file.

The Connection Signal

As mentioned in the next screenshot, the Connection signal returns the information about the network connection. When on a metred connection, you may want to limit how much data you send or receive over the network. To check for a metered connection, you query “Connection.Metered” which will return “true” or “false”. Querying Connection.Metered is useful because you can assume a user either is paying for the data or may have a low bandwidth connection. In this instance, you might want to prevent large amounts of data coming down to your PowerApp such as video, or large images.

Connection.Connected has already been discussed earlier.

A demonstration of the Issue Tracker App

The screens below demonstrate the entry point and home screen to my Issue Tracker App.

TIP: As an aside, if you ever need to demonstrate your PowerApp working Offline mode to an audience, you will need to mirror it to a projector monitor somehow. I found that there are several apps out there that can easily mirror a phone but, if you then turn on “Airplane mode” (to demonstrate “going Offline”) then the mirroring will stop working. Not ideal. I am using PowerApps on Android and found a mirroring app that works via USB called ApowerMirror. This also what I used to present my session at Collab365 PowerApps Virtual Summit.

Moving back to the demo …

  1. Screen 1 – This is simply the way to load the app from the PowerApps menu. I clicked “Issue Tracker”.
  2. Screen 2 – A loading screen opening my app (and typically where you’d load in your “On start” code).
  3. Screen 3 – The main screen has a “Simulate On Start” which is running the functionalities that would typically in the “In Start”. In our case, it is pulling data from the data source and populating our galleries.
  4. Screen 4 – Once the On Start has completed the button is re-enabled.

Once we’ve retrieved all of the data we require for the Issues, we’re ready to show them:

  1. Screen 1 – This presents our Issues. Notice that there’s a green circle to denote that the device is “online” (more on this shortly).
  2. Screen 2 – This is me turning my phone to “Airplane mode” to make it effectively completely Offline.
  3. Screen 3 – You can now see that the green circle has changed to red and the text now says “Offline”. It’s also worth noting that natively, PowerApps will show a partially transparent black rectangle that says “No Internet”. You will need to tap that to make it disappear.
  4. Screen 4 – This shows what happens when I turn Airplane Mode back off. The native experience then shows a green rectangle to signify the phone is back online.

Using the “Connection” Signal

We’ve already touched on how you can query the Connection signal but now we want to take a look at how we can build a small “component” with a circle and a label to show whether it’s offline or online (see next image).

Microsoft recently implemented components into PowerApps, which allow you to group controls into a reusable component. For this demo, I made a very simple component to show the connectivity status (called ‘comStatusInd’). This means it’s really easy to reuse it across screens without having to recreate it each and every time.

How do we make the circle green or red based on the connection status?

To achieve this drop a circle onto the canvas and set its color property to this function:

  • -> If (Connection.Connected, Green, Red)

How do we change the text to show Offline or Online?

Drop a label onto the canvas and set its label property to a function of:

  • -> If (Connection.Connected, “Online”, “Offline”).

Using SaveData() and LoadData()

These 2 functions are really important and it’s essential you have a good understanding of them. So let’s give a rundown of how to use them both.

SaveData()

This function is used to save data to the collection and takes 2 parameters:

  1. Collection of Data – the data you want to save in the Offline file.
  2. File Name – Name of the file to store against.

LoadData()

This function, as you’d imagine, is used to load data back out of the local collection and takes 3 parameters:

  1. Target Collection – the collection you want to load the data into.
  2. File Name – the file name containing the data.
  3. Boolean – True / False … if you can’t find the offline file do you want to load without an error. (normally you’d set this to “True”).

Other points of Note

The major point to make about the following slide is how you will bind your controls to their data. To support Offline mode you will need to bind them to the local collection rather than the underlying data source. If we didn’t do this then when you try to browse or edit the forms as soon as the connection goes your PowerApp would error. Pointing them to the collection allows the PowerApps to still serve the user with the data it has.

What is our standard App Start Process going look like in an Offline enable PowerApp?

When your PowerApps loads you would probably want to follow these steps:

If your PowerApps loads with an active connection …

  1. Get the data from your data source and load it into a collection.
  2. Save the Collection Data to an Offline file.

If your PowerApps loads with no active connection …

  1. Load the data from your local Offline file.
  2. Fill the collection from that locally loaded data.

A Typical “On Start” examined

The following slide illustrates how you structure a code snippet to handle the On Start for an Offline enabled PowerApp.

If there is a connection:

  1. Connected.Connected – this returns a boolean if the mobile device is connected or not.
  2. ClearCollect(LocalIssues, Issues) – The ClearCollect function will delete all the records in “LocalIssues” and then fill it with data from “Issues”).
  3. SaveData(LocalIssues, “OfflineIssues”) – This will save the data to a collection called “OfflineIssues”.

If there is no connection:

  1. LoadData(LocalIssues, “OfflineIssues”, true) – If we’re not connected (denoted by the comma), this reloads collection with the name “OfflineIssues”. The “true” tells PowerApps to ignore any loading errors.
  2. LoadData(LocalChanges, “OfflineIssues”, true) – If we’re not connected, we’re also going to load another collection the name “OfflineIssues”. The “true” tells PowerApps to ignore any loading errors. This will store all the changes made when offline.

In both Offline and Online cases you then want to:

  1. Navigate(BrowseScreen1, None) – regardless of whether the device is offline or online, we will then navigate to a screen called “BrowseScreen1”, which will show the data.

What happens when a change to the data happens?

When a change happens to the data this is the process that we will need to follow for Online and Offline:

Online change management

If we’re online we’re going to patch the data source, refresh it, fill a collection with data and then save it offline.

Offline change management

If we’re Offline when the data is changed it’s saved a collection that will track the changes. We then save that collection Offline and then reset the form to reflect the change.

How to add new items when you are connected to the Internet.

If the device has a connection then this is the flow illustrated in the following slide:

  1. If (Connected.Connected …) – this returns a boolean if the mobile device is connected or not.
  2. Patch(Issues, Defaults(Issues), { … } – As we are connected to we want to create a record in the “Issues” data source, using a default issue record and a change record supplied by values from the UI (as JSON).
  3. Refresh(Issues) – Now that we’ve patched the data, we need to refresh the records in the “Issues” data source.
  4. ClearCollect(LocalIssues, Issues) – Fill the collection with the updated data source.
  5. SaveData(LocalIssues, “OfflineIssues”) – This will save the data to a collection called “OfflineIssues”.

How to add new items when you are disconnected from the Internet

If the device has no connection to the Internet, then this is how you would code this up to save the records locally to the device (not the data source).

  1. If (Connected.Connected …) – not show on the slide, but let’s assume this returned “false”.
  2. Collect(LocalChanges, { … }) – Add the changes made in the UI to a our local collection called “LocalChanges”. We used “ChangeType” to denote the type of change (e.g. “a” = add, “d” = delete, “e” = edit). This is used to sync your changes back up when reconnected.
  3. SaveData(LocalChanges, “OfflineChanges”) – This will save the data to a collection called “OfflineIssues”.
  4. ResetForm(NewForm) – Reset the contents of the form to its initial values.
  5. Back() – Display the previous screen.

How to edit items when you are connected to the Internet

If the device has a connection then this is the flow illustrated in the following slide:

  1. If (Connected.Connected …) – this returns a boolean if the mobile device is connected or not.
  2. Patch(Issues, First(Issues), { … } – As we are connected to we want to create a record in the “Issues” data source, using the first Issue record (matching the IssueId) and a change record supplied by values from the UI (as JSON).
  3. Refresh(Issues) – Now that we’ve patched the data, we need to refresh the records in the “Issues” data source.
  4. ClearCollect(LocalIssues, Issues) – Fill the collection with the updated data source.
  5. SaveData(LocalIssues, “OfflineIssues”) – This will save the data to a collection called “OfflineIssues”.

How to edit items when you are disconnected from the Internet

The slide below omits the If (Connection.Connected) purposes, but this is still how we test. If this returns “false” that this code will run and this is how we handle an item being edited.

  1. Collect(LocalChanges, { … }) – Add the changes made in the UI to a our local collection called “LocalChanges”.
  2. SaveData(LocalIssues, “OfflineChanges”) – This will save the data to a collection called “OfflineChanges”.
  3. Navigate(BrowseScreen1, None) – regardless of whether the device is offline or online, we will then navigate to a screen called “BrowseScreen1”, which will show the data.

How to delete items in a connected and disconnected scenario

If we are connected:

  1. RemoveIf(Issues, IssueId = BrowseGallery1.Selected.IssueId) – Delete a record matching the selected Issue Id.
  2. Refresh(Issues) – Now that we’ve deleted the data record, we need to refresh the records in the “Issues” data source.
  3. ClearCollect(LocalIssues, Issues) – Fill the collection with the updated data source.

If we are disconnected:

As we can’t commit the deletions we need to store them locally and flag them to be deleted when we sync back up online.

  1. Collect(LocalChanges, { …, ChangeType=”d” }  – Save the record and flag is it to be deleted with a “ChangeType = “d”).
  2. SaveData(LocalIssues, “OfflineChanges”) – This will save the data to a collection called “OfflineChanges”.

How to sync changes when your PowerApps is reconnected?

Now that we’ve discovered good ways to store our edits and deletes in a local connection on the device, we need to work out how to commit these changes when the phone is connected again. One elegant and simple way to achieve this is to use a Timer Control and have it check every 30 seconds. The timer control will simply run (every 30 seconds) and if it’s connected will sync the changes back. The Slide below illustrates the process at a high level.

Steps to sync Offline Changes

  1. If (Connection.Connected && CountRows(LocalChanges) > 0, … – If we are connected and we have at least 1 record to sync back.
  2. ForAll(LocalChanges, – Iterate through every pending change in the “LocalChanges” collection.
  3. If (ChangeType = “u” – We only want to process updates (not deletes) at this stage.
    1. Patch(Issues, First(Issues), { … } – As we are connected to we want to create a record in the “Issues” data source, using the first Issue record (matching the IssueId) and a change record supplied by values from the UI (as JSON).
  4. If (ChangeType = “a” – We only want to process new records.
    1. Patch(Issues, Defaults(Issues), { … } – As we are connected to we want to create a record in the “Issues” data source, using a default issue record and a change record supplied by values from the UI (as JSON).

How to sync “deletes” back to the data source

  1. If (ChangeType = “d” – We only want to process deleted records.
  2. Remove(Issues, First(Filter(Issues, IssueId = Local_ID))) – Deletes the record matching the current Issue Id (in the loop).
  3. Clear(LocalChanges) – Empty out our local changes as we’ve not processed the records.
  4. SaveData(LocalChanges, “OfflineChanges”) – This will save (the cleared collection) to a file called “OfflineChanges”.
  5. Refresh(Issues) – Gets the latest data from the data source.
  6. ClearCollect(LocalIssues, Issues) – Fill the collection with the updated data source.
  7. SaveData(LocalIssues, “OfflineIssues”) – Save the new data to our offline collection.

How to handle security?

As you will be storing data locally, you do not want that data to be accessible by other users/applications on your device. For this reason, the storage is encrypted and it’s only possible to load data from the PowerApp that saved it. This means that PowerApps cannot share local data with each other.

What are the limitations with PowerApps?

I hope by this stage you have a really good idea how to deal with Offline features within PowerApps. In this section, I want to talk briefly about how some of the limitations that you should be aware of.

The first consideration is that you are limited by the amount of memory on the mobile device. You should be mindful that some mobile phones could be close to their memory limit so storing 100MB’s of data could result in an “out of space” issue.

Summary of Offline support

To review what we’ve covered let’s do a quick recap.

PowerApps offers built support for offline namely using LoadData(), SaveData() and Connection.Connected.

Although, this function is there to help it’s still largely up to the developer to wire the application up which will include building a UI that can work in both Offline and Online. You may also want to consider synchronisation issues and more importantly conflict resolution where the data has changed while the user is Offline.

To Debug your application, you will need to work on the mobile device due to the limitations of the browser.

Microsoft is making improvements in this area, so watch out for up and coming updates.

Need help?

If you need my help then please contact me on MicroJobs. I am willing to provide short term support, or can even build a fully-fledged PowerApps offering Offline support.

June 11, 2019

Creating an autograph book with PowerApps, Flow, SharePoint and Azure (4,250 words)

If you’re prone to losing paper copies of autographs (or indeed anything!), or wish to create an app that would allow you to combine images into a Word document, then here is a solution using a combination of PowerApps, Flow, SharePoint and Azure Functions that could be the answer to your problems. This demo uses the story of an autograph book, hopefully, you can see how this process can be slightly adapted to many other common scenarios.

It is worth pointing out at this point, that this example pulls together a number of different tools to achieve an end-to-end process. Whilst PowerApps is often described as a no-code/low-code tool, there are limits to what can be achieved without having to do small pieces of development. If, and when, you do meet those boundaries, you can easily pull on other resources to help provide the functionality which, at this time, may be lacking.

We’re going to use the following tools and processes to create and store the autograph book:

1) PowerApps: to take a photo and signature
2) Flow: to process the data obtained by the PowerApp
3) SharePoint: to store the data
4) Azure function: to combine the elements into a single document
5) SharePoint: to stored the final completed autograph book

Step 1. Use a PowerApp to capture images and autographs

The PowerApp contains a number of different screens, each of which moves the process on a stage at a time. Throughout the creation of the PowerApp, I’ve tried to promote some aspects of best practice which I always try to follow.

It’s always useful to create some standard screens in your apps to contain some basic information about the app itself. This means that, should someone else take over the maintenance/running of the app in the future, they have all the necessary documentation and explanation about how it all works for their future reference. These pages are not included within the navigation of the live app, however, when opened in PowerApps Studio an editor will be able to view the screens and read the documentation.

1) Governance – Documentation – An overview of the app and what it is intended to do, including any revisions which have taken place.
2) Governance – Collections – An explanation of the collections which are being used and the data being stored in them.
3) Governance – Styling – One method of being able to govern consistent styling where I have a number of controls governing colours etc. which are referenced by other controls in my app. This way, if I have to change a colour, I can do so just by updating the control on this screen.

Always name your components with meaningful descriptions, this makes it easier to identify your control when they appear in the Intellisense rather than trying to remember what Label1 and Label2 was. The format which I follow is:

Type_Screen_Description e.g. lbl_Home_Title

Home Screen

This screen is intended to be the first screen which the user sees when they load the app. The screen is comprised of a number of labels, images and icons to create the look and feel which I need.

The only piece of functionality required on this screen is to be able to navigate when we click on the icon next to “Get New Autograph”. The event which we are going to use is “OnSelect”, which is selectable within the property dropdown. I will use the “Navigate” function to navigate to the Autograph page using a fade transition.

Remember to use the help text with new formulas, the display provides hints to help you complete each section of the formula.

It’s a good idea to check your app works as intended as you develop it, this ensures that you don’t go too far off course at each stage and keeps you on track to create the functionality as intended. To test the app, click the “Run” button in the top left corner of your screen to Preview your progress so far.

In this case when clicking the “Get New Autograph Button” we should move to the next page, so we can enter the autograph details.

Autograph Screen

The purpose of the Autograph screen is to capture the name and autograph of someone within the PowerApp.

There are two types of text input control which I’m going to use on this page, both of which are available from the “Insert, Text” menu in PowerApps Studio.
First of all, I’m going to use a standard text box to capture the name of the person. This behaves exactly as you would expect from any standard text entry. You can type straight into it if you’re using a full browser, or you can use the virtual keyboard if you’re using a mobile device.
The second is the Pen Input control which, rather than taking a textual input, will allow me to sign, draw, scribble etc. within that area.

There are a number of user options associated with the pen control such as the ability to change the colour and thickness of the pen. As I want to only take a signature, in this case, I’ve removed the controls by toggling the “Show Controls” option to off. This is available in the properties blade on the right-hand side of the screen.

At the bottom of the autograph page, we have another navigate button to take us to the next stage to take a photograph.

Photograph Screen

This screen is dedicated to capturing the picture which I want to store along with my autograph.

Photographs can be captured by using the Camera control which can be found in the “Insert, Media” drop-down list.

Within this control, there are some really useful properties that can help us capture the photograph that we want. On the camera option, I’ve used a toggle control, which gives us a Boolean value and added a formula to the Camera property of the camera control to reference this toggle field. This will give the user the option to select which camera is being used, so for example on my phone, I can use the primary or secondary camera as needed.

There are a couple of ways you can trigger a photo caption within PowerApps. The first is by using the Camera Control and changing the OnSelect property to store the Photo by capturing cam_Photograph_Photograph.Photo. This property unfortunately can only be used directly on the camera control, which may not necessarily be the functionality you want.
The alternative is to add an icon and use that to capture the image. The thing to be aware of here is that the .Photo property can only be captured by interacting with the camera control. To use an external control to capture the photo we need to do two things. The first is to change the StreamRate property of the camera control. By default, it is 0, and I usually set it to 100. Then from our “take photo” control, we will call the Stream property e.g. cam_Photograph_Photograph.Stream.

Storing the data

There are numerous ways in which you can store data within a PowerApp, however for the purpose of this demonstration I’m going to use a Collection which will store an array of data within the app for as long as it remains running. Storing it in a collection means I can use the data in various ways across the app e.g. referencing it from other controls
There are two main formulas which are used for putting data into a Collection:
• Collect – each time this is called it will add a new entry into the collection
• ClearCollect – this will clear down any existing data within a collection and then add the new data into it. Using this method will only ever give you one item in the collection.
I am going to add the formula to save my data to a collection to the “OnSelect” property of my “take photo” button. As we only want one photo stored against each autograph we need to use “ClearCollect” (so if the first photo is not clear and you want to take another, the first one is deleted, leaving you with the final best shot to keep).

As well as the photograph, we also need to include the person’s name and also the signature so that we have all of the data we need to create the autograph book entry.
When creating a collection, it is important to ensure that you identify the elements in the collection or think of it as naming your columns. I would always follow this type of convention:
Collect(<Name of my Collection>, { FieldATitle: FieldAContent, FieldBTitle: FieldBContent}

In my case the formula is:
ClearCollect(MyAutographs,{Person: txt_Autograph_Name.Text, Autograph: pen_Autograph_Autograph.Image, Photograph: cam_Photograph_Photograph.Photo})
This will save the photo to the collection.

To review the collection, you can go to File, Collections, and then select the collection you want to view. In the example, we can see the penned signature, name of the person and their photograph.

Alternatively, I can use the View menu within PowerApps studio and select Collections from there.

Review Screen

The purpose of this screen is to review the data which has been collected over the previous screens. It will retrieve the data which we’ve stored within the collection and show it in a few controls.

I am using very simple controls such as a label to display the name, and image controls to display the photograph and autograph.
Each control just needs to pull out the first entry in the collection (as I have only one), and for that, I will use the keyword First.
The Image property of the photograph image control, therefore, would be “First(MyAutographs). Photograph” and the image will be displayed in the photo window. So effectively it is First(<Name of my Collection>).<Element of the Collection>.
Now that we have captured the data, we need to get it into a Word document. I can’t do this directly from PowerApps, so I’m going to use Microsoft Flow to process the data for me.

Step 2: Use Flow to process the data

When creating Flows, it’s a good idea to use “Try, Catch, Finally” as a pattern. This is something which developers use extensively to ensure that any errors are graciously handled. The same can be achieved in Flow by using Scopes and the Run-After configuration.
The actions of your Flow are placed into the Try phase, any errors are caught and handled in the Catch phase. E.g. if one of my actions failed, I want to email the IT Help Desk to raise a ticket for someone to investigate. This is a template which has been added to the Flow Templates gallery by Pieter Veenstra MVP, and is highly recommended pattern when adhering to best practices.

There are Triggers for PowerApps within Flow, and for the most part that will work with a lot of data. If I just wanted to take the photographing element from PowerApps and place it straight into my Flow, then I could use the PowerApps trigger. This is because what is passed from PowerApps is a fully encoded image file which Flow can quite happily accept and turn into a file using one of the many Create File actions (e.g. SharePoint, OneDrive etc).
The complexity with this example is that the image created by the Pen Input is not stored and passed as an image, it is passed as a URI. Therefore, I need to use a different trigger, the “When a HTTP request is received” which allows me to fully define the types for the data being passed to Flow. This type of trigger will generate a HTTP endpoint which I can use in a connector later to join PowerApps to Flow.
When you first create a HTTP request, the HTTP POST URL field will be blank. Upon your first save, it will generate the URL and populate this field in the trigger.

Within the Try, we will be extracting data from the Trigger using Data Compose actions. This will allow me to extract the exact data I need for each action e.g. I need to extract the person’s name, their photograph and their autograph.


For the purpose of showing how data can be passed through a HTTP call, I’m going to pass the person’s name through the query string, and the files through the http body.
The first compose, for the person’s name, will use an expression to parse the query string and identify the person’s name. Expressions can take a bit of time to get your head around, but once you have got the hang of them, they can be quite powerful. The expression for extracting the person’s name will be:
trigger()[‘outputs’][‘queries’][‘person’]
So, if I look at the output of the trigger, it will have a JSON string which contains a section titled queries, and within that, there will be a person. So for example if I pass ?person=Matt on the query string, then the output of this action will be “Matt”.
I’m going to tell Flow that the images for the autograph and photograph are going to be passed through as files, therefore they will be passed as multi-part form data. Therefore, the compose actions for both Autograph and Photograph will use the expression “triggerMultipartBody” and then a value e.g. 0 or 1 depending on the order of things I’ve been sent through. They get passed through as an array of items, therefore it will start at index 0.

The outputs of the compose actions for the Photograph and the Autograph will be a base64 encoded string of the images, which means that I can now happily store them in SharePoint using the Create File action. I will simply use the output of the “person” action in the title, so I will create a file called “Matt-Autograph.jpg” and then the output of the relevant compose action to get the pictures will be used within the File Content.


The next stage within the Flow, once I’ve saved the constituent parts within SharePoint, is to call an Azure Function to take everything and combine it into a Word Document. For this, I’m going to call my Azure Function by using a HTTP action which will allow me to post the data I need to my function for processing. You will see how we get the URL for this later, but all we need to do is provide it with the URL, and any other information I need within the body. For this demonstration, I’m passing the tenancy, the site, and the name of the person whose autograph I’ve collected.

Creating the Connection from PowerApps to Flow

As previously mentioned, I’ve not been able to use the standard connector to bind PowerApps to Flow. As I have a “HTTP” based trigger, I need to use a Custom Connector. A custom connector can be used to create your own connectors to any data source which uses a REST API, which makes them really powerful. The thing to remember, however, is that from the 1st February 2019, you need to have a PowerApps or Flow Plan 1 license to be able to use them. This applies to both the person who’s creating the Custom Connector, but also to any users who are using a PowerApp or Flow which uses the Custom Connector. If you already have custom connectors in place, you are currently in a grace period which lasts until the 31st January 2020, by which time you will need to have purchased the additional licenses or refactored your solution.
Custom Connectors are available for both PowerApps and Flow under the data heading on the left-hand menu.


There are several ways you can create the connector. You can create from Blank using a wizard, Import an OpenAPI file (also known as a Swagger file) from your local file system or from a URL, or import a Postman collection if you have been developing using that free tool.
Once the connector is created you can download to move it between tenancies or update it to reflect any changes or updates.

General Information

As the title suggests this is the high-level information required for our custom connector, but it doesn’t really do it justice as some of the critical pieces of information are required on this page: Host and Base URL.
You can change your icon and your colour etc if you wish, but I’m going to concentrate on what’s below i.e. the scheme, the host and base URL. The content which I need to put in these fields is generated from your Flow, to be exact the URL which was generated from our Trigger.
The scheme is obviously whether it is HTTP or HTTPS:
https://prod-39.westeurope.logic.azure.com:443/workflows/FLOWID/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=SIGNATURE

The host is the URL which hosts your web service. In this case, it is the Azure tenancy address we want:
https://prod-39.westeurope.logic.azure.com:443/workflows/FLOWID/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=SIGNATURE

Base URL is the section after the Host which will remain static i.e. with Flow, you will always have /workflows/FLOWID. Therefore the base URL will be /workflows.
https://prod-39.westeurope.logic.azure.com:443/workflows/FLOWID/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=SIGNATURE

Then you can set the relevant security requirements based on the documentation provided by the end point you’re calling. It will support no authentication, API keys, oauth etc so it can be quite flexible to allow you to authenticate using different methods.

The definition step in the wizard is one of the best tools I have found within the O365 stack for being able to do a lot in little time. It allows us to define what actions are going to take place within the connector, what triggers there are. Realistically I’m going to just concentrate on the actions as my trigger is being provided by Flow.
I will add a new action by clicking New Action and then filling out the form with the basic details such as the summary and the description.

General

The summary is what specifies the title for the action so make it short but descriptive of what it is you’re doing.

The summary is a line of text which offers a little more context around what your action is going to do. Finally is the Operation ID which is the unique name for identifying this operation and will be used when we call it from PowerApps.

Creating the main bulk of the request is really quite simple too because we can import from a sample. We have most of the sample already by taking the generated URL from the trigger event in our Flow.
My request URL looks like this, note that I’m also going to include a custom query string parameter which will take across my name:
https://prod-39.westeurope.logic.azure.com:443/workflows/FLOWID/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=SIGNATURE&Person=MattWeston365

We will now use this completed URL in the wizard to generate our request. Click on “Import from Sample” and you will see the “Import from Sample” blade appear on the right of your screen. Paste your request URL into the URL box, and because Flow is triggered by a Post event, I will select that as my verb.

If I know what my body is going to look like, I can also post in a sample payload into the “Body” area. Whilst I can’t fully configure my payload correctly unless you’ve actually run a test against your Flow using Postman or another tool, I won’t know what my sample payload looks like in terms of pushing through two images so I’m going to add that manually later directly into my Swagger file.


After you’ve clicked Import, you’ll see that your request is now being represented, and the various elements of the query and the body are now represented as objects which you can interact with.

In the query, I know what four out of the five elements are, they are all in the query string which I copied from Flow, so I can assign each element a default value. For each of the following I’m going to make the query string parameters mandatory, but not necessarily visible to the end user as I don’t need them to provide the information. Therefore, I will mark them as “Internal”.
• api-version
• sp
• sv
• sig


For “Person” I am going to make it mandatory and make it “Important” as I need the user to provide some data here at the point of calling the connector.
To make the process work, a few small tweaks are needed to the Swagger file. The tweaks are relevant to the files (images) which I’m going to pass through it, so I need to make a couple of small changes. An example of the working swagger file can be found here:
https://dev.azure.com/MATTWESTON365/_git/A%20Modern%20Autgraph%20Book%20-%20Swagger/branches
The first change is to tell the swagger file the exact type of data that it is expecting, therefore I’ve added “multipart/form-data” to what it consumes.


I then need to ensure that I have entries for both autograph and photograph. Unlike the other pieces of data being passed in, which are strings, I need to pass autograph and photograph as a file. Therefore, I’ve set the “in” property to be “FormData” as it’s being passed as part of the submission body, and also changed the type to “File”.


Once I’ve made the changes to the swagger file, I can update my connector by selecting to “update from OpenAPI file”.

Step 4: Azure Function

Azure functions are intended to be small, short running single task pieces of serverless code. Azure functions can be written using your preferred language, whether that is C#, nodeJS or even PowerShell.
The Azure Function will take some basic information from Flow which is passed through the body of the HTTP request. It will connect to the SharePoint site, locally copy the files that it needs (photograph / autograph etc.) and generate a new word document which it will upload back to SharePoint.
As I need to interact with the contents of a Word document, I’ve used the OpenXML SDK. I’m not able to use the Office Interop within an Azure Function as it needs Office to be installed locally. You can get the OpenXML assemblies using NuGet, as well as any other libraries which you might need such as SharePoint PnP Core which allows me to interact with SharePoint using the Client Site Object Model (CSOM).


You can find the complete code for the Azure function here:
https://dev.azure.com/MATTWESTON365/A%20Modern%20Autograph%20Book%20-%20Azure%20Function

Using the Custom Connector

Now that we have all constituent parts of our solution, we can start using the app to generate autographs, and see the whole process working. But just before we start snapping photographs, we need to connect the PowerApp to our Flow, using our custom connector. Our custom connector is added to our App by adding it as a data source.


We also need to add the formula to the save button to submit the data through the custom connector into Flow so it can be processed. To do this we will use the “OnSelect” and reference the name of our data source which will appear in Intellisense. I will then select my action, which I defined in my custom connector, and pass it the parameters that it wants the user to provide i.e. anything which we didn’t mark as internal when creating the connector. In our case, this will be the name of the person, the photograph and the autograph.

Using the PowerApp

There are numerous ways in which you can interact with your PowerApp, however, I’m going to focus on three key methods:
• SharePoint
• Teams
• Mobile device

SharePoint

If I want to use my PowerApp in SharePoint, I can add it as a web part on a modern page. When I edit the page and go to my webpart toolbox, I have a PowerApps web part which I need to provide with the App URL or App ID. Once I’ve added it to the page, the app will load on the page and will allow users to interact with it without the need to load PowerApps themselves.


The AppID or Weblink can be found within the “details” of your app, so you just need to copy either of these the WebPart properties and the autograph app will begin to render into the page. We can then use the Autograph App directly from the SharePoint page.

Teams

The experience of adding a PowerApp to Teams is much better than the experience of adding it to SharePoint. When we go into Teams and add a tab, we can add a PowerApp Tab which will allow you to select the PowerApp which you want to add. There is no need to provide IDs etc, you can simply click the one that you want the App can then be directly accessed from the Teams Tab.

Mobile Device

However, I really created this app for use on a mobile phone. We can install the PowerApps app on a phone (available for both iOS and Android) and then launch the Autograph app from there.

To make things even easier, rather than launch from within PowerApps, you can pin the App to your home screen. This doesn’t install it as an app in its own right, it simply creates a link for the app from the home page to launch it directly. To do this, use the ellipsis to the right of the app and select “Pin to Home”.

Then follow the instructions to add it to the home page, this obviously differs depending on the device, and it will create a shortcut for you to use (you can rename this if wished).

You can then use the app to take the autograph details. Click “Get New Autograph”, add the details, take a photo, review the 3 elements & click save.

After a few minutes, you can go back into SharePoint and review the final autograph document.

This Ebook was written from Matt’s session at the PowerApps Virtual Summit, you can still purchase a Pass to watch it and all the others.

May 28, 2019

The ultimate guide to using PowerApps with Zapier

In this article, I want to show you step-by-step how to call Zapier from within a PowerApp. Specifically, I am going to show you how a user could register themselves onto an event in Crowdcast. If you don’t know what Crowdcast is, it’s an online meeting service allowing you to run single events (such as a webinar), or even conferences such as we used for the PowerApps Virtual Summit. The example from a PowerApps point of view is going to be really simple, but with what I show, you can easily make a more advanced application using any connection with Zapier.

What is Zapier?

If you’ve not heard of Zapier then you’re missing out! Zapier is the most popular platform for connecting different services together. At the time of writing, it supports 1200 applications making it the largest service of it’s kind. It performs a very similar job to Microsoft Flow so you may be wondering why not use Flow? My advice here is that if Flow already supports the applications you want to integrate with, you’d probably want to use it. However, if you find that Flow is missing an application or that Zapier offers more flexibility in the way it can create your workflow then you might want to use Zapier.

Before I show you all of the steps, it’s worth taking a look at what we’re going to do. I am going to create a simple single canvas PowerApp that will take the first name, last name and email address. It will also have a button. When the form is filled in (and the button clicked), this information will be sent to Zapier. Zapier will then pass this over to Crowdcast to register the user on the Crowdcast event. As mentioned, this is really simple, but I am certain you will get a lot of value in how we connect PowerApps to Zapier.

What I explain here can be used in many different scenarios and massively increases the integration options when developing a PowerApp.

This article is meant to be a tutorial, allowing you to follow every single step. It should take you no more than 30 minutes to set all this up and have a working PowerApp form that submits a new event registration to Crowdcast.

Let’s get started!

Step1: Create an account, or login into Zapier

If you haven’t already got a Zapier account, you will need to grab one. They also do a 14-day trial. Here’s what it looks like when you’re logged in:

Step 2: Make a new Zap

What is a Zap? A Zap is a flow which is a set of instructions that allow you to create automated tasks, which receive data via Triggers. You can also choose to do things within your Zap such as looping or filtering the data. Once, the data comes in (and optionally manipulated), the Zap and then passes it onward to one or more other external applications, like Crowdcast.

At the top of Zapier console, click the “Make a Zap” orange button.

Step 3: Setup a Webhook to receive the data from PowerApps

In this Zap, we’re going to be receiving data from our PowerApp, which will then be passed into Crowdcast to register a person for our event. Select the ‘Choose App’ > ‘Webhooks’ from within your Zap.

If you don’t know what a Webhook is then you can think of it as being similar to a web page but usable via a machine (not a human). It’s a URL that’s constantly listening for input from another machine. In other words, it’s how two services can communicate with each other without human interaction.

 

Step 4: Choose a ‘Catch Hook’

There are 2 types of webhooks we can use in Zapier. The ‘Retrieve Poll’ will call a given URL on a given schedule to see if there’s anything new to add. This is ideal if you have a system that has data but doesn’t have the ability to push out. The downside of this is it’s inefficient. You’re forcing Zapier to “do work” even though nothing may have changed, which can also increase your cost.

In our case, we’re going to be using PowerApps to “push” the data so let’s choose ‘Catch Hook’. (When the button is pressed on our new PowerApps form, we will call Zapiers webhook).

Step 5: Click Continue as we don’t want to select any Child Keys

If we’re going to be sending data (FirstName, LastName and Email) into Zapier which is specific to what we need in Crowdcast, then we’re going to need to tell Zapier about that data. I am going to do this part using a tool called “Postman” so, for now, let’s just click “Continue” as we will come back to this part.

Step 6: Copy the Webook Url onto your clipboard

One thing we will need to do is copy the new Webhook URL so that we can use it in Postman to enrich our request with data. So copy the URL as below.

Now that we’ve got the Url on the clipboard it’s time to load up the 2nd tool of the day which is something called “Postman”.

What is Postman?

Postman is what is known as a REST client. REST is a way to request information from another site, and Postman is a tool that is designed to help design this process. It can be a very powerful tool when working with Microsoft products, and the output from the Postman project can also be used in most Microsoft applications, such as PowerApps. Here we won’t be using Postman to create the design however, we will be using it to generate the link we will need to program Zapier.

Step 7: Download Postman and Create a new Request

To complete this next step you need to download Postman from this page. Once you’ve downloaded you need to click on a new ‘Request’ because we’re going to be getting information from our PowerApp (to send into Zapier).

Step 8: Save a new Request

On the ‘Save Request’ Screen enter a ‘Request name’ and add a new collection called ‘Zapier’.

Once you’ve filled it out, click ‘Save to Zapier’

Step 9: Send a test over to Zapier

The next screen you will see is the ‘Workspace’ screen. This is a little tool that you can now use to make requests to test that the Zap is working. On this screen, you want to paste the URL that you copied from Zapier (in step 6) into the box next to the word ‘GET’. Once you’ve done that, click ‘Send’. You should then see the ‘Body’ at the bottom of the screenshot saying ‘status: success’.

Step 10: Add parameters into our request.

Now that we know that the request works, we want to start adding key/value pairs into the request. These values will allow us to send in the ‘FirstName’, ‘LastName’ and ‘Email’ from PowerApps into Zapier and then over to Crowdcast.

To do this (back in Postman), you need to use the form editor to add the parameters. Add a new key and value for FirstName, LastName and Email (as below).

Step 11: Retest that Zapier has picked up your changes.

Go back into Zapier and click “Ok I did this”. You should now be able to analyse the Hook (as below) to ensure that your 3 new values came through to Zapier. Click “Continue” if this looks ok.

Step 12: Create an event in Crowdcast

At this stage, we’re ready to create a new event in Crowdcast. So log in with your live account (trial accounts don’t work with the API at the time of writing this). Click “Create an Event” and call it something like “Zapier Integration Test”. You can also explore and tweak the other settings if you wish, however, they’re not really important for this test.

Step 13: Get your API Key from Crowdcast

Once your event is created, go to “Integrations” and copy your “API Key” to the clipboard, which can be found under the “Zapier” area of the page.

 

Step 14: Add Crowdcast into your Zap

Back in the Zap where you just created your new Webhook, you want to choose “Add a Step”, then “Action/Search” to find “Crowdcast”. Doing this will take the new registration that we’re going to fire over from our PowerApp and pop it into Crowdcast. This is also where you may need to enter your API key if you’ve not done it already.

Step 15: Choose the “Register Participant” action

At the time of writing this, there’s only one relevant Crowdcast action that we can choose called “Register Participant” which is perfect as that’s what we need! Select it and click “Save + Continue”.

Step 16: Configure the Zapier Template

The Crowdcast template (within Zapier) is very simple. It requires an “Email”, “Event Code” and optionally, an “Attendee’s Name”. For the emails, click the plus sign on the right of the Email box and selected your test email. To find your event code you will want to pop back into Crowdcast and browse the event you just created. You will notice that part of the URL is constructed by a code. For example, if we have an event URL of https://www.crowdcast.io/e/l4yxjmwf then “l4yxjmwf” is your event code.

To add the attendee’s name, click the plus sign and add the name in. Be sure to add a space in between the first name and last name.

Step 17: Test your Zap creates an attendee in Crowdcast

Click “Continue” on the “Edit Template” screen and then click “Send Test to Crowdcast”. Once you’ve done this, please navigate back to your Crowdcast event and look at the “People” section at the bottom of the screen. Now you should see that your test person is now attending your brand new event! How cool 🙂

Step 18: Copy the URL from Postman

Quickly pop back to Postman and copy the URL that you used to send into Zapier. It should now have FirstName, LastName and Email appended to the query string.

What is Apigee?

Apigee is a tool that allows you to describe your API so that it can be imported and consumed into PowerApps. You can do the same in Postman, but I find Apigee much easier to use. The Apigee team describe their service as follows:

You have data, you have services, and you want to develop new business solutions quickly, both internally and externally.

With Apigee, you can build API proxies—RESTful, HTTP-based APIs that interact with your services. With easy-to-use APIs, developers can be more productive, increasing your speed to market.

API proxies give you the full power of Apigee’s API platform to secure API calls, throttle traffic, mediate messages, control error handling, cache things, build developer portals, document APIs, analyze API traffic data, make money on the use of your APIs, protect against bad bots, and more.” (source: Apigee docs)

Step 19: Get an account in “Apigee”.

Once you’ve created a free trial account in Apigee click on “new project” and then paste in the URL you copied from Postman into the box next to the “GET” label, as follows:

Step 20: Setup the “Program Info” tab.

On the Program info tab, add a Title, Description and version to your API.

Step 21: Setup the “API Info” tab

On the API Info tab give your “Operation Id” a name like “SendToZapier” plus add a summary of your choosing. Then click “Next Step ->” until you hit the “Open API Spec” tab.

Step 22: Setup the “Program Info” tab

We’re pretty much done now with Apigee. All we need now is to click the “Download Spec” and save the JSON file locally on your hard drive. Remember where you saved it as we’re going to need it in PowerApps.

Step 23: Build an app in PowerApps

Now we’re getting to the good part :). In this last section, we’re going to build a very simple UI that will take the first name, last name and email. It will also have a button that (when clicked) will register the new person in our Crowdcast event (by calling into Zapier).

The first thing you need to do now is to click custom connectors and choose “Create custom connector” and choose the option to “Import an OpenAPI file”. The format of the JSON we just exported was built against the OpenAPI specification so it will import into PowerApps just fine. [Read more on custom connectors].

Step 24: Load your JSON and name your Custom connector

In the dialogue that appears, you need to “import” the JSON file that you downloaded from Apigee. Give your custom connector a memorable name.

Step 25: Configure your custom connector

In this step, you may choose to give your connector an icon and some branding to make it easier to be identified in the future. For now, we will take the defaults and move onto the Security tab.

Step 26: Choose “No Authentication” for your security model.

The authentication type model we’re going to use is “No authentication”. The security model that we’re going to use is sufficiently secure as it’s secured by obfuscation meaning that it’s virtually impossible to guess the Zapier URL. However, you may find, depending on what you’re doing with Zapier that you need to explore better security models. As we’re just registering people on our event we are willing to secure this by relying on an obfuscated URL.

Step 27: Build an app in PowerApps

In the “Definition” tab you don’t need to change anything, but you do need to check that “FirstName”, “LastName” and “Email” are showing. Also, make sure that the response is working and valid (at the bottom of the tab).

Step 28: Test that the connection works

On the last step of the wizard, you will be testing that your connection to Zapier works and that you can add a new person into Crowdcast. To do this click “+ New connection” as below.

Step 29: A new connection is now visible

Verify that you can see your connection under “Connections”.

Step 30: Edit your connection

Now the connection is set up, click the pencil so we can continue with the test.

Step 31: Carry on with the test

Go back into your custom connector and make sure you are on the “Test” tab. You will now see the connection that you created is already selected under “Connections”. Add in a “FirstName”, “LastName” and “Email” and then click “Test operation”.

Step 32: Check for test success

Look at the “Response” section and verify that you get an “OK (200)” and a “status: “success”” in the “Body”.

Step 33: Also verify your user made it into Crowdcast

As a “belts and braces” check, go back into Crowdcast and verify that your new user is registered as a participant under “People”.

Step 34: Create an app from blank (in PowerApps).

By this stage, we know that our custom connector is working and sending people into Crowdcast. Now it’s a simple job of creating a simple PowerApps app to build a UI so that a PowerApps user can register a user as they wish.

Select the “+ Create” option from the left-hand menu and choose “Canvas app from blank”.

Step 35: Choose a “phone” optimised app

On the dialogue choose “Phone” and call it “Zapier Integration Demo” and click “Create”.

Step 36: Choose a Data Source.

Under the “View” menu select “Data sources” and click “+ Add data source”.

Step 37: Choose our newly created connection

As we’ve already set up the connection you can now select it from the options, as below.

Step 38: Build the UI!

You now need to add 3 text boxes and a button as shown below. Give each text box a meaningful name such as “Email”, “LastName”, “FirstName”.

Step 40: Now create the only line of code in this entire solution

Select the button and add a line of code like this:

>> OpenAPIZapierAPIProgram.SendToZapier(FirstName.Text, LastName.Text, Email.Text)

Step 41: Congratulations – your PowerApps is ready!

Well done your PowerApps is now ready to use, so you can now run it and make sure it adds a new participant when you click “Submit”. At this point, you should now be able to register new people on your Crowdcast event using PowerApps to gather the data and Zapier to send it over to Crowdcast!

Summary

Although the steps outlined above might appear like a lot, if you break it down, quite a chunk of this is testing that each step works. This solution only used 1 line of code, which was to wire up the button to call Zapier. Although this process utilised Crowdcast it’s just as easy to connect PowerApps to many other services that Microsoft Flow doesn’t already utilise which, at the time of writing, is around 600!

If Flow already supports your service then the recommendation is to utilise that, but if you want to easily to connect to a service that Flow doesn’t support then Zapier is an excellent choice!

Hope you enjoyed this in-depth tutorial and are now more confident in how to use Zapier with PowerApps!

May 16, 2019

What is PowerApps? The complete introduction

PowerApps is part of Office 365 and allows power users to create business applications in as little as a couple of hours. In this article, I’m going to show you how to start learning the basics of PowerApps. Once you’ve read this post, check out the 10-Book PowerApps Bundle for $19 (for a limited time), this will really accelerate your PowerApps learning.

How do I start developing a PowerApp?

PowerApps development can either be completed in PowerApps Studio, which is a downloadable client application or directly inside your browser.
Previously, PowerApps Studio had much more functionality, however, at the moment the browser has caught up and has virtually the same features. The browser version has also become much faster to use than ever before.

Start with a template …

My recommendation, if you’re just starting, is to create your first PowerApp from one of the templates included in the product.

Initially, don’t worry about how your PowerApp will look. Just click on the “create” button (on the PowerApps start screen) and pick a template to use in your business environment. Even if there is no need for you to use this business application, it can be an excellent way to learn about what’s possible and share ideas with your team.

Branding and building for mobile devices

You will notice that some applications in the templates section have multiple design modes. In the example of the Estimator Pro PowerApp, there are two design modes: the phone factor and tablet factor.
A phone factor doesn’t mean your PowerApp cannot be displayed on a PC or tablet; it means it will work optimally for a phone (having a small rectangular design).

It is up to the PowerApp creator to choose the design mode, the orientation of the PowerApp (landscape or portrait) and also consider whether the aspect ratio and orientation should be locked.

I am sure you will agree, this is a great start! In just a few clicks it gives you, the creator, a working PowerApp. However, keep in mind that this is only the start of your PowerApp creation. The branding and responsiveness factors are up to you.

Next to the “orientation” option, the user can also choose the required “size” of the target device.

Explaining The PowerApp User interface

The PowerApps interface looks as follows, where each section has its purpose.

The first section (top) is the simplest to describe because this is the most familiar one: the ribbon. Microsoft introduced it in Office 2007, and since then it has never left. Now it is also available in the PowerApps designer and allows you to achieve the same functionality that you are used to in Excel or Word. A few examples of what you can do are underlining text, aligning content and setting formulas on your objects (in comparison to formulas on cells in Excel).

If you are used to Excel, there will be a mind-shift you will have to go through when creating mobile applications instead of creating Excel worksheets. The most significant difference is that you will have to start thinking about “screens”. These will be shown in the second section (bottom-left). The first screen in this section will be your start screen with subsequent screens displayed in order below. To change the order of these screens, simply drag them up or down to your preferred location, think of it being similar to the slide sorter in PowerPoint.

In the third section (bottom-middle), your PowerApp screens are displayed. You can use this section to select your controls (and afterwards setting formulas on these) or to simply drag objects to another location.

In the fourth section (bottom-right), you can define the attributes for the selected control, like in the formula bar. You will see there is quite some overlap between these two sections. Some attributes can only be selected in this section though, for example connecting to your data and picking the correct data layout template.

Branding your PowerApp

When branding your PowerApps, I recommend that you have a consistent brand and colour. Be sure to set your colour only once (for example on the top bar of the first screen). On the other screens, don’t copy this colour. You should copy the attribute (“fill”) from the first screen’s top bar. Using one value for your colour is how you maintain consistent branding throughout your PowerApp.

In the screenshot above, I have set my fill to a colour, then referenced it as shown below:

Formulas and Attributes

PowerApps contain many formulas and attributes. Formulas are always coupled to an attribute; attributes are dependent on the object you have selected. For example, a screen will have attributes such as BackgroundImage, Fill, ImagePosition, LoadingSpinner and LoadingSpinnerColor allowing you to set some design preference. It will also have some start handlers like OnHidden, OnStart and OnVisible allowing you to execute some actions like refreshing data sources etc.

Other objects will have different attributes like OnSelect where you can define what should happen when you click the object.

Objects that allow you to show data will have the attribute Items where you can define your correct data source.

Top 10 Formulas

There are around 150 formulas that you can use with PowerApps, details of which can all be found on Microsoft’s documentation site. I asked our PowerApps group on Facebook which ones they used most frequently and listed them below.

  • If: Determines if an expression evaluates to true. If it is then a specified value is used, otherwise, a default value is returned.
  • SortByColumns: Allows you to sort a table by one or more columns.
  • Sort: Sorts a table based on a given formula and sort order.
  • SubmitForm: Saves the contents for a form to the underlying data source.
  • Filter: Allows you to filter a set of records based on a given formula.
  • Search: Allows you to search for a set of records based on a given search query.
  • UpdateContext: Allows you to store any useful value in a context variable. Scoped to the PowerAppsScreen.
  • Set: Similar to UpdateContext only this time the variables stored are globally scoped.
  • Lookup: Finds the first row in a table matching a specified formula. Returns a single record.
  • ClearCollect: Clears all records from a collection and then adds a different set of records to the same collection.
  • UpdateIf: Update a value if a condition is true.

Connecting to external data

PowerApps has great support to connect to data from other systems. There are already more than 180 connectors available. Examples of the most common ones are shown in the screenshot below:


Connecting to on-premises data

It’s also possible to leverage data that’s stored in your on-premises data stores. This is achieved by using a gateway. To set up a gateway you will need to use a data source from one of the following: SQL Server, SharePoint, Oracle, Informix, Filesystem, DB2. The process for configuring and managing your gateway can be found in this article.

Connecting to data in the Common Data Service

Microsoft PowerApps can utilise data via a service known as the Common Data Service. In Microsoft’s words – “The Common Data Service (CDS) for Apps lets you securely store and manage data that’s used by business applications. Data within CDS for Apps is stored within a set of entities. An entity is a set of records used to store data, similar to how a table stores data within a database.”

The poster below gives an overview of which entities currently exist within the common data model.

Do more in PowerApps with Microsoft Flow

PowerApps allows you to create mobile applications easily and quickly. Along with your mobile application, you will probably also need some automation to be done in the background, like for example, sending e-mails when a user clicks on a reservation button. Simple tasks like this can easily be done in PowerApps, however, when more advanced logic is needed, Microsoft Flow offers more flexibility to handle this.

PowerApps integrates very well with Flow, and they make their connection via the PowerApps and Flow buttons, each one allowing the other to be started. You can even use MS Flow to handle some PowerApps maintenance and governance.

Using PowerApps on a mobile device

To run a PowerApp on your mobile device simply install the PowerApps application via the Google Play Store (for Android devices) or Apple App Store (for Apple devices). The first time you start the PowerApps app, it will ask you to sign in to your organizational account. When this is done, all your company apps will be displayed here (at least all the ones that are shared with you). If you have a couple of apps that you use frequently you can add them to your home screen (pulling them out of the PowerApp app) – however, this functionality only works on Android at this moment.

The screenshots below are sourced on the Google Play store (provided by Microsoft) and give a great example of what a PowerApp looks like on a mobile device.

If you don’t want to install another app, then don’t worry, you can access them via your browser.

Going Offline with PowerApps

One of the major use cases for PowerApps is to allow workers to use apps on the road. One common scenario for a remote worker (such as a travelling salesman) is that they may not always be connected to the internet.

You will be pleased to know that PowerApps does offer some support for working ‘offline’. To build support in our PowerApp to handle offline data can be achieved by utilising a few useful expressions as follows:

  • Connection.Connected: Allows us to test if our PowerApp is connected to the data source. If it’s isn’t then we need to save and query the data from the local storage (on the mobile device).
  • Clear: This will remove records from your collection.
  • Collect: Allows you to store records in the local cache, if we have no connection.
  • LoadData: Allows you to load data into a collection locally.
  • SaveData: Allows you to save data to a collection locally.
  • Patch: Updates/creates a record in the data source. Ideal if you have a connection.

As you can see, offline support can be quite a complex subject if you’re not a developer by trade, however, once you’ve grasped the concepts it’s quite straightforward.

How to support multiple languages

It is entirely possible to create a PowerApp that renders text in the language of the logged on user. Note: If you think your PowerApp may need to be multi-lingual in the future then it’s wise to provide support from day 1. Delaying it will result in you have to refactor every label, text, tooltip and message which can be a painful job.

Before we do anything, we need to work out what language the user uses. This can be determined by calling the “language” function. “Language()” returns the “language”, “script” and “region” for the current user as a tag, e.g. “en-GB” would return for someone living in Great Britain.

The golden rule when adding support for multiple languages in your PowerApp is not to hardcode text. Instead, you should use a function that looks up the language from a local data set that stores the key name and string. Your dataset can be imported from something like Excel and would be in this format.

Language Key StringName
en SaveTooltip Save the customer
pt SaveTooltip Salve o cliente
fr SaveTooltip Sauver le client

 

Now that we know which language the user needs and have a language dictionary, all we need to do is perform a lookup like this (note, you will more often than not store the user’s language somewhere locally):

LookUp(LanguageDict, Key = “SaveTooltip” And Language = “en”)

Summary and other useful resources

So in this article, I’ve shown you how to get started, given a brief tour of the PowerApps interface and explained some of the more popular formulas that you may want to use. We moved on to look at how to connect to external data sources, data held on premises as well as data in the Common Data Service. We also considered the possibility of linking with Microsoft Flow and using PowerApps on Mobile devices along with how to cope when the user is offline or out of the office. Finally, we looked at how to provide information to the user in their local language.

If you’d like to find out more, then (along with Laura Graham Brown) I’ve written a Beginners Guide to PowerApps e-book that you can download from here

Alternatively, why not join us for the Collab365 PowerApps Virtual Summit on March 27th where Laura, myself (and 8 other PowerApps experts) are presenting sessions showcasing the use of PowerApps and hopefully providing some inspiration to help you benefit from these amazingly powerful features.

March 12, 2019

What is the future of work and what will it mean for your job?

The way we work is changing. Not only is it changing, but it’s changing at an incredible pace! What we are used to today will look very different in 5-10 years and beyond. Many factors are driving a change in the normal 9 to 5 working hours, and I believe this passionately. I believe it so much that I spent two years of my life developing a service that will help facilitate the changing work landscape. (More on that later…)

I realise that some of you may disagree with my points listed below and that’s fine. However, regardless of whether I am right or wrong, you can still introduce a few changes to safeguard your future.

I am a great believer in planning for the future. I have always taken an interest in technological advances and wonder what impact they will have on me, my family and those around me. I also think it’s wise to make sure we’re able to end our careers and be able to enjoy a long and happy retirement.

If you’re an employer, you will also need to consider how these points will affect your workforce and in particular, how you’re going to attract and retain the staff you need to run your business successfully.

Let’s get started, the remainder of this article sets out nine factors or predictions that will affect our jobs in the future.

1 – Technology can now easily support remote working

If two people want to work or collaborate remotely then, regardless of the specific task they are completing, you can be sure that they will need to contact each other. One of the most significant requirements for carrying out a project is to be able to use great software to facilitate regular meetings and conversation as well as tools to help manage the full project life cycle. Independent workers also require a variety of systems to help manage their customers and business.

Up until the last 3-5 years this technology wasn’t available or was limited in some way – remember the ‘good old days’ when you actually had to physically write a letter and walk to the post box or actually pick up a telephone and dial! How many of us now use some kind of instant message / online chat / video link – even when the ‘remote’ worker is simply located in a different corner of the same building!

Here are a few hand-picked solutions to aid project co-ordination and collaboration.

Online meeting/chat/collaboration software

Online web conferencing and meeting software used to be either expensive, flaky or slow due to reduced domestic internet speeds. Now we have a flurry of meeting software and fast internet speeds are becoming the norm.

Here are some great tools to help run an online meeting or conversation:

  • Microsoft Teams – A tool allowing a Team to chat, collaborate, share and meet online.
  • Slack – This performs the same role as Microsoft Teams.
  • Google Hangouts – Developed by Google and is a free way to run an online meeting with multiple attendees.
  • Zoom – A paid meeting/webinar tool, but packed full of features.

Project and Task Management tools

To be able to manage a project’s progress, you need to be able to break it down into individual tasks, allocate them across the team and then review progress against expectations. Trello changed everything for our business.

We run Collaboris entirely remotely and use Trello on a daily basis – allocating team members to an individual task ‘card’ and then moving the card from left to right across the project board as its status changes (initial idea / approved / in progress / ready for review / complete etc.). Microsoft also has a Trello equivalent called Microsoft Planner that’s bundled in with an Office 365 subscription.

Here are some great tools that can help you manage a project:

  • Trello – Manage tasks in a Kanban style board.
  • Monday.com – Plan. Organize. Track. In one visual, collaborative space.
  • Microsoft Planner – Developed by Microsoft and remarkably similar to Trello.

Small business, solo-preneur tooling.

Every person wanting to become a Freelancer or small business needs to be able to manage documents, contracts, scheduling and tasks to run their business successfully. Office 365 can be used for as little as £6/month. With that, you get access to an eye-watering amount of features.

Here are some other great tools to help manage your business:

  • ClickFunnels – Amazing software to help you build a marketing funnel to attract new business (great training too).
  • QuickBooks – Accounting software to manage your business.
  • HelloSign –  Get legal documents digitally signed.

I won’t list every conceivable tool that you could use to run your own business from home as there are simply too many. Rest assured you can get your business up and running for a few dollars a month instead of having to pay hefty up-front fees for expensive software.

2 – Remote access to organisations data and systems is easy

Business is now flocking to the Cloud at an incredible rate. We are continually hearing incredible statistics from Microsoft about Azure, and some believe it will be bigger than Windows by 2021. Let that sink in for a moment. That means Microsoft will generate more money in its Cloud business than selling on-premises software (mainly Windows). Amazon (AWS) has an even bigger marketshare with Google (Google Cloud) just behind Microsoft. I believe this trend will continue until we’re all in. Some will need to resist due to regulations or data sovereignty issues, but the rest of us will be “forced” to the Cloud so that we can keep our costs down and enjoy the latest versions of software too.

So why does it matter that we will all be in the Cloud?

This matters for many reasons, but the main one is that remote workers can now access organisations systems with ease. Just ten years ago, if a company in the UK wanted to hire a person in a different country (and have them access their system/data), it would be a massive challenge. All of the data was locked away inside a corporate on-premises data centre where you had to be on-site to access it. Some companies would open up their data with a VPN, but this would often be resisted by the I.T., compliance and security teams.

SAAS solutions (like Azure and Office 365) have made it easy to grant access with granular permissions to 3rd party global workers. Nearly all Cloud services make it simple to add users often with social logins like Facebook or LinkedIn.

3 – Business users are now unleashed with a new breed of tools

Traditionally, even for simple requests, business users would have to raise a request to the I.T. department. I.T. would then evaluate it and decide how and when to deliver it. Often this took a long time as I.T. had other projects on their books. In many cases, the business would want I.T. to upgrade on-premises software as they required the new features. Depending on the software this might be very expensive, time-consuming and risky.

In the new Cloud-enabled world we live in today, the Cloud vendor is continuously patching and releasing new features directly to the business. This is a massive change in itself. Rightly, or wrongly business users can now build on top of their cloud service and integrate it with other services without I.T. even being involved. Vendors such as Microsoft are also releasing “power user” tools (see PowerApps, Flow and Power BI) that allow power users (often referred to as citizen developers) to build entire applications themselves.

What are the opportunities here?

As these new tools and “mini projects” arise business users are going to need training and support to adopt them. This support will likely be requested externally as internal teams don’t have the skills to keep up. Tools like PowerApps put a lot of power in your average Joe’s hand, but they’re still going to hit stumbling blocks. In other words, if you position yourself as a remote worker who specialises in this new niche, you can create a business serving customers all around the world. To become a successful Freelancer, I believe it’s better to specialise in one thing well rather than have to be a generalist like so many are having to do as contract and permanent workers. Being a generalist can cause you massive frustration in just trying to keep up!

4 – The new generation expects flexibility

I am a dad of two teenage daughters and uncle to a 20 and 22-year-old. The life they’ve experienced in their early years is dramatically different from the one I did. Instead of endless nights playing on the parks, going to friends houses, they’ve “mostly” communicated via text messages in their favourite social apps such as Snapchat, Instagram and Kik. Their generation communicates far less than we did.  They expect everything to be done via the phone or with their mates on a gaming console. They’ve used mobiles, tablets, Netflix instead of bikes, marbles and conkers.

Through no fault of their own, this momentum is set to continue (and to be honest I can’t see it ever being reversed). All of this means that their expectations from a “job” are very different from the ones I had when I began my career. When I started, I was taught that a “job is for life”. The new generation expects the tools, convenience and flexibility they’ve grown up with.

The video below explains five trends that are shaping the future of work.

What are the impacts to business if the generation entering the workforce now expects to work remotely?

Many businesses are already dipping their toe in the “gig economy” and utilising online Freelance workers (especially in sectors such as I.T., design, sales and marketing). If there’s enough opportunity for the new breed of worker to work as a solo-preneur they will take it, meaning you may miss out on some fantastically skilled workers.

If you’re unsure how big the Gig economy was in 2017, then have a read of “Freelancing in America: A National Survey of the New Workforce –  “This workforce is adding $715 billion annually to the economy through their freelance work“.

5 – Climate change will limit the commute

Let’s face it; our planet is facing a massive crisis. We’ve got ice melting that really shouldn’t be. Species are dying off. The earth needs some urgent T-L-C. Every day there’s a new story breaking by a leading scientist saying that we have just a few years to reverse this horrifying trend.

As I write this a news story just broke that negotiators in Poland have finally secured agreement on a range of measures that will make the Paris climate pact operational in 2020. In other words,  most of the world has agreed to lower global warming by adhering to a standard set of rules — fantastic news. At last, we’re doing something about it.

How could the climate agreement affect us as business owners?

This is a big deal. To lower our emissions and stop the burning of fuel we’re going to need to stop spending millions of hours per day in cars, trains and planes travelling to large offices which burn even more.

To follow it’s a natural conclusion if governments follow through with their promises to reduce global warming, they’re going almost certainly need businesses to lower their carbon footprint. This will inevitably mean less business travel and less time in fuel burning offices.

6 – We now expect an “uberized” world

One other way to predict how we will work tomorrow is to look at the trend that’s happened in recent years. Services like Uber and AirBnb have made buying and selling services as easy as buying a pair of socks on Amazon.

It couldn’t be easier. Pick up your phone, find a house, (or taxi driver in Uber’s case) and click a couple of buttons. They’ve disrupted entire industries and have triggered a new generation of marketplaces to pop up in every industry imaginable. An example of where we utilised a marketplace at Collaboris was when we needed some legal documents writing for MicroJobs. We had no prior relationships with lawyers and were also conscious of cost. Within less than 30 minutes we’d hired someone from Lexoo.

Recruiting staff is frustrating, risky and potentially expensive – especially for smaller companies or where the task only requires someone for a few hours/days. You create a job spec, use an online site or hire a recruiter. The process can take an age and even then you risk hiring the wrong person.

Expect recruitment to be “uberized”.

The traditional process of recruiting staff is prime for disruption. Just as we have with the Uber app, we are going to see a flurry of services like ours (Collab365 MicroJobs) that make it easy to find staff. Like we have done for experts with Microsoft skills and Lexoo with lawyers, I expect we will soon end up with a lot of niche marketplaces that are set to serve one particular industry or vendor. As these sites grow and gain momentum, they will eventually make recruitment a far more enjoyable experience. They will usually have robust payment flows that protect buyer and seller and also offer reviews making you more confident of your new hire.

7 – AI and Robotics could automate your job

Another massive trend we see in technology is Artificial Intelligence (AI) and robotics. AI is already huge. Each of the major tech companies has invested heavily in it.

Towards Data Science has gathered some interesting statistics on AI, here are a few:

  • The share of jobs requiring AI has increased by 450% since 2013, per Adobe.
  • According to BridghtEdge, the next significant marketing trend is seen as being consumer personalisation (29%), followed by AI (26%).
  • Around the globe, robot imports have increased from around 100,000 in 2000 to roughly 250,000 in 2015, according to IDC.

You can read the full report here

A recent post on Brugel, the European ‘think tank’ specialising in economics stated that:

Based on a European application of Frey & Osborne (2013)’s data on the probability of job automation across occupations, the proportion of the EU workforce predicted to be impacted significantly by advances in technology over the coming decades ranges from the mid-40% range (similar to the US) up to well over 60%.”

The report goes on to explain that the net result would be 54% of EU jobs being at threat of automation.

Although some of these figures sound shocking, there are also studies that suggest that the workforce will merely shift in focus and that with the relevant reskilling and training opportunities for employment will come from different areas and different sectors. This will make the actual figure of jobs that AI, automation, and robotics displace much lower.

In fact, according to a recent report from the World Economic Forum, the shift in the workforce could result in a net gain of 58 million jobs in the next five years.

This candid video is well worth of a watch. It explains the “future of work” really well.

I think if you’re working I.T. – you’re lucky. Demand for developers and engineers are set to rise. However, the industries you currently work in may be impacted by automation. My advice here is to keep an eye on the future and keep your skills relevant. Remember my point above, become a specialist and sell that “one thing” over and over, rather than become a jack of all trades (master of none).

8 – Companies can’t find staff (in I.T. anyway)

A study by Korn Ferry Institute finds that by 2030 there will be a global human talent shortage of more than 85 million people which could result in ~ $85 trillion unrealised annual revenues globally.

While those figures are across many industries and Job skills, IT skills are always close to the top of that shortage list globally. The innovative and fast-paced IT landscape means that there is a constant shift in ‘Goalposts’ when it comes to demand meeting supply.

This skills gap coupled with the changing attitudes and expectation of the modern worker makes for some changes in focus and working cultures across the industry. These include investments in training, adjusted and flexible working patterns, alternative resourcing and Outsourcing models, to name a few.

If there’s a skills shortage then surely it’s going to be easy to find a job?

Maybe. I think it will depend upon where you live. Sadly, we are seeing a trend towards protectionism. We’ve got the USA touting “American Jobs for American people”, and the primary driver behind Brexit was to lower immigration and protect UK jobs. All of this sounds logical. Fewer immigrants mean more jobs, doesn’t it? Not necessarily. The main reason we utilise the skills of foreign workers is that businesses can’t find people or afford to hire local workers. If they can no longer hire relocated workers from abroad, then projects will have to be completed elsewhere or cancelled. If they can’t afford local workers then pay levels will have to drop. This is the reverse of what those who wanted to lower immigration campaigned for.

I also see a big opportunity here. If a company can’t recruit a skilled worker from abroad to work locally in their office, they can still hire them to work remotely. For those that offer their skills in markets that are becoming more closed off this could lead to a prosperous few years 🙂

9 – Cloud service integrators wanted!

As organisations move their workloads to the Cloud, they’re going to be faced with many options around which provider to select. In each business area, we have almost unlimited choice. For example, I could list five services in each of these areas: CRM’s, Helpdesk’s, marketing apps, task management apps and collaboration tools.

Each business will end up utilising several vendors to provide the tooling they need for their company to operate.

Services like Zapier, IFTTT and Microsoft Flow have sprung up in recent years to help solve this. For example, in our business, we use Zapier to hook up our website to our CRM through Zapier. We’re able to connect systems from different vendors together in a few clicks. This is amazing as we all like choice. It forces competition which either forces the price down or services offering up. As consumers of this, it’s fantastic.

However, with all this choice there’s a problem (and an opportunity).

Business will need to find and hire people that know how these services are meant to work and can guide them through it. If they were to do this alone, they’d need to evaluate every service, then learn how to use it. Then bombard their team causing distraction and information overload.

For the worker, there’s an opportunity to specialise in a particular product or in an integration tool like Zapier. Once you’ve mastered your specialism, you can then offer it remotely around the earth.

As an employee how can you secure your future?

Given the way the workplace is set to pan out I have some candid advice for both the worker and the employer:

My advice for workers: First and foremost is to decide where your passion lies. If you follow your passion (in your work), this leads to happiness. For example, I desire to help those people that want to build a flexible life, working from wherever they want. The review below was for one of our first ever MicroJob sales. I keep looking at it as it makes me smile. I love the fact that we’ve built a platform that’s connected two people who didn’t know each other from different areas of the world. My goal is to help 1000 people become full-time Freelancers, and that’s what drives me to get out of bed every morning.

Once you’ve found your passion look for opportunities to help others and once you’ve noticed that opportunity, become an expert and offer your skills on a platform like ours. Quick plug: If you have Microsoft skills, then we’d love to have you on board. If you don’t have Microsoft skills but can offer services (such as marketing, sales, graphic design) to companies operating in the Microsoft sphere then we also have “Microsoft partners” category.

Don’t give up your day job just yet, consider working a couple of extra hours in the evenings until you’re established and confident that freelancing is the life for you. You can also gauge demand by offering different types of jobs requiring different skillsets. We have a great post here with good advice on how to create the perfect MicroJob.

Employers need to prepare to resource labour from around the world

My advice for employers: In the next few years your business will no doubt go through significant change. You will have competitors popping up who’ve built a business from their bedroom by utilising low-cost Cloud services. You can’t rely on finding staff as quickly as you can today (has it ever been easy?). You need to start building an army of remote workers who you can call on when you need them most. If you’re working on Microsoft projects, then look at our marketplace to find someone. If you’re not, then many other sites offer a much broader range of skills such as Freelancer.com and Upwork.

If you are a business in need of Microsoft skills, then you can post a Job Request. If you’re more of a shopper then why not browse some of our existing MicroJobs.

Hope you found this useful 🙂

December 16, 2018

How to build a successful career in I.T. when you have no skills and no job

This article is aimed at young people (aged 16-25) who are still wondering what career path to take and whether to get into a tech career. The reason I wrote it is because I’ve chatted several times with my nephew and niece, my children and more recently to my nephews mate on a recent birthday celebration (or “sesh” as they were calling it!) and realised they all had similar questions, doubts and reservations about their future.

The exact question I received on FB Messenger (I don’t use Snapchat ;)) was this :

Hi Mark. How are you? We briefly spoke the other weekend about IT. I’m interested in a long term career in IT, specifically networking. What advice or qualifications would you advise for moving into this field of work? I apologise that it’s quite late on a Sunday buddy.

Choosing the correct career can be really daunting! My 15-year-old daughter (Hannah) is in her exam year and she’s still very unclear about what she wants to do. The latest indication is that she quite fancies a tech career. They’ve been doing the basics of programming with Python at school and she’s really enjoyed it.

Firstly, before I get started, a brief introduction into my back story…

If I am being honest, I chose IT in the first place because my dad had done it all his life and at the time I really enjoyed copying 10 pages of code, line by line, word by word from a trendy magazine of the time called “Input” into my Commodore 64. They were simple, happy days!

Back in the early 1990s when I was starting out, the Internet hadn’t taken off, data was stored on “floppy disks”, games consoles mainly consisted of a single joystick and 2 buttons and mobile phones were generally the size of a house brick. How times have changed in the last 25 years!

In my career I’ve been lucky enough to gain experience with 3 different roles:

  • I started out as a developer (beginning with ‘Basic’) and specialised with Microsoft technologies.
  • I’ve run a business, which means I’ve tried everything from graphic design to sales, mainly because someone had to!
  • I’ve got pretty good at marketing, specifically “digital marketing”, which really means marketing done online.

Looking back I’ve often wondered if I could choose again, would I have taken the same path? In all honesty, knowing what I know now, I think I’d probably go with marketing. So why did I end up in IT? I think the main reason was that I simply didn’t know that Marketing existed at the time – I’d had no real exposure to it and (at the time) the IT environment was developing so fast I was carried along with the flow. Thinking about it, unless you’re lucky you’re never going to find your true passion from day 1, however, we’ve all got to start somewhere!

Current careers that I think have limited future opportunities!

Now this is a little ‘crystal ball’ as it may not all come true. However, I believe the writing is on the wall for many jobs due to massive advances in technology. Jobs that are the norm today will be gone in 5-10 years. Maybe sooner, maybe longer, but they certainly won’t have the longevity to take you through to retirement. More and more jobs are becoming automated with technology providing the potential for robots to replace real people (they can work 24/7 and rarely complain). Robots also have the potential to repair other robots providing a double-whammy effect on the working population. This is bad news for some careers and professions, as it means if your job gets automated (done by a machine) then you’re going to have to learn new skills and jump career at a time when you might have your own family and financial strains.

This BBC article is awesome and reported on the threat of automation 2 years ago. It allows you to pick your chosen career and will give you a probability of it being eaten by computers.

Examples:

  • Retail – Amazon is changing everything. In the U.K., we’re seeing high streets really struggling. Big department stores and the old power-houses of town centres are shutting down. Supermarkets are moving to self-serve checkouts. Amazon are already trialling a store with no staff!
  • Car Industry – Look at how Uber has disrupted the taxi driver industry. Add in driverless, electric cars and we’re probably going to end up with people not owning cars. So that’s the entire car industry struggling in as little as 15 years. Check out this more recent BBC article Why you have (probably) already bought your last car
  • Finance – A new technology called Artificial Intelligence (AI) is also growing really fast. This has the capacity to take in 10s of 1000s of data points and to provide you with a result in less than a second. In fact, when you search Google, you’re probably using AI. AI can make decisions far faster than any human, so if your career has lots of number crunching or analysis, there’s a good chance AI will do it faster.

As one career dies, another is born!

Now one thing to note and (it’s just been pointed out by my daughter’s boyfriend, Alex). As jobs get automated this leads to more opportunities. Take Amazon’s “Alexa” for instance, she didn’t exist 10 years ago. It’s an entirely new way of interfacing with computers. With that new tech there are now 1000’s of opportunities. So many manufacturers are now utilising Alexa in their products. You could write your own skill for Alexa and sell it on the marketplace.

When driverless cars gain mass adoption, this will create new opportunities. Car layouts will change and become more leisure experiences, so expect a load of “in the car” entertainment and working devices.

One way to think about it is this: “What products and services will be needed for this?”. There are always going to be some.

So what careers are more future proof?

The most common theme (about all of the predictions above) is that technology is causing a huge disruption to many industries and it will continue to do so. If you are uncertain about what you want to do, then something with a technical nature is not a bad option. Technology will soon be ingrained in everything from clothes to glasses, wallpaper to windows, cars to drones. I bet we will also see it being used in the human body in my lifetime.

If you already have a massive passion to change the world in some way or help a certain group of other people, do that. Doing something you’re passionate about keeps you happy and fulfilled. Just keep one eye on tech advances and try to look for those opportunities.

Anyway, let’s get back to the original purpose of this article, how to ‘get into I.T.’ (in this case Microsoft) and what to do?

Now, I’ve hopefully given you a little food for thought, one of the best points about I.T. … You’re spoilt for choice. Getting ‘into I.T.’ doesn’t necessarily mean you need to stay up all night writing code. There are so many angles to take as it’s literally used everywhere and in everything.

Do I need a degree?

I.T. generally isn’t like some other professions such as training to be a lawyer or doctor where a degree and fixed learning path is mandated. A degree is by no means necessary. In fact, unless you go to one of the specialist universities then you could well find that some of the IT skills you learn at University are already out of date. Some companies run graduate trainee roles or specify that you need a degree qualification, so it again really depends on what you want to do.

Would I do a degree if I had my time again?

If I had my time again, I probably wouldn’t do a degree. I’d go down the apprentice route and learn up to date skills from day 1. My degree taught me quite a few things that were useful to my initial careers such as Object-Oriented design and database fundamentals.

It also taught me a whole load of rubbish.

Technically speaking, I left knowing very little real-world tech and learnt far more ‘on the job’. I.T. is pretty unique because it changes so often, so things date fast. The fundamentals are usually the same but there are 1000s of courses that will teach you what you need and an apprenticeship gives you experience, fast.

What A-Levels should I choose?

To pursue a path in I.T., then an I.T. related A-level makes a lot of sense. I’d also intermingle it with something like business studies. Being able to understand how a business operates really helps.

Have you considered a Microsoft Apprenticeship?

You may not know but Microsoft offers their own form of apprenticeship programs which are beneficial to both you and the employer.

Anthony Pounder, Director for a Microsoft services company called Intelligent Decisioning has employed many apprentices and had this to say:

The MS Apprenticeship scheme is a Microsoft and U.K. government scheme geared towards getting young people into IT jobs.

The way it works is that the apprentice is either found by QA (the training people) or, you can find your own and put them through the apprenticeship. The IT infrastructure apprenticeships take about 13/14 months and is broken down into time with a company who employs the apprentice interspersed with classroom based training.

They have to have a certain level of basic Maths and English but, if they don’t, they can take on extra learning as part of the course.

The cost of the apprenticeship scheme training is free if your apprentice is less than 18 (I think) with a sliding scale of discount as the apprentice age increases. There are at least 2 threads of learning:

  1. IT infrastructure
  2. Developer

We’ve had about 6 people go through the scheme. Some in IT infrastructure and some developer. The 1st apprentices we took in in Sept 2010 have only recently left the business and gone onto take new jobs in their chosen discipline, so for ID we’ve had 6 to 7 great years allowing us to give them great experience.

I also chatted with Ryan Yates on this subject and here’s what he had to say:

A career in IT will open a wide variety of opportunities including the ability to travel, both for work and also to learn at conferences and user groups, constant on the job learning and a never ending cycle of new things coming out to learn about. If you love to learn, IT definitely is a good choice to get into as there is always something new to learn about and this is not something that is going to change anytime soon. There are many areas that you could learn about within IT (not all of them technical), there are lots of fantastic people that blog and present about the knowledge they have (myself included), and whilst it can be difficult to find out the good from the bad, one of the most powerful things you can do for your long term career, once you’ve chosen that a career in IT is the one that you want, is to network with people like myself and ask questions of us, especially those about career choices or most things related to most forms of technology.

If you’re interested in finding out more and locating a training company then take a look at Microsoft Apprenticeships – Candidates (UK) and the Microsoft Students site. (The links provided are for UK schemes, but I am sure you will find the same in your country given a bit of Googling).

Don’t wait – You’re unlikely to be gifted the perfect career!

If you’ve left school, college or uni and haven’t been lucky enough to find a role then you can really help yourself become more marketable by following some of these tips. When I look at C.V.s, people who show initiative and do things to progress their skills immediately jump out at me.

Why?

If someone can self-start and show initiative then they’re more likely to do exactly that when working for us.

If you’ve yet to land your first job, follow these steps to make yourself irresistible:

Step # 1: Start learning …

These are some of the courses you can take to learn the fundamentals. All four of these cost less than a night out, don’t involve a hangover and can seriously improve your potential.

These 5 online courses will teach you the basics and from there you can decide on your chosen path. There are also loads more courses out there – take a look and give one a try, you never know what opportunities it could unlock.

Step # 2: Join a group, ask questions and be helpful

There are plenty of us old folk who are willing to offer some advice. In fact, quite a few of us actually enjoy sharing what we’ve learnt. For example, we have a Facebook group for Collab365 where there’s always someone willing to help. When using online groups, always consider the magic of reciprocity – just think about how you feel after someone has helped you out, you are far more likely to help them back. Now compare this to the polar opposite and think about someone who constantly asks you for help, but never offers anything back in return.

The best advice is where possible, help others. Okay, at the beginning of your career you may think you have nothing to share, but consider sharing your learning. Tell them about courses you’ve taken, what you found useful and things you may (with hindsight) have done differently, you may just be surprised about the amount of feedback and other useful tips you get in return. You could even start your own group to share learning with your peers.

Step #3 : Think of and build a “fun project”

If something interests you (this could be anything e.g. a mobile app, VR / AR, teach Alexa a skill, automate your parents home lighting system etc.), develop the idea further. You will find that your learning really benefits from some focus when you’ve got a specific project in mind. Once you’ve learned those skills you may even be able to make a career out of them – you never know who may also want to achieve what you’ve done. It’s also brilliant to chat / blog about your hobbies/interests and they add huge credibility to your C.V.

Step #4 : Share your learning on YouTube / Blog and here!

This will not only allow you to cement what you’re learning but it also looks amazing to prospective employers. How cool would it be to say in an interview, “I built a YouTube channel to share what I’ve learnt and got 150 subscribers”. I’d be blown away by that. As a little indication of how this can work so well for you…

I set up a community with a very proactive chap I’d met online called Vlad Catrinescu. He was just 20 at the time. He put so much effort into improving his skills and building a reputation for himself that he is now one of the most recognised names in our community and (I am pretty sure) is one the youngest Microsoft Valued Professionals (MVP). An MVP is something Microsoft give to a few around the world for their contribution to one of their products. In fact, it’s a pretty big deal! For Vlad to get it so young just shows what’s possible if you commit to doing something.

Vlad’s now been invited to speak at several conferences and also produced several courses (in his own time) that now sell to people all over the world.

** We accept blog posts on this site, so grab an account and let us help you share your new found expertise. The more you blog and get your name out there, the more this helps you land a job. **

Step #5 : Consider taking a Microsoft certification.

Microsoft has a massively popular certification programme and their certifications are recognised worldwide. You can pretty much study for them for free by using YouTube, blogs and Microsoft Virtual Academy.

In short, when you’re looking for work use your time wisely – should one opportunity not work out, don’t be too downhearted use the opportunity of free time to learn something new. It will motivate you and provide you with a much-needed skill that could prove to be the key to success in your next interview.

Advice from others ..

I also asked for advice from others who have been working in tech for a while.

Andrew Connell, Owner Voitanos

IT is a *huge* field in terms of breadth… know that if you go into it, it’s a career in learning as things will constantly change. If you embrace it and look at that as a positive, you’re gold.

To that point, interview LOTS of people in the field and find out what they like best about their position. From network admins to IT pros to help desk to support to DBA’s to architects to software engineers to web developers to designers to BA’s… find out what they like & dislike.

With your studies, focus on big picture/core things. The courses that taught me the most were the ones that made me think about concepts, not the ones that taught me a programming language/database design.

Most importantly though, pick something that makes you think, that challenges you.

There are only three books I still have from my college years. Discrete Structures (same stuff from the start of Goodwill Hunting), Design Patterns (by Gang of Four) & Business Law.

Marc D Anderson, owner at Sympraxis Consulting.

I think the notion of a “career” is passé. It’s more important to do work you love than plan some grand career. Do high-quality work, don’t burn any bridges (if you can help it), and build good relationships with your co-workers.

“I.T.” can mean so many things these days, but if anyone you talk to is customer-focused, please encourage them to give it a try.

Kevin McDonnell

Be open-minded and stay open-minded. Technology changes rapidly and often so consider how things are done as much as what they do as then you’ll be well placed for what changes. Don’t be afraid to shake things up a little but don’t be an arsehole about it

Final thoughts

I really hope this helps. I know from my personal experience and my daughters more recently, choosing a career can feel so daunting. Don’t worry if you choose the wrong one, just keep an open mind and follow your passion.

Never stop learning and go help others 🙂

December 16, 2018

How to create a User Registration Form using PowerApps, Flow & SharePoint Lists

Have you ever wanted to create a registration form for an event that saves the attendees to a SharePoint List? Do you also want the form to work seamlessly on desktops, Android and iOS? If yes – then I will show exactly how to do this.

This tutorial shows how to create a complete end to end user registration system using:

  • Microsoft PowerApps
  • Microsoft Flow
  • SharePoint Online

Related Content:

Here are the steps that we will go through in this tutorial.

  • First, we’ll create a list in a SharePoint online site to capture the user’s details.
  • Open and create a PowerApps App using the SharePoint list Menu.
  • Design a form using PowerApps designer and bind data with controls and then save the data back to SharePoint Online list.
  • Open and create a Microsoft Flow to send email on item creation using SharePoint List.
  • Finally, we’ll check the PowerApps functionality and configure an email to be sent from Microsoft Flow.

Sound good? Let’s begin …

1. Create a new list in your SharePoint Online site containing the required columns to capture the relevant User Registration details.

2. Now browse the SharePoint List and click on the ‘PowerApps menu’ within the SharePoint List screen and then click on ‘Create an app’ menu to create a PowerApps for this SharePoint List.

3. On the next screen, you need to provide a ‘Name’ for your app and click on ‘Create’ button to continue…

4. You’ll now be taken to the PowerApps portal and the app will be created for you (note, it may take a while!). See below.

5. On the following screen, the app is created for you and you can see the data which is being displayed from the SharePoint List. BTW, you can skip the tour if you don’t want to complete it.

6. Now, you can add a new screen to create a User Registration form. Open PowerApps designer and click on ‘New screen’ to add a new screen.

7. As suggested on the screen, you can add items from the Insert tab or connect to data to design the screen. For our registration app, please add TextBox’s, TextInput’s and Camera controls as below:

8. You need to setup following formulas and properties for your controls. Let’s take a look at them one by one:

TextInput
• Set Reset property to Button1_1.Pressed
• Set PhotoDateTime to get System date time

Camera
• Set the camera control to use the formula below which will capture the image and clear the collection. Also, update the date time.

ClearCollect(CameraImage, Camera1.Photo); UpdateContext({PhotoDateTime: Now()})

Save Button

• Add this formula into the Save button to capture entered data in SharePoint List

Patch(UserRegistration, Defaults(UserRegistration), 
{Title:"New",First_x0020_Name : TextInput1_6.Text,
Last_x0020_Name:TextInput1_1.Text,
Age:TextInput1_2.Text,
Email:TextInput1_3.Text,
MobileNo:TextInput1_7.Text,
Address:TextInput1_5.Text,
PictureDate:TextInput1.Text, 
Imagedata: First(CameraImage).Url});
Navigate(Screen2, None)

9. Now, you can run your app and insert the data in your SharePoint list.

10. Let’s also add a new screen to display captured data in list grid view as depicted below.

To get the total count of the users, you can use below formula:

"Total Registered Users : " & CountRows(UserRegistration)

To Add ( ) new screen, you need to apply below formula:

Navigate(Screen1, None)

To refresh data on screen use formula:

Refresh(UserRegistration)

To delete:

Remove(UserRegistration, ThisItem)

To Back:

Back()

11. Save the app and go to the App settings to setup following;

• Icon
• Background color
• Screen Size and orientation

12. Click on Share, it will take you to a browser to setup following:

  • Details – you check details of your app.
  • Share – you can share the created app within the Organization.
  • Versions – by clicking on this tab, you can check and delete versions of your app.
  • Settings – you set an appropriate category for your app. You can use a maximum of 2 categories.

Note: You can’t share PowerApps outside your Organization/Office 365.

Create a Flow to send an email notification when a new person registers.

Now, you need to create a Flow to send email notification on item creation of SharePoint List. So, you can follow the steps below to create Microsoft Flow for your App.

1. Go to your SharePoint list and click on ‘Flow’ menu.

2. Click on ‘Create a flow’ and select ‘Send a customized email when a new SharePoint list item is added’ flow from the following screen.

3. On the next screen, you need to click on the ‘Continue’ button to proceed.

4. On the next screen, you can add and edit details for your flow. Click on ‘Create flow’ button to continue.

5. Now, your flow is created. You should be able to test it by creating a new item for the SharePoint list.

6. Create a list item and check your Inbox for the notification email.

7. You can install this app on your Windows, iOS and Android mobile device to test the functionality. Below are the screenshots of mobile screens.

Finally, you’re done!

By following steps of this blog, you have learned the following things:

  • PowerApps connection with SharePoint list.
  • Formulas of PowerApps to perform the following operations:
    • Connect Camera control to app
    • Save data in a SharePoint List
    • Bind controls with data source (SharePoint list)
    • Navigation for screens
    • Add new screen
    • Delete record using formula
    • Clear controls value on button click
  • Create flow for your SharePoint list and send email notification on new item creation in a list of SharePoint
  • Setting up PowerApps
  • Share your PowerApps within Organization

I hope, this will be helpful for you.

December 15, 2018

How to send (or forward) an Email to a Microsoft Teams Channel

In our 2nd Microsoft Teams Tip, we’re going to show something really cool around Email… Imagine you are a salesperson out on the road and you get an Email from a potential customer. The customer is asking for a special deal which requires you to discuss it further with your sales team. Normally, you’d forward the Email to your colleagues and then Email tennis would continue for the next few hours. Then, after a few days, it gets lost in the abyss of your inbox… One of the main drivers behind teams is to try to replace these all too common use cases where Email has often been used. In fact, I think an MS Teams Channel also has several advantages over Email namely:

  • Allows instant chat to happen around pretty much anything.
  • Stores files/attachments directly into SharePoint so nothing gets lost/deleted in your inboxes.
  • More structure collaboration with the introduction of tabs for things like Planner, Office Docs, Files and so much more.
  • You can add a ‘connector’ allowing you to bring in data messages from tons of apps (e.g. Trello, Twitter, CRM’s, News, Market etc).

There are loads more advantages but these are the big wins where a Teams Channel would work better than a “CC all” via Email. Emails are dead 🙂 So back to the point of the post – let me show you how easy it is for that sales guy to send the Email to his channel …

Step 1 – Find out the Email address of your Microsoft Teams Channel

In order to forward / send an Email to an MS Teams Channel, you need to know what the Email address is. This is pretty simple, just find the Channel that you’d like to Email, find the ellipsis and then select “Get Email address”.

Step 2 – Make a copy of your Email address

Once you’ve clicked “Get Email address”, just make a copy of it and you are ready to rock ‘n’ roll. 

Step 3 – Now it’s time to test it out and send an Email to your Channel

To do this, pull up your favourite Email client and simply send a message to the Email address we discovered above. 

Step 4 – Let’s see what it looks like within Teams

The screenshot below shows how the Email looks. Few things to note:

  • It shows up in the “Conversations” Tab.
  • Any attachments (the photo I attached) are extracted and saved to the SharePoint document library for the channel.

Step 5 – See how the Email and the Attachment are stored within the “Files” Tab.

One very cool process happening underneath is that the Email along with the attachments get stored in the SharePoint site that’s created when you create the channel. Look at the image below to show how the Email is stored in a document library called “Documents” within a folder called “Email Messages”. This gives these benefits:

  • No data is lost 🙂
  • It’s searchable and organised in one place.
  • You’ve got the power of SharePoint at your disposal so you can provide some further workflow to the Email (perhaps using MS Flow).

Step 6 – Finally, this is how the Email looks within Microsoft Teams.

December 15, 2018

Why is it so hard to hire a person with Microsoft skills?

Obtaining and maintaining relevant Microsoft skills is a tricky business. Not only are there a large number of different Microsoft products but for each one their technologies are moving on so fast that it can be tough to keep up! New releases, services, products and updates are published at an alarming rate and keeping your team up to date can require constant vigilance.

Yet they can offer so many benefits that organisations probably want to use them as soon as they are available to maintain their competitive advantage and stay ahead of their rivals. It’s a constant battle, with the 2 main options (recruit more/different staff with up to date skills or train/retrain your existing staff) both potentially requiring significant budgets! Let’s look at some of the common issues in more detail.

Recruiting Permanent Staff Members

Let’s face it, recruitment is tough and often very expensive!

That’s before considering that you may also need to wait for someone to leave or retire or worse, manage an ineffective existing staff member!

Should you promote a current member of staff into a new role, you still have to back-fill their old position, so while it’s great for the team to see hard work and dedication being rewarded with new opportunities, from a management point of view, you still have a lot of work to do!

Any recruitment process needs you to clearly specify your requirements, get them approved by management, pass the details to HR or an external agency and then wait to see who responds. This generally goes one of two ways – either no one is interested, or you get loads of applicants which then provides you with a new nightmare of sifting through their details, making a short list, inviting them to interview and trying to identify someone who will fit nicely into your team over the course of 1 hour!

Each one of those stages needs a whole load of effort! Writing a job spec. is easier if you are replacing an existing role, but what if you are creating a new one?

Getting recruitment approval depends very much on the performance of the company as a whole, and in these economically tough times, what if there is a recruitment freeze or your organisation thinks you can ‘make do’ and share the responsibilities out amongst your existing staff?

Recruitment agencies can often help (but at a considerable price) and may merely present you with the C.V.s of people on their books who happen to be already available or whose existing contracts are coming to an end; often with a long list of skills that makes you wonder whether they actually ready your job spec. in the first place and not necessarily someone who would be an excellent fit for your organisation or who has targeted your organisation as somewhere they would really love to work.

Generally, the process is worth it assuming you can find someone great who then rewards you by staying with you for several years and therefore protecting you from going through the drama all over again!

But what if you only need someone for a short period of time?

Training / Retraining Existing Staff

Training (or retraining) your current team members is great because it can reduce the need for the recruitment nightmare scenarios above; however, it is not without its own problems.

Suitable training opportunities can be hard to find, you could spend hours researching different training companies and course opportunities to eventually see a fabulous course that will fit your needs perfectly, only to realise it involves significant travel expenses or not be scheduled for another 6 months when you need a solution right now!

You may find loads of courses that could help, but nothing that addresses your specific needs or the course agenda suggests you will only get halfway there! In the back of your mind, you also know that, while a member of staff is undertaking the course, they are not able to continue with their regular duties leaving you with a backfill problem, or (more likely) stuff that won’t get done while they are away.

Before you throw in the towel…

All is not lost, here at Collab365, we have spent the last year creating a new exciting solution to these problems in the form of Collab365 MicroJobs.

MicroJobs can introduce you to a range of experienced Microsoft Professionals ready to help you meet your specific needs. If you don’t want to recruit a permanent person, you just need help for a couple of hours to get you past a roadblock or want someone to train your team as soon as possible & once you’ve solved the problem you will be back on track then read on!

How MicroJobs can help

  • Freelancers are willing to help you for as long as you need – from a quick 15-minute phone call to an hour online meeting to a few days training or providing expert technical help.
  • Many of our Freelancers are Microsoft MVP’s – they know their stuff and have insight into new Microsoft releases in advance so are ready to cascade their knowledge as soon as it is released publicly.
  • If your specific requirements are not entirely met by the Freelancer (let’s face it if they offered every combination of services up front you would be bombarded with detail) so we’ve designed an option for you to contact them directly to tailor a job to your needs so you can be sure to receive precisely what you need, when you need it.
  • The MicroJobs service is free to use for Collab365 Community members & we are onboarding more and more Freelancers every day so keep checking back to see what’s new.

How to use the Collab365 MicroJobs platform

You can browse the available MicroJobs here. If you’d like to proceed and purchase one you will need to set yourself (or your company) up as a buyer – this isn’t us being overly protective, it’s a requirement for financial regulations and also means that if you want to contact a buyer, they can reach back to you with ease! You can register in 3 steps & it only takes a minute. The process is as follows..

1 – Sign up…

Click the ‘Sign Up’ button at the top right-hand corner of the screen to begin.

2 – Add your details.

You can use existing social profiles or create new ones specifically for MicroJobs (this may be preferable if you are going to sign up as a company and use a generic log on for all IT teams to use to purchase MicroJobs).

 

3 – Create your MicroJobs Account.

Please choose your Username carefully as it will be seen publicly on your profile and can’t be changed later. Also let us know if you would like to be added to the newsletters we issue. Click on the links provided to see examples of what you will receive and don’t worry – you can unsubscribe to these at a later stage. If you find the content useful – let your team members know so they can benefit as well 🙂 . Finally (as with all online systems) please ensure you are happy with our T&C’s…

Using the MicroJobs platform

Once you have signed up and are logged in you can contact Freelancers and purchase MicroJobs..

You can search for the skills you need, use the menu items to narrow your search or use the “Request a Service” option on the left to ask for something specific (more details about this in another blog)

 

Clicking on one of the jobs will take you into the description screen where the Freelancer will explain in more details what they will deliver as part of the deal. Keep a look out for any extras they may offer in relation to their service (they may not be relevant to you but allow the Freelancer to offer a more flexible and complete service.)

Once you find the item you need, click on the buy now button..

You will then arrive at the following screen to confirm your purchase..

This is like an online ‘basket’ and confirms exactly what you are purchasing. Payment is made using the Stripe Connect system which holds your payment securely until the work is completed (the Freelancer only gets paid after the job is marked as complete giving you extra reassurance that they will do a good job!)

Check the total (to make sure you have added any required extras) and click ‘PayNow’..

Add your card details then click ‘Confirm & Pay’..

Note the ‘Useful Links’ that are provided under the ‘Confirm & Pay’ section, these help explain what happens next and any other questions you may have.

I hope this helps – don’t forget the MicroJobs service is free to use and we are onboarding more and more Freelancers every day so keep checking back to see what’s new.

December 14, 2018
Load More
>