Category: Office 365

Create an Approval Process Template using SharePoint and Power Automate – Part 1

Overview and features

How long does it take from you to develop a complex approval process? What about testing?

Do you want a single simple solution that you can use to automate all your business processes?

In this post I will show how to create a template using SharePoint and Power Automate that you can keep reusing for all your processes. The solution will allow to:

  • Automate repetitive processes
  • Define approval steps from a SharePoint configuration list in order not to change in the Power Automate flow
  • Gives an overall picture of the workflow – Keep history of all actions in a separate SharePoint list
  • Manage approval process from a SharePoint configuration list
  • Standardize all notifications by creating one single location to send email and create approval
  • Save development time and most importantly standardize approval processes
  • Approve from either SharePoint List or Power Automate
  • Choose notification option to assigned approvers from the following options:
    • Notify through email – user must approve from SharePoint list. This is useful in case there is some fields that he needs to set before submitting to the next step.
    • Notify through email with options – User can respond with any of the selected Options from Email.
    • Create approval with options – User can respond with any of the selected option from Email or from flow client app.
  • Assign approval to manager get from Active Directory
  • Send custom notification to requester
  • Send notification to chosen people on CC
  • Integrate with MS Teams

With this system you will be able to develop very big and complex approval processes in matter of hours.

Solution architecture

Following the main components of the solution:

  • SharePoint Lists
  1. Trigger List: this is a custom SharePoint List used to trigger the flow. It contains all the fields necessary to keep track of the flow status, and all the fields required for business (Form fields). We will name this list: Flow Trigger
  2. Configuration List: this is a custom SharePoint List that contains all the process steps and what will happen on each step. We will name this list: Flow Status
  3. Flow History List: this is a custom SharePoint List that will contain all approval actions and history. This list will also trigger a Flow that will send email notifications and create approvals. We will name this list: Flow History
  • Power Automate Flows
  1. Main Flow process: this is a Flow triggered when a new item is created or modified on the Main List. It checks the current data and accordingly fetch what is the next step from the Flow Status List. We will name this Flow: Flow Trigger – Main Process
  2. History Flow Process: This is a Flow triggered when a new item is created in the list Flow History. In this Flow we will provide a unified location to send notifications and create approvals. We will name this Flow: Flow History – Main Process

SharePoint Lists (back end)

Following SharePoint Lists (back end) fields.

Flow Trigger

Create custom SharePoint List: Flow Trigger with the following fields.


TitleSingle Line of Text
WorkflowCurrentStatusLookupUsed to retrieve current process Id from configuration list Flow Status

WorkflowCurrentStatusIDNumberUsed to contain Id of lookup field WorkflowCurrentStatus. This will be set by workflow and needed for ease of using a Text Field instead of lookup
RequesterPeople PickerPeople only field. Used to contain the summary status of the request
RequestClassificationChoiceChoice field used to set the current status

ApprovalStatus1ChoiceThis field is updated by user. Contain choices for first level submitter. Usually options available for requester

ApprovalStatus2ChoiceContain choices for second level submitter. Usually options available for approver.

ApprovalDate1Date With TimeContain the date of level 1 submit. (In this example when Requester change the status from Save as draft to Submit for Approval) This is changed by Flow
ApprovalDate2Date With TimeContain the date of level 2 submit. (In this example when ApprovalStatus2 is changed from In Progress). This field is updated by Flow.
ApprovalDate3Date With TimeContain the date of level 3 submit. (In this example when ApprovalStatus3 is changed from In Progress). This field is updated by Flow.
ApprovalDate4Date With TimeContain the date of level 4 submit. (In this example when ApprovalStatus4 is changed from In Progress). This field is updated by Flow.
ApprovalDate5Date With TimeContain the date of level 5 submit. (In this example when ApprovalStatus5 is changed from In Progress). This field is updated by Flow.
ApprovalDate6Date With TimeContain the date of level 6 submit. (In this example when ApprovalStatus6 is changed from In Progress). This field is updated by Flow.
ApprovalDate7Date With TimeContain the date of level 7 submit. (In this example when ApprovalStatus7 is changed from In Progress). This field is updated by Flow.
ClosedDateDate With TimeContain the closure date. At this point the request is completed, either approved or Rejected. This field is updated by Flow.
CommentsMultiple lines of textUser updated field. Make sure to set as Plain Text!!

CommentsHistoryMultiple lines of textContains history of all approval actions and comments. This field is updated by Flow.

EmailSelectedFlowIDNumberFor Internal use with Flow.
EmailSelectedUserOptionSingle line of textFor Internal use with Flow
LastSubmitActionSingle line of text
LastSubmitByPeople Picker
LastSubmitDateDate With Time
AssignedToPeople PickerContain the current peoples assigned to request

These are the standards fields used by the workflow. In this list you will also add all the business-related fields.

Flow Status

List Columns

TitleShort TextName of the approval step
Assign to Requester ManagerYes/NoSet to Yes in order to assign the request to the Requester Manager set in AD. In case no Manager is set. The request will be assigned to whoever is set in field Assigned To
Notify Requester in CCYes/NoSet to yes in order to send a notification to the requester on the action taken on this step
Notify OptionsChoice

  • Email: Send Email a normal email. User must login to the form in order to approve
  • EmailWithOptions: Send Email with approval options. User can approve directly from Email.
  • ApprovalWithOptions: Create and wait for approval with custom responses
Approval Options Flow StatusMultiple lines of textSet Flow status ID Corresponding to each Approval Option. This a Json object with the following schema


“type”: “array”,

“items”: {

“type”: “object”,

“properties”: {

“ApprovalOption”: {

“type”: “string”


“Id”: {

“type”: “integer”


“ApprovalAction”: {

“type”: “string”



“required”: [






  1. Id: point to the Id of the step that will be in case ApprovalOption is selected
  2. ApprovalAction can only take the below values:
  • Continue
  • Review
  • Approved
  • Rejected

This instruct the workflow on the type of action being done. This will be explained further in Part 2

Following a sample of data:


“ApprovalOption”: “Approve”,

“Id”: 4,

“ApprovalAction”: “Continue”



“ApprovalOption”: “Reject”,

“Id”: 6,

“ApprovalAction”: “Rejected”



“ApprovalOption”: “Send for Review”,

“Id”: 1,

“ApprovalAction”: “Review”


Approval OptionsShort TextNo need to fill this field manually as it will be filled by workflow. This fields contains the custom responses separated by ,
Assigned To CCPeopleThe people that will be notified on CC on this step
Assigned ToPeoplePeople that will be assigned on this step

Sample Data

So, what does the above means? If we take for example step 2- Manager Approval,

  • Assign to requester manager = Yes => the request will be assigned to the requester manager. In case the requester manager does not exist, the request will be assigned to user “dany” (Assigned To = Dany)
  • Notify Requester in CC = Yes => the requester will be notified on CC
  • Assigned to CC = “Tony” => User “tony” will be notified on CC
  • Notify Options = “ApprovalWithOptions” => the notification type that will be send to the assigned user will be created as Create and Wait for an approval with custom responses
  • Approval Options Flow Status

This means that on this step we have 3 approval options: “Approve, Reject and Send for Review”. When “Approve” is selected the flow next step id is equal to 3 and the flow will set the field WorkflowCurrentStatus to “3-Final Approval”. When “Reject” is selected the flow next step id is equal to 5 and the flow will set the field WorkflowCurrentStatus to “5-Completed – Rejected”. When “Send for Review” is selected the flow next step id is equal to 1 and the flow will set the field WorkflowCurrentStatus to “1-Requester Submit”.

In summary, this will define the action to be taken per the selected value by the user

Flow History

Items in this list will be created by workflow and will be used to keep track on this history of all actions that have been done. Also, this list will be the trigger of the approval workflow. This will bring the advantage of having one single place to define approvals.

List Columns

TitleShort Text
EmailTemplateKeyShort Text
SubjectShort Text
BodyLong Text
CurrentItemUrlShort Text
relatedCaseIDShort Text
KeyShort Text
ItemDescriptionLong Text
ItemTitleShort Text
ModifiedActionShort Text
SiteURLShort Text
ListNameShort Text
CommentsHistoryLong Text
WorkflowCurrentStatusShort Text
SiteFlowURLShort Text
SiteFlowIDShort Text
RequestClassificationShort Text
EmailSelectOptionsShort Text
ModifiedByDateDate With Time
EmailOptionsHeaderTextShort Text
EmailOptionsSelectionTextShort Text
FlowNameShort Text
ListTitleShort Text
CommentsLong Text
BodyApprovalLong Text

Sample Data


In part two, we will create the Power Automate flows that makes all the magic.

September 21, 2020

What would I do to become a successful full-time Microsoft Freelancer?

If you are an IT Contractor working on-site and would like to become a full-time Microsoft Freelancer, you might need to think differently to succeed.

Note: I started to write this post as a response to a couple of comments on Ant Clay’s Employers: Are you prepared for the IR35 ticking time-bomb?, but decided it was probably better as an article…

If you are currently serving clients as an independent contractor, by working on-site, then you are likely to be the main product of your business. The downside is, if you’re away or off sick, you won’t get paid. As you can’t scale or clone yourself, it also means you are limited by what you can deliver on any given day.

Changing to the freelancer model, there are ways to not only get paid but also solve that “scale” problem.

In this article, I want to share what I would do if I had to become a freelancer on a platform like Collab365 MicroJobs.

First let’s be straight, if you quit today (maybe due to forced changes like Ant describes), and want to earn the same as a Freelancer (immediately), it’s going to be really “challenging”.

You might get a few sales if you market yourself, but it does take time. You firstly need to be offering what people want and at a price they are willing to pay. Instead of an agent bringing you large contracts every 6 months, you need to learn how to market yourself to many clients across the World.

However, if you can make the transition, freelancing offers many benefits:

  1. You get to work from anywhere and enjoy great freedom.
  2. The number of potential clients you can serve will expand to the entire world, not just somewhere within a commute of your home.
  3. You will get more job experience, variation, and hopefully more enjoyable experiences because you will work for clients in all forms of industry.
  4. You no longer have to travel to work. Let’s assume it takes you 1 hour to get from your office desk to your home. That’s now nearly three weeks you get back over a year (or two years over a 40-year career).

How good does that sound!

Anyway, let’s get back to what I’d do! Hopefully, it will give you some different ways and ideas to think about “going for it”.

What are my goals as a Freelancer?

First up, I’d take a step back and set myself some goals:

  • I’d want to be able to earn money on “auto-pilot”, meaning not everything I sell should require “oodles of my time”. This will protect me against sickness and allow me not to stress while on holiday. (I always detested not earning while sat on the beach!)
  • I’d also want to specialise in “one thing” and become an expert in it. Why “one thing”? Tbh, I learned many years ago; it’s both impossible and frustrating to keep up with everything Microsoft has going on. SharePoint was hard enough. I would want to become a master of one tech/area and not have to think, “Oh great! MORE stuff to learn”.
  • I’d also want to be able to set prices for what I was worth, rather than race other freelancers to the bottom price.
  • And, most importantly, to ensure that whoever paid for my MicroJob, received an amazingly successful outcome. Ensuring customer success would then give me positive reviews and a great chance of repeat business.

How on earth could I fulfill all those goals?

Step 1: Pick a specialism.

I’d pick something I enjoy doing and learn that well. I would find an online training course, attend a conference such as GlobalCon1, and also get someone on MicroJobs to help me level up and mentor me in those early stages. Getting paid help means I can shortcut my route to success and also build a good relationship should I ever need to “buddy-up” on a Gig. (Some Freelancers are already doing this).

Step 2: Do some research.

I’d then begin doing some research on tech forums, vendors sites, app stores, conference agendas to discover what’s in demand. Doing this research would give me a good indicator of what kind of problems people are struggling with.

Step3: Build something to sell.

Now that I’ve learned something well enough to offer to others and also got a clear picture of what they are asking for, I’d create a pre-canned solution. The solution should take me 1-3 weeks to build and be good enough to sell to thousands of customers.

It would have to be a small, “granular” solution as I would want to price it in the range of 200-500$. Something cheap enough to be bought on a credit card but good enough to supplement my daily income and eventually even become my main source of income!

Going small offers these advantages:

  • It means if no one wants what I’ve built, I’ve not wasted months of effort.
  • It means I am more likely to be found in particular Google and platform searches. People don’t search for a “Power Apps Freelancer”, they search for “Time Recording Power App”.
  • It means I can create lots of solutions to granular problems, increasing my chances of sales.
  • It also means I don’t have a support nightmare on my hands, as my solutions are simple.

As an example, if I chose “Power Apps” as my specialism, I would find out what’s always coming up in forums like on Facebook groups.

Anyway, for this example, let’s say my research ended up in me deciding to build a “Time recording” Power App.

I’d go and build that, record a great video on how to use it, do awesome screenshots and provide a compelling overview for it.

One I have it built, I have 2 options:

I could put it for sale on my own website. However, then I have to worry about building and marketing the website, plus payment and more importantly, I would find it harder to do the “real kicker” of this idea (coming next).

As I am biased and my solution is Microsoft-based, I would place it on Collab365 MicroJobs (as a MicroJob) and then add extra’s that buyers can add to their order.

No alt text provided for this image

Against My MicroJob, I would offer these extras:

  • blocks of time to customise the Power App to meet the buyer’s requirements.
  • a block of time to customise it further over X weeks.
  • Include extra documentation on how it works.
  • “handover training” so the buyer can maintain it themselves. (I would probably have this as a separate MicroJob.)

Doing the above, now means that people could buy my Power App, while I sleep and I’d only need a small amount of my time to deliver it. On top of that, a certain percentage of my extra’s would be purchased increasing my sale value and allowing me to charge more premium rates as the customer already wants what I have.

As I ensured customer success, I would then get great reviews on my MicroJob encouraging others to purchase as well. (This is why I wouldn’t bother with my own website approach).

The example I used is around Power Apps, but you could relate this to anything you can package:

  • Scripts,
  • Short training videos,
  • Ebooks,
  • Templates,
  • Guidance,
  • Tools / Utils, etc.

The result of this approach is that I am now able to sell something simple to multiple clients that doesn’t cost lots of my time.

The buyer will want my solution because people always go for the quickest shortcut to success.

For example, if I offered a MicroJobs that gave the same outcome such as:

  1. “I will build you any Power App in 3 days”,
  2. “Buy my Power Apps Time recording App”,

Which one do you think they would buy, (assuming they were hunting for a time recording solution on Power apps)? Which one is most likely to come up in a Google/Platform search?

*Update*: I got a couple of messages back about this being great for developer-focused people but I am not a developer or Power User.

If you specialize more around user adoption, frameworks, project management, governance, compliance, it’s harder, but not impossible.

A few examples of would you could do:

  • Think about 2-hour training, videos, ebooks.
  • In-depth guides, reports, research, analysis (e.g Gartner reports)
  • Create mini frameworks, processes to sell.
  • Hook up with other developer-minded freelancers and get a tool that you could sell (which leads to your expertise consultancy).

I agree it’s a bit more of a challenge but you must create something that’s both highly congruent to your specialism and valuable enough for people to pay for.

Example: Assume I was a “Scrum Master” and wanted to help others set-up great processes around their Microsoft-based projects.

I would create an offer such as the “Ultimate Scrum Masters Kit”, which includes:

  • explainer videos by me
  • checklists
  • templates
  • branded planning cards
  • guide: how to setup planner, Teams for tracking
  • guide: how to find and engage with short-term experts on Sprints
  • [more… (hopefully you get the idea.)

Create enough value in your “kit” and niche it by technology and/or industry. It’s a good idea if you angle it towards a niche as this will allow you to stand out from competitors and also makes the potential buyer feel like you’re so much closer to their current problems.

Why do I need to target a niche?

For example, if your BMW 5-Series kept cutting out and if both cost the same, would you prefer to send it to a general car mechanic or an engineer or who builds the exact model of your BMW?

Anyway, once your Kit is put together, your custom extra’s (“Upsells”) could be:

  • “I will sit in and advise on your Sprint planning sessions”.
  • “I will run a retrospective for you”
  • “I will run your stand-ups for 1 week”
  • “I will give you one-on-one training”.

Do ‘Expert Calls’ to learn more about your customers

To supplement my income and get a good feel for what people are struggling with, I’d also offer a MicroJob to book an expert call with me. This is actually something new and we’re running a pilot now. Not only would I learn much more about “simple solutions” I could build, but this would allow me to earn extra and timebox how long it’d take. (Great if you’re transitioning from full time to freelance).

Take a look at the “Expert Calls” we’ve got in our pilot.

There’s plenty more I could do to improve this, but I don’t want to take up too much of your time. Happy to answer any comments.

BTW, If you need some inspiration, some of our existing Freelancers are already offering MicroJobs similar to this idea:

I will provide and set up a PowerApp Holiday App for Office 365

Make your SharePoint migrations easier with this Script Kit

Anyway, hope this gives you some food for thought!

A couple of options:

  1. If you need a Microsoft Freelancer, request one.
  2. If you want to become a Freelancer
February 28, 2020

How to gain access to Microsoft Teams sites using SharePoint Designer

By default, you cannot open MS Teams site using SharePoint designer because Custom Script is not allowed on OneDrive, on sites users create themselves, on Group Sites such as sites created by MS Teams and on the root site for your organization.

To allow custom script on MS Teams sites

To allow custom script on a site immediately, follow these steps:

  1. Download the latest SharePoint Online Management Shell.
  2. Connect to SharePoint Online as a global admin or SharePoint admin in Office 365. To learn how to see Getting started with SharePoint Online Management Shell.
  3. Run the following commands.
Connect-SPOService -Url https://<siteName>

Set-SPOsite <SiteURL> -DenyAddAndCustomizePages 0

Make sure when specifying the site URL, to remove the trailing slash, as including it (e.g.: https://<tenant><siteURL>/), PowerShell will expect that (sites/<siteURL>/) to be a managed path when it is not. Leaving the slash off (e.g.: https://<tenant><siteURL>), will works fine.


January 6, 2020

How to create an approval workflow with custom responses?

Not all approvals are as simple as Approve/Reject. Sometimes there are more than two options. For example, we need to approve, reject or re-submit for review.

The Approvals V2 actions introduced the ability to replace the typical Approve/Reject choices with any number of user-defined choices but it required a premium P1 or better license. The good news is that after October licensing update this was moved to Standard Licensing.

As a separate note, before approving with custom responses was available in the standard licensing there was another way for this by using the action Send Email with Option thus it didn’t have all the features available in the approvals action, like integration with the mobile client or appearing on the Approvals tab in Flow. But it did let users specify their own comma-delimited list of approval responses.

Creating approval using custom responses is identical to the regular approval with few modifications explained below.

1. Add action start and wait for an approval

Select either Custom Responses – Wait for all responses or Custom Responses – Wait for one response

2. The action will expand by adding Response Options field highlighted in the screenshot below

3. Continue with your flow logic. For example

4. Running the flow, following the approval with custom responses received

January 6, 2020

How to update the SharePoint People Picker Field using Power Automate

In this post, we are going to demonstrate how to create a Flow (using Power Automate) that updates a SharePoint people picker field using the “Send a HTTP request to SharePoint” action.

The Flow will update the following people picker fields

  • Requester: will be set to modified by field
  • Manager: will be set from requester manager in case the manager does not exist will set to empty
  • Assigned To: this is a multiple value field; we will get the values from a multi value people picker field in another list. In case no values are retrieved. We will set to empty

Before we start, it is important to mention that when using REST API, the People and Group field will be returned as “FieldNameId”. The format for user field value:

  • single-valued user field: ‘<user field name>Id’ : <user id>
  • multi-valued user field: ‘<user field name>Id’ : { ‘results’: [<array of user ids>] }

To empty the fields

  • single-valued user field: ‘<user field name>Id’ : -1
  • multi-valued user field: ‘<user field name>Id’ : { ‘results’: [0] }

Initialize Variables

So now let us go to our Flow and start by creating the needed variables. Note that it is not necessary to create these variables, but it will make the Flow more readable and maintainable.

Get Requester Id

As mentioned, to update a people picker field we need the Id of the user. At the time of writing, Flow does not support a way to retrieve user Id, so we must get it using the Rest API.

We can use the following Rest API to get site user information:

  • _api/web/SiteUsers/getByEmail(‘UserEmail‘)

Get Manager Id

Now let’s get the manager for the requester.

Get AssignedTo Id

Update Item – Set Requester, Manager, AssignedTo

Update item using Send an HTTP request to SharePoint action set as following:

  • Method: Post
  • Uri: _api/lists/getbytitle(‘@{replace(variables(‘ListTitle’), ‘ ‘, ‘%20′)}’)/items(@{int(triggerBody()?[‘ID’])})
  • Headers:

"Accept": "application/json;odata=verbose",

"Content-Type": "application/json;odata=verbose",

"X-HTTP-Method": "MERGE",

"IF-MATCH": "*"


Remove highlighted in case of add item
  • Headers:

"__metadata": {

"type": "[email protected]{replace(variables('ListName'),' ','_x0020_')}ListItem"


"RequesterId": "@{variables('RequesterId')}",

"ManagerId": "@{variables('ManagerId')}",

"AssignedToId" : @{variables('AssignedCCId')}

December 24, 2019

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.


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.


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!!


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


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 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.


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 –

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 –


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 🙂


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).


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


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.


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.


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:
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:

The host is the URL which hosts your web service. In this case, it is the Azure tenancy address we want:

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.

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.


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:

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:
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:

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


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.


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 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!


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.

enSaveTooltipSave the customer
ptSaveTooltipSalve o cliente
frSaveTooltipSauver 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
Load More