SharePoint Saturday Detroit 2018

I'm excited to announce that I'll be returning to SharePoint Detroit for a third year in a row.  This year, I'll be presenting Business Process Automation is not Dead!

We've heard it all before - SharePoint solutions are dead. SharePoint Designer is deadish. InfoPath is not far behind. So what does all this mean for your workflow and forms solutions in SharePoint Online or your hybrid environment? Over the past couple of years, Microsoft has released a number of new apps and services that that not only fill the gaps, but provide you with great flexibility.

Join me in this session as I introduce some of these new technologies, including PowerApps, Forms, Flow, LogicApps, Azure Functions, and Azure WebJobs to name a few and discuss the scenarios to best leverage each of them.

Flow Approval Workflows with Office 365 and Azure AD Groups

If you've had a chance to use the Flow approvals, you'll agree that it's very easy to configure them and have users respond to them, even from within their email applications.  Building approval workflows is very simple.  All you need is is a trigger that will cause the approval to kick-off and provide a few key pieces of information:

  • Approval type - whether one person or everyone in the Assigned to field must respond to it before the workflow continues
  • Title - this will be the title of the email sent and the approval item in the assignee's environments
  • Assigned to - who will be approving the workflow
  • Requestor - who is requesting the approval

Workflow Assignees

The list of Assigned to is made up of one or more entries.  Each entry will receive an email with the approval notification.  There are three types of email-enabled entities in Office 365:
  • Individual users
  • Office 365 Groups
  • Azure AD Groups
The case of sending approval requests to individuals is straight-forward.  Simply add the individual email address in the Assigned to field and you're done.  However, it's a bit more complex when dealing with Office 365 or Azure AD groups.  Currently, the Approval workflow does not distinguish between individuals and groups.  If you try to type the name of a group, you will notice that the lookup will not resolve it.  In my case, I have a groups office365group and azureadsecuritygroup.  Here's what happens when I try to add them

So what do you do if you need to send approvals to members of groups?  You need to first identify what type of group you have and then retrieve the individual users.

Claims Encoding

One way to tell what type of group you have is to look at the claim that is associated with it.  Each email type is made up of different parameters in the claim encoding.  A claim is made up of 5-6 components, as defined in the SharePoint 2010/2013: Claims Encoding:

<IdentityClaim>:0<ClaimType><ClaimValueType><AuthMode>|<OriginalIssuer (optional)>|<ClaimValue>

When you look at the claims returned for an individual, Office 365 Group, or Azure AD Group, you'll notice the following patterns:

  • Individual - i:0#.f|membership|<user email>
  • Office 365 Group - c:0o.c|federateddirectoryclaimprovider|<GUID>
  • Azure AD Group - c:0t.c|tenant|<GUID>
Without understanding all the intricate details of each claim type, you can use these formats as fingerprints to the type of email address you're dealing with

Resolving Email Addresses

Now that we know how to distinguish between the various email types, the next step is to provide different instructions for your Flow to deal with each.  Below is a snapshot of how I generate a list of all approval emails

It looks complex, but is fairly straight-forward.  Here's the breakdown:

  1. Initialize a string variable that will hold all the email names, e.g. Approval Emails
  2. Get a list of all the email objects.  In my case, I retrieve it from a SharePoint list item People field.  I then iterate through each of the objects as follows
    1. Determine what is the claim format before the second pipe (i.e. |)
    2. If it's for single users, simply add them and a semicolon before them to the Approval Emails string variable
    3. If it's an Azure AD Group or Office 365, get the appropriate group by looking at the claim GUID following the second pipe (NOTE: make sure you use the correct connector to retrieve the groups)
    4. For each group, iterate through the list of users and add their email and a semicolon before it to the Approval Emails string
Once you finished iterating, you can then assign the Approval Emails string to the Approval Request action.


As with many solutions, there are some potential caveats here:

  1. Approvals require at least one email address.  Make that the list of approvers (composed of the individuals and groups) constitute one or more email addresses.
  2. If your Azure AD group includes other groups, you'll need to modify this mechanism to drill down to extract the users as well.  This can be done using loops or recursion.

PowerApps Sharing screen just got a Face Lift

Say hello to the new PowerApps Sharing screen.  In the past, you were able to select users and share the app.  With the new UI, you know the specific permissions the users will need, such as gateways, API's entities, and various connectors. 

As a PowerApp creator, this can save you time from having to remember to verify the permissions or have users contact you about the problems they may be facing if they don't have the required access.

Staying Healthy with Flow and PowerApps

Getting sick is never fun, especially with small kids at home.  This is why it’s important that we take
our meds quickly to avoid prolonging our recovery.  In the past, I would stack up each on some common meds to be ready just in case I needed them.  However, I would still find myself either missing some, buying the same ones I already have, or having expired ones because I bought too many in the past.  Well, now that I have Flow and PowerApps, that is all in the past.

In this post, I’m describing a simple MedApp I built to help me keep track of the meds I have at home and replenish any that are about to expire.  The app serves a few purposes:

  • Keeps track of what apps I have at home and when they expire
  • Notify me when meds are about to expire
The solution is made up of three parts:  SharePoint list, Flow, and PowerApp

SharePoint List

The SharePoint list is the backend that stores all my key information.  A few fields were required for this solution:

Field Name
Name of medication
Single line of text
Description of medication
Multiple lines of text (plain text)
Expiry Date
When the meds expire
Of course, you can track additional information about each drug if you choose to.


The solution has a time-triggered Flow that checks on a nightly basis if there are any meds that are about to expire (within two weeks) and sends a notification via email.  There are three actions and one control required to build this flow.

  1. Get a list of all items who's due date is within 15 days.  To do so, I'm leveraging the following Flow expression in the Get items action under the Filter Query field  formatdatetime(adddays(utcnow(),15),'yyyy-MM-dd')

    1. Create an HTML table for all the items using the Create HTML table action.  I only need the Title and Expire Date (Expiry_x0020_Date) for my email
    2. Finally, I use the Send email action to notify me of the medications that are about to expire
    The flow can easily be expanded to add items to a shopping list for other apps and services, such as Amazon.  In that case, you will likely need to add additional fields to the SharePoint list to add the item ID or links for these medications in the various apps or services.


    To keep things simple, I'm using the Start from data template and modified a few fields to achieve the look shown above

    In particular, in order to get the EXPIRED label to show up, I'm using the following formula for the Visible parameter:  DateDiff(Today(),ThisItem.'Expiry Date',Days)<0

    Similarly, I'm using the expiry date to determine whether the bar on the left-hand-side of an item should be white (default), yellow (within 30 to 15 days of expiry), or red (less than 15 days of expiry).
    If(DateDiff(Today(),ThisItem.'Expiry Date',Days)<15,RGBA(255,0,0,1),If(And(DateDiff(Today(),ThisItem.'Expiry Date',Days)>=15,DateDiff(Today(),ThisItem.'Expiry Date',Days)<30),RGBA(255,255,0,1),RGBA(255,255,255,1)))

    Simple solution, but will hopefully help you stay healthy by having your meds nearby and prevent you from wasting money on buying too much.

    Employee Check-in Simplified using Power Automate

    Company employees who move between offices are often faced with a tedious process of having to check-in at the security desk to get a tempor...